Re: Subselect AS and Where clause - Mailing list pgsql-general

From Uwe Schroeder
Subject Re: Subselect AS and Where clause
Date
Msg-id 201101260100.29505.uwe@oss4u.com
Whole thread Raw
In response to Re: Subselect AS and Where clause  (Thomas Kellerer <spam_eater@gmx.net>)
List pgsql-general

> 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




pgsql-general by date:

Previous
From: tuanhoanganh
Date:
Subject: PostgreSQL 9.0 service error : The service did not respond to the start or control request in a timely fashion.
Next
From: tuanhoanganh
Date:
Subject: Re: PostgreSQL 9.0 service error : The service did not respond to the start or control request in a timely fashion.