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:
I'd guess that the following will return some rows:
hth,
Wayne
If it helps the OP then here goes ...
Kheribus,
If your case statement may return NULL on several conditions:
Code:
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:
Code:
SELECT cluster_name
from ecsss.transportation.private_program
where private_program not in (Select private_program
FROM private_programs)
hth,
Wayne