CODEWITHSUNDEEP
sqlsql-serveroracle-database
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 34054

Use Alias in Select Query

I need to ask how can use Alias in Select Query,

I need this

SELECT (Complex SubQuery) AS A, (Another Sub Query WHERE ID = A) FROM TABLE

Upvotes: 8

Views: 18166

Answers (3)

Arbel Sella
Arbel Sella

Reputation: 1

Another solution which you can use:

SELECT (Complex SubQuery) AS A, (Another Sub Query WHERE ID = A) 
FROM 
TABLE MAIN
OUTER APPLY (SELECT (Complex SubQuery) AS A)

Upvotes: 0

wqw
wqw

Reputation: 11991

You can rewrite your query like this

SELECT Complex.A, (Another Sub Query WHERE ID = Complex.A)
FROM TABLE
CROSS JOIN ((Complex SubQuery) AS A) Complex

Upvotes: 2

Johan
Johan

Reputation: 76753

You cannot do this:

SELECT (Complex SubQuery) AS A, (Another Sub Query WHERE ID = A) FROM TABLE

You can however do this:

SELECT (Another Sub Query WHERE ID = A.somecolumn)
FROM table
JOIN SELECT (Complex SubQuery) AS A on (A.X = TABLE.Y)

Or

SELECT (Another Sub Query)
FROM table
WHERE table.afield IN (SELECT Complex SubQuery.otherfield)

The problem is that you cannot refer to aliases like this in the SELECT and WHERE clauses, because they will not have evaluated by the time the select or where part is executed.
You can also use a having clause, but having clauses do not use indexes and should be avoided if possible.

Upvotes: 13

Related Questions