Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
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

share|improve this question
    
When we're talking about a LEFT, what is the value of trying to reference the alias? Why not just WHERE Column1 NOT LIKE 'dec%'? –  Aaron Bertrand Feb 28 '12 at 4:43

2 Answers 2

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" 
share|improve this answer

Just wrap it up in a derived table:

SELECT *
  INTO Table2
FROM (
  SELECT (SUBSTRING(Column1,1,3)) AS ComputedColumn
  FROM Table1
) t
WHERE ComputedColumn != 'dec'

I also change the double quotes to single quoteset because string literals need to be put between single quotes. Double quotes denote column (or table) names.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.