Click here to Skip to main content
15,446,232 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Guys,

I have a query that I'm creating a column in select using case statement
SQL
select col1, (case when t1.org='x' then xDate else yDate) as newDate,(case when newDate< joiningDate then 1 else 0) as PDate
from table1 t1
left join table2 on t1.id=t2.id

I'm unable to find the column newDate in IntelliSense in order to create column PDate. if I type it manually it throws an invalid column error.
I'm not sure how exactly does it work.

Can anyone help me to get the desired result?

Thank you.

What I have tried:

I'm not sure how does it work.
Posted
Updated 20-Sep-21 3:01am
v2

Calculated columns aren't available in the same SELECT statement where they're defined. You either need to repeat the calculation:
SQL
CASE 
    WHEN t1.org = 'x' And xDate < joiningDate THEN 1
    WHEN t1.org != 'x' And yDate < joiningDate THEN 1
    ELSE 0
END As PDate

Or you can use CROSS APPLY to define your calculated column:
SQL
SELECT
    col1,
    D.newDate,
    CASE WHEN D.newDate < joiningDate THEN 1 ELSE 0 END As PDate
FROM
    table1 As t1
    LEFT JOIN table2 As t2 ON t1.id = t2.id
    CROSS APPLY (SELECT CASE WHEN t1.org = 'x' THEN xDate ELSE yDate END As newDate) As D
;
The many uses of CROSS APPLY | SQL Studies[^]
 
Share this answer
 
v2
Thank you Richard, Cross Apply just worked great :) and got to know a use case as well.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900