SEO

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?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

mike wrote:
> WHEN to_number(vw_expend.code::text, '999'::text) <
> 50::numeric THEN '2'::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: " "

Probably vw_expend.code contains a space in one or more rows, and
to_number() is failing the conversion.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings