Обсуждение: Subselect AS and Where clause

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

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

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

> 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