where cannot use alias name of column?

En un mensaje anterior, Giorgio Volpe escribi�:

May be it's my ignorance about sql ...
but why cannot i use alias name of a column in a where clause?

# select key as cc from mytable where cc > 0;
ERROR: Attribute 'cc' not found

AFAIK you can't use alias in ther where part.

Good luck!

Fernando P. Schapachnik
Planificaci�n de red y tecnolog�a
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

Giorgio Volpe writes:

May be it's my ignorance about sql ...
but why cannot i use alias name of a column in a where clause?

# select key as cc from mytable where cc > 0;
ERROR: Attribute 'cc' not found

The processing order of this command is, perhaps unintuitively, FROM ->
WHERE -> SELECT [-> ORDER BY]. The aliases introduced in the SELECT list
are not available in the WHERE expression (but they would be in the ORDER
BY list). If you want to use an alias in the WHERE clause you have to
introduce it in the FROM clause, such as:

SELECT * FROM mytable AS myalias (xx, yy, zz) WHERE zz > 0;

This may or may not be actually useful in your case.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

On Thu, 13 Sep 2001, Giorgio Volpe wrote:

May be it's my ignorance about sql ...
but why cannot i use alias name of a column in a where clause?

# select key as cc from mytable where cc > 0;
ERROR: Attribute 'cc' not found

Because the select list (the key as cc part) isn't
evaluated until after the where clause determines
which rows to evaluate it for.

The WHERE clause is evaluated before the SELECT list, so the column aliases
are not available to be used at that point.
You can get away with using column alias in a GROUP BY (and SORT BY as
well?), but I believe that is not standard SQL.

----- Original Message -----
From: "Giorgio Volpe" <giorgio.volpe@gtngroup.it>
To: "Postgresql" <pgsql-general@postgresql.org>
Sent: Thursday, September 13, 2001 6:25 AM
Subject: [GENERAL] where cannot use alias name of column?

Peter Eisentraut wrote:

Giorgio Volpe writes:

May be it's my ignorance about sql ...
but why cannot i use alias name of a column in a where clause?

# select key as cc from mytable where cc > 0;
ERROR: Attribute 'cc' not found

If you want to use an alias in the WHERE clause you have to
introduce it in the FROM clause, such as:

SELECT * FROM mytable AS myalias (xx, yy, zz) WHERE zz > 0;

This may or may not be actually useful in your case.

ok & thanks ... actually in my case does not help
i would like to rename a value from an expression so to use it in the where
clause without rewriting the expression!
... for example!

select date_part('month',my_date) as month from my_table where month =
3;

it would be very nice with very complex expressions! (also avoiding
postgresql to evaluate them twice or more times!)
is there a workaround for this?

--

Giorgio

-----------------------------------------