On 01/26/2011 09:34 AM, Uwe Schroeder wrote:
Google being useless tonight - now that's new :-)
What I'm trying to do is the following and I'm certain there is a simple
solution which eludes me:
I have a query like this:
SELECT a,b,c, (select problem from other_table where id=a) as problem FROM
mytable WHERE a=1
So far so good. Actually "problem" always resolves to one record, so it's not
the "multiple records returned" problem.
What I try to do is this:
SELECT a,b,c, (select problem from other_table where id=a) as problem FROM
mytable WHERE a=1 and problem = 3
see the "problem=3" part in the where clause? The error I get is
SQLError: (ProgrammingError) column "problem" does not exist
Do I miss something? Shouldn't the "as" assignment make this virtual column
available to the where clause? I think this should work and I know it works if
I'd make a view out of the query. However, the query is built dynamically, so
turning it into a view isn't really an option.
Any pointer will be greatly appreciated.
Uwe
The simplest solution would be
select a,b,c,problem from mytable left join other_table on id=a
where a=1 and problem=3
column aliases dont work inside the statement itself, neither in the
select, join, where, group nor having clauses in postgresql (they do in
mysql) to get this to work you would need to use the full column such as
select a,b,c,(select problem from test2 where id=a) as prob
from test1
where a=1 and (select problem from test2 where id=a) = 'dont work'
Sim
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
