Re: Subselect AS and Where clause - Mailing 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