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

----- Original Message -----
From: "Darko Prenosil" <Darko.Prenosil@finteh.hr>
To: "Hervé Piedvache" <herve@elma.fr>; "Postgresql General"
<pgsql-general@postgresql.org>
Sent: Monday, August 18, 2003 10:09 PM
Subject: Re: [GENERAL] 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 !
>
Wrong again ! This works, but it does not sorting anything. We can say that
ORDER BY accepts both column numbers and column aliases, but not column
numbers and aliases as arguments in functions. I can say this because this
works :

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

same as:

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

Sorry for the mess !
Regards !