Use an alias in Select Query

Question

I need to ask how to use an alias in Select Query,

I need this

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

Answer 1

You cannot do this:

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

However, you can do this:

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

or

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

The problem is that you cannot reference aliases such as those in SELECT and WHERE clauses, because they will not be evaluated by the time the selection is made or where it is executed. You can also use the having , but do not use indexes with clauses and should be avoided if possible.

Answer 2

You can rewrite your request as follows

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

Answer 3

Another solution you can use:

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