Thread: Invalid input for numeric problem

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?

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