Thread: BUG #1228: numeric field from a view from a view does not recognise any where values
BUG #1228: numeric field from a view from a view does not recognise any where values
From
"PostgreSQL Bugs List"
Date:
The following bug has been logged online: Bug reference: 1228 Logged by: mike Email address: mike@bristolreccc.co.uk PostgreSQL version: 7.4.3 Operating system: FC3 Description: numeric field from a view from a view does not recognise any where values Details: 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
Re: BUG #1228: numeric field from a view from a view does not recognise any where values
From
Alvaro Herrera
Date:
On Tue, Aug 24, 2004 at 01:33:46PM -0300, PostgreSQL Bugs List wrote: > 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 > 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: " " Maybe there's a row somewhere in the vw_rec_sum relation that has a " " value in the code field ... or a similar weirdness in some other column. That's expected when you store numbers in text columns. Don't do that. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"