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

Invalid input for integer on VIEW

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

Responses

pgsql-general by date

Next:From: Tatsuo IshiiDate: 2004-08-24 14:52:23
Subject: Re: UTF-8 and LIKE vs =
Previous:From: Dave CramerDate: 2004-08-24 14:39:22
Subject: Re: No connection to a PG 8.0 Beta 1 win32 server

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