-1

I'm using SSMS (T-SQL) to find even numbers in the ID column. My code does not recognize the CASE WHEN column AS 'Nums'. What am I doing wrong?

The code:

SELECT 
    City, ID,
    (CASE
        WHEN ID % 2 = 1 THEN 'Odd'
        ELSE 'Even'
     END) AS Nums
FROM 
    STATION
WHERE 
    Nums = 'Even'

The error I get:

Msg 207, Level 16, State 1, Server dbrank-tsql, Line 6
Invalid column name 'Nums'

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jay Hussain
  • 1
  • You would have to do the case statement in the where clause as well or do a sub select. – Brad Aug 12 '22 at 12:06

2 Answers2

2

You cannot reference a newly introduced "computed" column in the WHERE clause - you'd have to e.g. use a CTE (or a subquery) to achieve this:

WITH DataCte AS
(  
    SELECT 
        City, ID,
        CASE
            WHEN ID % 2 = 1  
                THEN 'Odd'
                ELSE 'Even'
        END AS Nums
    FROM 
        STATION
)
SELECT City, ID, Nums
FROM DataCte
WHERE Nums = 'Even';
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

This because you can not used 'Nums' for comparing because 'Nums' is not a column it is only alias, try following

Select City,ID,
(CASE WHEN ID % 2 = 1 THEN 'Odd' ELSE 'Even' END) AS Nums
From STATION
Where ID % 2 = 0

if you want to used 'Nums' as column than used with as a following

With T As
(Select City,ID,
(CASE WHEN ID % 2 = 1 THEN 'Odd' ELSE 'Even' END) AS Nums
From STATION)
Select * From T Where Nums = 'Even'
Uday Dodiya
  • 339
  • 2
  • 15
  • 3
    `Nums` is most definitely a column of the result set. An alias is when an existing column is given a new name. The reason it can't be used is only because `WHERE` logically happens before `SELECT`, so `Nums` is not in scope. – Jeroen Mostert Aug 12 '22 at 12:22