Re: Invalid input for integer on VIEW

On Tue, 2004-08-24 at 15:42 +0100, mike wrote:
> 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
>

If I do the same query, except to create a new table, everything works,
so is this a view bug?

> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>