Sql-server – Reference a newly created computed column in the same query where we create it

sql-server-2008

SELECT 
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE ComputedColumn !="dec"
INTO Table2
FROM Table1;

This is an example where I have a computed column (which is essentially created by using an expression). If I run this, I get a "column name invalid" error, because I make a reference to the computed column in the same query where I created it.

In an application called SAS, this is solved by prefixing the computed column name in the WHERE clause with the "(CALCULATED)" keyword. Is there a similar solution in Sql Server.
Other than doing this:

SELECT 
(SUBSTRING(Column1,1,3)) AS ComputedColumn
WHERE (SUBSTRING(Column1,1,3)) !="dec"
INTO Table2
FROM Table1;

Which I know it is possible but very confusing when the expression is very huge. There must be another way to REFERENCE A NEWLY CREATED COMPUTED COLUMN in the same query where we create it.

Thanks a billion!
Anna

Best Answer

You can use CROSS APPLY for this:

SELECT ComputedColumn 
INTO Table2 
FROM Table1
CROSS APPLY (SELECT SUBSTRING(Column1,1,3)) CxA(ComputedColumn)
WHERE ComputedColumn !="dec"
Related Question