CODEWITHSUNDEEP
sqlsql-serverselect
Starbucks
Starbucks

Reputation: 1568

Select Statement - Use Created Column to Manipulate

How do I use a created variable in a SQL Server Select statement and manipulate it afterwards? For the purposes of the example, I want to create a binary column from column No_Grade.

SELECT r.[ID], r.[Grade1], r.[Grade2], r.[Grade3], r.[Grade4], 

(case when (r.[Grade1] = r.[Grade2]) then r.[Rod1Grade]
else '' END) as No_Grade,

(case when No_Grade is null then 1 else 0 END) as Yes_Grade

FROM [DB].[TABLE] as r

I get the error Incorrect syntax near 'case'. for the second case statement.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

In SQL Server, I would suggest using APPLY -- to move the definition to the FROM clause:

SELECT r.[ID], r.[Grade1], r.[Grade2], r.[Grade3], r.[Grade4], 
       v.No_Grade,
       (case when v.No_Grade is null then 1 else 0 END) as Yes_Grade
FROM [DB].[TABLE] r CROSS APPLY
     (VALUES (case when r.[Grade1] = r.[Grade2] then r.[Rod1Grade]
              end)
     ) v(no_grade);

Note that I removed the else clause so the value can actually be NULL.

Upvotes: 1

Related Questions