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

Thanks David.

I really thought I had seen examples where this was possible. The idea being to break up a complex expression to intermediate results, but apparently this is not working. Your method does allow aliases but defeats the purpose of what I was trying to achieve. But thanks anyway.

Kostis

On Nov 29, 5:48 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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 - 10:28:54 CST