Lost Thread - CASE Statement Returns Null Values (1 Viewer)

[WayneRyan]

I finished typing this response; only to find the thread gone.
If it helps the OP then here goes ...

Kheribus,

If your case statement may return NULL on several conditions:

CASE WHEN ecsss.transportation.private_program IS NOT NULL THEN
              (SELECT cluster_name
               FROM   private_programs n
               WHERE  n.private_program = ecsss.transportation.private_program)
     ELSE dbo.schools.cluster
     END AS svc_cluster

1) change your SELECT part of your query to add the following
   AND add a where clause to spot your troubles:

SELECT  ecsss.transportation.request_type,
        FORMAT(ecsss.transportation.effective_date, 'dd-MM-yy') AS [Effective Date],
        ecsss.transportation.private_program,    -- You're already selecting this; are any null

           (SELECT  cluster_name                 -- Add this subquery, it represents the 1st condition
            FROM    private_programs n           -- of your case statement
            WHERE   n.private_program = ecsss.transportation.private_program) AS Check_Cluster --

        dbo.schools.cluster AS svc_cluster,      -- Add this, represents the ELSE part of your case
        ecsss.transportation.chg_contact,
        -- ... rest of columns

FROM            ecsss.transportation INNER JOIN
                         dbo.schools ON ecsss.transportation.attending_school_code = dbo.schools.schoolCode INNER JOIN
                         dbo.[2019_rank_table_st11] ON ecsss.transportation.extract_id = dbo.[2019_rank_table_st11].extract_id AND dbo.[2019_rank_table_st11].rank = 1 LEFT OUTER JOIN
                         sses.students ON ecsss.transportation.student_ext_id = sses.students.studentID
--
-- The where clause will show the rows that return NULL values for EACH condition of
-- your CASE statement.
--
WHERE  (   ecsss.transportation.private_program Is Not Null And
           (SELECT  cluster_name              
            FROM    private_programs n         
            WHERE   n.private_program = ecsss.transportation.private_program) Is NULL) OR
       (    ecsss.transportation.private_program Is Null And
            dbo.schools.cluster Is Null)

I'd guess that the following will return some rows:

SELECT  cluster_name 
from    ecsss.transportation.private_program
where private_program not in (Select private_program
                              FROM   private_programs)

hth,
Wayne