How to use a non-existing column in sql query

Question

I am working in SQL server 2012. I have to write a sql statement where I first assign a value to [Pay_Type], which is a non-existing column (not sure whether it can be called as variable or not) and based upon its value I want to use it in another case statement as shown below

SELECT sp.First_Name, [Pay_Type] = CASE WHEN NOT EXISTS(SELECT '1' FROM 
PERSON_SALARY ps WHERE ps.PARTY_ID = sp.PARTY_ID and ps.END_DATE IS NULL) 
THEN 'Hourly' ELSE 'Salary' END,
HOURLY_RATE = CASE WHEN [Pay_Type] = 'Hourly' THEN pj.HOURLY_RATE ELSE 
'0.00' END
FROM SEC_PERSON sp 
LEFT OUTER JOIN PERSON_JOB pj ON sp.PERSON_ID = pj.PERSON_ID 
WHERE sp.END_DATE IS NOT NULL

But I am getting "Invalid column name 'Pay_Type' " error.

Can you not move to the end of the Case statement and tack on `AS "Pay_Type"` — dbmitch, May 29 '18 at 17:20
To be able to refer columns you have created with an alias, you need to do them inside a derived table, cross / outer apply or a CTE — James Z, May 29 '18 at 17:21
Possible duplicate of [Reference an alias elsewhere in the SELECT list](https://stackoverflow.com/questions/11975749/reference-an-alias-elsewhere-in-the-select-list) — Tab Alleman, May 29 '18 at 17:21

score 0 · Answer 1 · answered May 29 '18 at 17:21

Column aliases cannot be re-used in the same SELECT where they are define. The typical answer is to use a subquery or CTE. I also like using a lateral join:

SELECT sp.First_Name, s.Pay_Type,
       HOURLY_RATE = (CASE WHEN s.Pay_Type = 'Hourly' THEN pj.HOURLY_RATE ELSE 
'0.00' END)
FROM SEC_PERSON sp LEFT OUTER JOIN
     PERSON_JOB pj
     ON sp.PERSON_ID = pj.PERSON_ID OUTER APPLY
     (SELECT (CASE WHEN NOT EXISTS (SELECT 1
                                    FROM PERSON_SALARY ps
                                    WHERE ps.PARTY_ID = sp.PARTY_ID and ps.END_DATE IS NULL
                                   ) 
                   THEN 'Hourly' ELSE 'Salary'
             END) as PayType
     ) s    
WHERE sp.END_DATE IS NOT NULL

How to use a non-existing column in sql query

1 Answers1