General discussion


SQL Basic: Referencing an Alias?

By Jay Johnson ·
How does one reference another column in the same select statement by alias?

for example in:

Select (1 + 2) as mysum, (mysum*2) as doublemy um"

seems to cause an error.
My problem is that i have some very complex expressions and I need to be able to refer to their value repeatedly...


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by raghx_2000 In reply to SQL Basic: Referencing an ...

You need to show the code coz to see the expression clearly.

Collapse -

referencing alias

by tdselvam In reply to SQL Basic: Referencing an ...

I hope you are using oracle.The only way i can think of solving your process it to use the frequently need values as separate select statment like this

Option 1:-
select (select 1*2 from dual)+(3*2) mysum1 from dual;

Option 2:-
select 1*2 mysum,(select 1*2 from dual)+(3*2) mysum1 from dual;

Hope this helps you.


Collapse -

Use Dual

by Tom Carlisle In reply to SQL Basic: Referencing an ...

Oracle has a psuedo table called DUAL that facilitates this but, even if you're using MS Access, you can do it.
First, create a table with one column called DUMMY (Text, 1 character wide) and call the table DUAL. Close the table, then open it and insert a NULL value in the first record by typing something in the column, then backspacing to delete it entirely. Then save the table by closing it.
Second comes your SQL. Here it is:

SELECT (1+2) AS MySum, MySum*2 as DoubleMySum FROM DUAL;

I tried it on MS Access 2000 and it works.

Collapse -

Try This

by john_larkin In reply to SQL Basic: Referencing an ...

Try this. It's saved me many times. You select the
column that you want to reference in a select stmt whose result set will be trweated as a table in the FROM clause, you just need to alias the query (d1) so that you can reference columns in the result set. (This was run on Oracle 8.1.7)

Select d1.mysum, d1.mysum*2 as doublemysum
from dual,
(select (1 + 2) as mysum from dual) d1

Related Discussions

Related Forums