Re: Intermediate results with aliases - when does it work?

On Nov 29, 8:53 am, vezerid <veze..._at_act.edu> wrote:
> Hello all,
>
> please consider the following query:
>
> select 12*sal yearly, yearly+comm
> from emp;
>
> I would expect that the alias can be used in the next output field of
> SELECT. Oracle complains:
>
> select 12*sal yearly, yearly+comm
> *
> ERROR at line 1:
> ORA-00904: invalid column name
>
> However, I am sure I have seen such alias use for intermediate results
> in the past. When can this feature be used?
>
> Kostis Vezerides

This is how it could be done:

SQL> select yearly, yearly+comm
  2 from emp, (select empno, 12*sal yearly from emp) yrly   3 where yrly.empno = emp.empno;

    YEARLY YEARLY+COMM
---------- -----------

      9600
     19200       19500
     15000       15500
     35700
     15000       16400
     34200
     29400
     36000
     60000
     18000       18000
     13200

    YEARLY YEARLY+COMM
---------- -----------

     11400
     36000
     15600

14 rows selected.

Notice, however, that NULL commissions result in NULL sums. You should really be using:

SQL> select yearly, yearly+nvl(comm,0) "YEARLY+COMM"   2 from emp, (select empno, 12*sal yearly from emp) yrly   3 where yrly.empno = emp.empno;

    YEARLY YEARLY+COMM
---------- -----------

      9600        9600
     19200       19500
     15000       15500
     35700       35700
     15000       16400
     34200       34200
     29400       29400
     36000       36000
     60000       60000
     18000       18000
     13200       13200

    YEARLY YEARLY+COMM
---------- -----------

     11400       11400
     36000       36000
     15600       15600

14 rows selected.

SQL> You cannot use an alias in the same select list as the one where it is assigned, as you've found. Using it from another query, as illustrated, works.

David Fitzjarrell Received on Thu Nov 29 2007 - 09:48:10 CST