Re: why can a named subselect not be used in a where

Markus,

> cenes_test=> select personen.*, (select max(last_visit) from sessions
> where sessions.personen_id = personen.personen_id) as max from
> personen
> where max between 1009148400 and 1011913200 order by max desc;
> ERROR: Attribute 'max' not found
>
> I again think that the table structure is not neccesary to understand
> my
> question.
>
> Well, why can't I use max in the where clause?

There's two problems with the above query that have nothing to do with
named subselects, which are well-supported by PostgreSQL (in fact, as
of 7.1.3, Postgres has better support for subselects of all sorts than
any other major RDBMS platform).

1. "Max" is a reserved word in SQL. You are confusing the parser.
Pick another name.

2. While you are already aware that the example query is not the most
efficient construction, I also think you are doing your aliasing in
the wrong place:
select personen.*, (select max(last_visit) as max_visit from sessions
where sessions.personen_id = personen.personen_id) from
personen
... but I could be wrong, as I almost never find any reason to use a
sub-select in the SELECT line.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco