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

Re: BUG #1228: numeric field from a view from a view does not recognise any where values

From: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
To: mike <mike(at)bristolreccc(dot)co(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1228: numeric field from a view from a view does not recognise any where values
Date: 2004-08-24 19:11:48
Message-ID: 20040824191148.GB5682@dcc.uchile.cl (view raw or flat)
Thread:
Lists: pgsql-bugs
On Tue, Aug 24, 2004 at 01:33:46PM -0300, PostgreSQL Bugs List wrote:

>         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

> 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: " "

Maybe there's a row somewhere in the vw_rec_sum relation that has a " "
value in the code field ... or a similar weirdness in some other column.
That's expected when you store numbers in text columns.  Don't do that.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"


In response to

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2004-08-24 19:14:42
Subject: Re: replacing a function called "isnull" reports an error
Previous:From: Reini UrbanDate: 2004-08-24 18:33:20
Subject: 8.0.0b1 cygwin problems

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