Re: field alias in where condition

Havasvölgyi Ottó wrote:
> Hi all,
>
> I issued the following queries:
>
>
> select substring(proname from 1 to 1) as nevresz, count(*)
> from pg_proc
> where nevresz = 'a'
> order by nevresz
> group by nevresz;
>
> select substring(proname from 1 to 1) as nevresz, count(*)
> from pg_proc
> order by nevresz
> group by nevresz;
>
> The first query fails, and says that column 'nevresz' does not exist.
> The second is OK.
>
> What is the problem? I cannot use column alias in where condition?

Correct. SQL defines it that way. You could reformulate as a sub-query
if you wanted:

SELECT nevresz,count(*)
FROM
(
SELECT substring(proname from 1 to 1) as nevresz FROM pg_proc
) AS dummy
GROUP BY nevresz
ORDER BY nevresz

--
Richard Huxton
Archonet Ltd