Обсуждение: upper() don't work with field-alias in the order by part of select statement.

Поиск
Список
Период
Сортировка

upper() don't work with field-alias in the order by part of select statement.

От
Wjatscheslaw Wächter
Дата:
I need in some select-statements case-insensetiv ordering of results,
i want to use upper()-function in order by part of statement but it
doesn't work with field-aliases.
Can I do this in another way?
I'm using PostgreSql 7.1.2 on SuSe-Linux

Example:   this works:   select p1.prs_vorname as vorname, p1.prs_nachname as nachname,          p1.lfdnr as lfdnr,
pos.pos_nameas position,          p1.prs_email as email, abt.abt_name as abteilung   from person p1 left join abteilung
abt         on abt.lfdnr = p1.prs_abt_lfdnr,        person p2 left join positionen pos          on
pos.lfdnr=p2.prs_pos_lfdnr  where   p1.lfdnr=p2.lfdnr   ORDER BY nachname; 
   this don't work:   select p1.prs_vorname as vorname, p1.prs_nachname as nachname,          p1.lfdnr as lfdnr,
pos.pos_nameas position,          p1.prs_email as email, abt.abt_name as abteilung   from person p1 left join abteilung
abt         on abt.lfdnr = p1.prs_abt_lfdnr,        person p2 left join positionen pos          on
pos.lfdnr=p2.prs_pos_lfdnr  where   p1.lfdnr=p2.lfdnr   ORDER BY upper(nachname); 
   ERROR:  Attribute 'nachname' not found

Re: upper() don't work with field-alias in the order by part

От
Stephan Szabo
Дата:
On Thu, 6 Sep 2001, Wjatscheslaw [iso-8859-1] Wächter wrote:

> I'm using PostgreSql 7.1.2 on SuSe-Linux
>
> Example:
>     this works:
>     select p1.prs_vorname as vorname, p1.prs_nachname as nachname,
>            p1.lfdnr as lfdnr, pos.pos_name as position,
>            p1.prs_email as email, abt.abt_name as abteilung
>     from person p1 left join abteilung abt
>            on abt.lfdnr = p1.prs_abt_lfdnr,
>          person p2 left join positionen pos
>            on pos.lfdnr=p2.prs_pos_lfdnr
>     where   p1.lfdnr=p2.lfdnr
>     ORDER BY nachname;
>
>     this don't work:
>     select p1.prs_vorname as vorname, p1.prs_nachname as nachname,
>            p1.lfdnr as lfdnr, pos.pos_name as position,
>            p1.prs_email as email, abt.abt_name as abteilung
>     from person p1 left join abteilung abt
>            on abt.lfdnr = p1.prs_abt_lfdnr,
>          person p2 left join positionen pos
>            on pos.lfdnr=p2.prs_pos_lfdnr
>     where   p1.lfdnr=p2.lfdnr
>     ORDER BY upper(nachname);
>
>     ERROR:  Attribute 'nachname' not found

Does upper(p1.prs_nachname) work?