Thread: Invalid input for integer on VIEW
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
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 >
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
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
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