Re: Why lower's not accept an AS declaration ?

----- Original Message -----
From: "Hervé Piedvache" <herve@elma.fr>
To: "Darko Prenosil" <darko.prenosil@finteh.hr>; "Postgresql General"
<pgsql-general@postgresql.org>
Sent: Monday, August 18, 2003 6:59 PM
Subject: Re: [GENERAL] Why lower's not accept an AS declaration ?


> Hi,
>
> An to be more precise what I exactly want to do :
>
> select
> case when 'now' between t.begin and t.end then t.login else 'None' end as
log
> from my_table t
> order by lower(log);
>

Here is the rewired query that works :

CREATE TABLE my_table ("begin" timestamp, "end" timestamp, login
varchar(100));

select case
    when now() between "t"."begin" and "t"."end"  then t.login
    else 'None'
    end
    as log
from my_table t
order by lower(1);


where number 1 is the number of result column. I'm puzzled too now, because
according to docs, it should work.
Here is the part from docs that even explains what happens if the real table
column name and result alias are the same:

If an ORDER BY expression is a simple name that matches both a result column
name and an input column name, ORDER BY will interpret it as the result
column name. This is the opposite of the choice that GROUP BY will make in
the same situation. This inconsistency is made to be compatible with the SQL
standard.

I must confess that I wasn't reading Your mail carefully. Sorry ! You were
right !
Regards !