Re: Subselect AS and Where clause

> Uwe Schroeder, 26.01.2011 08:34:
> > 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
>
> You need to wrap the whole SELECT in order to be able to use the column
> alias:
>
> SELECT *
> FROM (
> SELECT a,
> b,
> c,
> (select problem from other_table where id=a) as problem
> FROM mytable
> ) t
> WHERE a=1
> AND problem = 3
>
> Regards
> Thomas

Thanks a lot Thomas!

there's the reason why open source like postgresql is far supperior to
anything commercial - an answer when you need it!

Works like a charm now.

Uwe