Skip site navigation (1) Skip section navigation (2)

Re: Invalid input for integer on VIEW

From: mike <mike(at)bristolreccc(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Invalid input for integer on VIEW
Date: 2004-08-24 15:40:25
Message-ID: 1093362025.11622.2.camel@datacc (view raw or flat)
Thread:
Lists: pgsql-general
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
> 

In response to

Responses

pgsql-general by date

Next:From: Uwe C. SchroederDate: 2004-08-24 15:47:43
Subject: Is it possible...
Previous:From: Thilina GunasekaraDate: 2004-08-24 15:38:19
Subject: Re: [GENERAL] Dump and Restore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group