The following bug has been logged online:

Bug reference: 1228
Logged by: mike

Email address: mike (AT) bristolreccc (DOT) 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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Tue, Aug 24, 2004 at 01:33:46PM -0300, PostgreSQL Bugs List wrote:

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?"


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly