CODEWITHSUNDEEP
sql-server-2008nullcase
user2800758
user2800758

Reputation: 5

SQL Server 2008 - Return not null values from a CASE WHEN statement

SELECT *,
    CASE 
    when PERSONNUM in ('x','y','z')
    then 'Home'
    end as HomeEmployees
    when PERSONNUM in ('a','b','c')
    then 'Away'
    end as AwayEmployees
FROM dbo.ALLTOTALS
where PERSONNUM in ('a','b','c','x','y','z')
    and HomeEmployees is not null

--multiple whens based upon select PERSONNUM fields

Upvotes: 0

Views: 5099

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Logical order of execution(see section "Logical Processing Order of the SELECT statement") for the following SELECT statement:

DECLARE @MyTable TABLE
(
    ID      INT IDENTITY PRIMARY KEY,
    [Date]  SMALLDATETIME NOT NULL,
    WorkingTime INT NOT NULL,
    EmployeeID  INT NULL
);
INSERT  @MyTable ([Date], WorkingTime, EmployeeID)
SELECT  '20130801', 1, 123 UNION ALL 
SELECT  '20130802', 0, 124 UNION ALL 
SELECT  '20130803', 0, 125; 

SELECT  x.ID,
        CASE WHEN x.ID % 2 = 1 THEN x.ID END AS EmployeeID
FROM    @MyTable x
WHERE   EmployeeID IS NOT NULL;

is

  1. FROM @myTable x
  2. WHERE EmployeeID IS NOT NULL ( <=> WHERE x.EmployeeID IS NOT NULL )
  3. SELECT x.ID, CASE ... END AS EmployeeID

As you can see, CASE ... END AS EmployeeID expression is evaluated AFTER WHERE clause. And this means also that EmployeeID IS NOT NULL predicate references x.EmployeeID column and not the EmployeeID alias from SELECT clause.

This is the reason I get the following results:

ID          EmployeeID
----------- -----------
1           1
2           NULL
3           3

If you want to filter by EmployeeID alias then you could use one of these solutions:

SELECT  x.ID,
        CASE WHEN x.ID % 2 = 1 THEN x.ID END AS EmployeeID
FROM    @MyTable x
WHERE   CASE WHEN x.ID % 2 = 1 THEN x.ID END IS NOT NULL;

SELECT *
FROM
(
SELECT  x.ID,
        CASE WHEN x.ID % 2 = 1 THEN x.ID END AS EmployeeID
FROM    @MyTable x
) src
WHERE   src.EmployeeID IS NOT NULL;

Note: It's not a good idea to use an alias with the same name as a column name. This create confusion.

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

You can also project the case into an alias and then filter it with an outer query, like so:

SELECT * -- Or better still, explicitly identify each needed column
FROM
(
  SELECT *, -- Or better still, explicitly identify each needed column
    CASE 
        WHEN PERSONNUM in ('x','y','z')
        THEN 'Home'
        END AS HomeEmployees
    FROM AllTotals
) AS FilteredEmployees 
WHERE 
  FilteredEmployees.HomeEmployees IS NOT NULL;

Or as a CTE:

WITH FilteredEmployees AS
(
  SELECT *, 
    CASE 
        WHEN PERSONNUM in ('x','y','z')
        THEN 'Home'
        END AS HomeEmployees
    FROM AllTotals
)
SELECT *
  FROM FilteredEmployees
  WHERE FilteredEmployees.HomeEmployees IS NOT NULL;

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Why not modify your WHERE clause from

where HomeEmployees is not null

into 

WHERE PERSONNUM IN ('x', 'y', 'z')

so only records with values of x , y, z in column PERSONNUM are only selected.

Upvotes: 2

Related Questions