Invalid input for integer on VIEW
От | mike |
---|---|
Тема | Invalid input for integer on VIEW |
Дата | |
Msg-id | 1093358573.10206.5.camel@datacc обсуждение исходный текст |
Ответы |
Re: Invalid input for integer on VIEW
|
Список | pgsql-general |
I have the following view definition Column | Type | Modifiers ----------------+-----------------------+----------- bcode | character varying(15) | subhead | text | sc_description | character varying(60) | Budget | numeric | expenditure | numeric | balance | numeric | head | integer | period | integer | View definition: SELECT CASE WHEN vw_rec_sum.code IS NULL AND vw_pay_sum.sum IS NOT NULL THEN vw_pay_sum.code ELSE vw_rec_sum.code END AS bcode, CASE WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 'Salary Costs'::text WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 'Startup Costs'::text WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 'Running Costs'::text WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 'Training Costs'::text ELSE NULL::text END AS subhead, sc_description, vw_rec_sum.sum AS "Budget", vw_pay_sum.sum AS expenditure, CASE WHEN vw_pay_sum.sum IS NULL THEN vw_rec_sum.sum WHEN vw_pay_sum.sum < 0.01 THEN 0.00 - vw_pay_sum.sum + vw_rec_sum.sum ELSE vw_rec_sum.sum - vw_pay_sum.sum END AS balance, CASE WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 1 WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 2 WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 3 WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 4 ELSE NULL::integer END AS head, CASE WHEN to_number(vw_rec_sum.code::text, '999'::text) > 194::numeric THEN 3 WHEN to_number(vw_rec_sum.code::text, '999'::text) < 195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) > 50::numeric THEN 2 ELSE 1 END AS period FROM vw_rec_sum FULL JOIN vw_pay_sum ON vw_rec_sum.code::text = vw_pay_sum.code::text JOIN vw_ac ON vw_rec_sum.code::text = vw_ac.id::text ORDER BY to_number(vw_rec_sum.code::text, '999'::text); However whenever I try to query it with criteria on the period column I get SELECT * FROM vw_budget HAVING "period"='3'; ERROR: invalid input syntax for type numeric: " " If I try on the head column the query runs Getting stumped - anyone any idea what is going on here. This is with 7.4.3
В списке pgsql-general по дате отправления: