T-SQL: accessing temporary column in Common Table Expression

Question

Is it possible to access a temporary column that was defined in a query for a Common Table Expression? Say I have

select * from myTable 

;with cte  as 
(
    select
        *, Salary * 4 as FourTimesSalary
    from 
        Employees
    where 
        Name = @name
        and ID >= 100 
)

Is there a way to use the temporary column FourTimesSalary when querying cte like so?

select top 2 *  
from cte  
order by FourTimesSalary, Name

TIA.

Abe Miessler · Accepted Answer · 2012-02-10 22:11:29Z

1

Yes you can do that. Example:

with temp as
(
    select 1 as id, 2*4 as val
    UNION
    select 2 as id, 3*4 as val
)
SELECT * FROM temp ORDER BY VAL desc

Your example looks fine, did you get an error when you tried that or something?

answered Feb 10, 2012 at 22:11

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Klaus Nji Over a year ago

Yes, the error indicated I did not have column name FourTimesSalary defined. It turned out to be a problem within my query statement.

Collectives™ on Stack Overflow

T-SQL: accessing temporary column in Common Table Expression

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related