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

On Nov 29, 10:28 am, vezerid <veze..._at_act.edu> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

You may be thinking of using the WITH syntax:

with yrly as (
select empno, 12*sal yearly
from emp
)
select yearly, yearly+nvl(comm,0) "YEARLY+COMM" from emp, yrly
where yrly.empno = emp.empno;

But that does essentially the same thing as the prior example.

David Fitzjarrell Received on Thu Nov 29 2007 - 10:33:53 CST