Thread: Invalid input for integer on VIEW

Invalid input for integer on VIEW

From
mike
Date:
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

Re: Invalid input for integer on VIEW

From
mike
Date:
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@postgresql.org
>

Re: Invalid input for integer on VIEW

From
Tom Lane
Date:
mike <mike@bristolreccc.co.uk> writes:
> If I do the same query, except to create a new table, everything works,
> so is this a view bug?

Possibly, but you haven't given enough info to let someone else
reproduce the problem.  A SQL script that creates all the necessary
tables and the view and then triggers the failure would make it much
easier for us to investigate.

            regards, tom lane

Re: Invalid input for integer on VIEW

From
mike
Date:
On Tue, 2004-08-24 at 12:30 -0400, Tom Lane wrote:
> mike <mike@bristolreccc.co.uk> writes:
> > If I do the same query, except to create a new table, everything works,
> > so is this a view bug?
>
> Possibly, but you haven't given enough info to let someone else
> reproduce the problem.  A SQL script that creates all the necessary
> tables and the view and then triggers the failure would make it much
> easier for us to investigate.
>
>             regards, tom lane
>

Is this OK, or do you want some data as well?

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Attachment

Re: Invalid input for integer on VIEW

From
Tom Lane
Date:
mike <mike@bristolreccc.co.uk> writes:
> On Tue, 2004-08-24 at 12:30 -0400, Tom Lane wrote:
>> Possibly, but you haven't given enough info to let someone else
>> reproduce the problem.

> Is this OK, or do you want some data as well?

I plugged in the view definition from your original mail and got

regression=# SELECT * FROM vw_budget HAVING  "period"='3';
 bcode | subhead | sc_description | Budget | expenditure | balance | head | period
-------+---------+----------------+--------+-------------+---------+------+--------
(0 rows)

So either the problem requires data, or there is something wrong with
your left() function, which was not included in the script.  I guessed
at

create function left(text, integer) returns text as
'select substr($1,$2)' language sql ;

but I dunno if this is anything like what you are using.

Please actually *test* the script you are submitting, next time, and
verify that it reproduces the problem starting from an empty database.

            regards, tom lane