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

On Nov 29, 11:33 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

There's no need to join the emp table with itself.

select yearly, yearly+nvl(comm,0) "Yearly+Comm"   from (select 12*sal yearly, comm from emp); Received on Fri Nov 30 2007 - 08:32:20 CST