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

On Nov 29, 8:53 am, vezerid <veze... (AT) act (DOT) edu> wrote:
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

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 (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

On Nov 29, 10:28 am, vezerid <veze... (AT) act (DOT) edu> wrote:
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

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

Thanks again

On Nov 29, 6:33 pm, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net> wrote:

On Nov 29, 11:33 am, "fitzjarr... (AT) cox (DOT) net" <fitzjarr... (AT) cox (DOT) net>
wrote:
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);