Rohini Mathur Rohini Mathur - 2 months ago 10
SQL Question

Cross Apply with Derived Column In SQL with Case Expression

I have very simple query with few computed column like

SELECT COL1 , COL2,COL3
COL4 = (COL1-(COL2*.15))
COL5 = (COL3*(COL1*1.5))

FROM TABLE1 AS A
INNER JOIN TABLE2 AS B
ON A.ID = B.ID


Now i am trying to put Case statement on computed column value using cross apply

CROSS APPLY (

SELECT REMARKS = CASE WHEN COL4> COL1 THEN GOOD ELSE BAD END


but this is giving error .

In Output I am expecting all column i.e col1,col2,col3,col4,col5,Remarks

Can someone please suggest what is my mistake?

Answer

The problem is that CROSS APPLY cannot access a computed field from the SELECT clause because it is evaluated first.

One option is to cascade your computed columns using one CROSS APPLY for each column:

SELECT COL1, COL2, COL3,
       C.COL4, D.COL5,
       E.REMARKS 
FROM TABLE1 AS A 
INNER JOIN TABLE2 AS B ON A.ID = B.ID
CROSS APPLY (SELECT COL4 = (COL1-(COL2*.15))) AS C
CROSS APPLY (SELECT COL5 = (COL3*(COL1*1.5))) AS D
CROSS APPLY (SELECT REMARKS = CASE WHEN C.COL4 > COL1 THEN 'GOOD' ELSE 'BAD' END) AS E

Demo here