Search Postgresql Archives

Invalid input for numeric problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux