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

Yes, it does the same thing but it taught me something new <s>.

Thanks again

On Nov 29, 6:33 pm, "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
Received on Fri Nov 30 2007 - 07:24:50 CST