I have the following view (output from psql \d)
>>>>>>>>
Column | Type | Modifiers
----------------+-----------------------+-----------
code | character varying(15) |
subhead | text |
sc_description | character varying(60) |
Grant | numeric |
adjustments | numeric |
expenditure | numeric |
balance | numeric |
head | integer |
period | text |
View definition:
SELECT vw_expend.code,
CASE
WHEN "left"(vw_ac.ac_code::text, 2) = 'SA'::text THEN
'Salary Costs'::text
WHEN "left"(vw_ac.ac_code::text, 2) = 'SC'::text THEN
'Startup Costs'::text
WHEN "left"(vw_ac.ac_code::text, 2) = 'RC'::text THEN
'Running Costs'::text
WHEN "left"(vw_ac.ac_code::text, 2) = 'TC'::text THEN
'Training Costs'::text
ELSE NULL::text
END AS subhead, vw_ac.sc_description, vw_expend."Grant",
vw_expend.sum AS adjustments, vw_expend.expenditure, vw_expend.balance,
CASE
WHEN "left"(vw_ac.ac_code::text, 2) = 'SA'::text THEN 1
WHEN "left"(vw_ac.ac_code::text, 2) = 'SC'::text THEN 2
WHEN "left"(vw_ac.ac_code::text, 2) = 'RC'::text THEN 3
WHEN "left"(vw_ac.ac_code::text, 2) = 'TC'::text THEN 4
ELSE NULL::integer
END AS head,
CASE
WHEN to_number(vw_expend.code::text, '999'::text) >
194::numeric THEN '3'::text
WHEN to_number(vw_expend.code::text, '999'::text) <
195::numeric AND to_number(vw_expend.code::text, '999'::text) >
50::numeric THEN '1'::text
WHEN to_number(vw_expend.code::text, '999'::text) <
50::numeric THEN '2'::text
ELSE '0'::text
END AS period
FROM vw_expend
JOIN vw_ac ON vw_expend.code::text = vw_ac.id::text
ORDER BY to_number(vw_expend.code::text, '999'::text);
<<<<<<<<<<
When I do for example
SELECT * FROM vw_budget WHERE period = '1';
I get the following error
ERROR: invalid input syntax for type numeric: " "
this is with rc3
anyone any idea what is going on here?
