SELECT CASE is returning NULL rows - How to remove them

Question

Hi

I have a query which returns a row of NULL values wich I suspect is coming from the select case. How would I correct this without rewriting the whole sql query. This is for a matrix which put the 'section' into a condition performance according to some calculations. I have only included the select case part for easy readability.

SELECT

CASE WHEN ((B.PERFORMANCE_RATING >= 1 AND B.PERFORMANCE_RATING <= 49) AND(B.CONDITION_RATING >= 1 AND B.CONDITION_RATING <= 49)) THEN B.SECTION ELSE NULL END  AS P_POOR_C_POOR,

CASE WHEN ((B.PERFORMANCE_RATING >= 50 AND B.PERFORMANCE_RATING <= 79) AND(B.CONDITION_RATING >= 1 AND B.CONDITION_RATING <= 49)) THEN B.SECTION ELSE NULL END  AS P_MODERATE_C_POOR,

CASE WHEN ((B.PERFORMANCE_RATING >= 80 AND B.PERFORMANCE_RATING <= 100) AND(B.CONDITION_RATING >= 1 AND B.CONDITION_RATING <= 49)) THEN B.SECTION ELSE NULL END  AS P_GOOD_C_POOR,

CASE WHEN ((B.PERFORMANCE_RATING >= 1 AND B.PERFORMANCE_RATING <= 49) AND(B.CONDITION_RATING >= 50 AND B.CONDITION_RATING <= 79)) THEN B.SECTION ELSE NULL END  AS P_POOR_C_MODERATE,

CASE WHEN ((B.PERFORMANCE_RATING >= 50 AND B.PERFORMANCE_RATING <= 79) AND(B.CONDITION_RATING >= 50 AND B.CONDITION_RATING <= 79)) THEN B.SECTION ELSE NULL END  AS P_MODERATE_C_MODERATE,

CASE WHEN ((B.PERFORMANCE_RATING >= 80 AND B.PERFORMANCE_RATING <= 100) AND(B.CONDITION_RATING >= 50 AND B.CONDITION_RATING <= 79)) THEN B.SECTION ELSE NULL END  AS P_GOOD_C_MODERATE,

CASE WHEN ((B.PERFORMANCE_RATING >= 1 AND B.PERFORMANCE_RATING <= 49) AND(B.CONDITION_RATING >= 80 AND B.CONDITION_RATING <= 100)) THEN B.SECTION ELSE NULL END  AS P_POOR_C_GOOD,

CASE WHEN ((B.PERFORMANCE_RATING >= 50 AND B.PERFORMANCE_RATING <= 79) AND(B.CONDITION_RATING >= 80 AND B.CONDITION_RATING <= 100)) THEN B.SECTION ELSE NULL END  AS P_MODERATE_C_GOOD,

CASE WHEN ((B.PERFORMANCE_RATING >= 80 AND B.PERFORMANCE_RATING <= 100) AND(B.CONDITION_RATING >= 80 AND B.CONDITION_RATING <= 100)) THEN B.SECTION ELSE NULL END  AS P_GOOD_C_GOOD

FROM

(

ROW1:   NULL  NULL NULL NULL NULL NULL NULL NULL NULL
ROW2:   NULL NULL NULL NULL NULL Siyaya NULL NULL NULL

How would I exclude null row?

Answer 1

How would I exclude null row?

One method is to wrap the query in a CTE and then use COALESCE to identify rows with all NULL values:

WITH PerformanceRatings AS (
SELECT 
	CASE WHEN ((B.PERFORMANCE_RATING >= 1 AND B.PERFORMANCE_RATING <= 49) AND(B.CONDITION_RATING >= 1 AND B.CONDITION_RATING <= 49)) THEN B.SECTION ELSE NULL END  AS P_POOR_C_POOR,
	CASE WHEN ((B.PERFORMANCE_RATING >= 50 AND B.PERFORMANCE_RATING <= 79) AND(B.CONDITION_RATING >= 1 AND B.CONDITION_RATING <= 49)) THEN B.SECTION ELSE NULL END  AS P_MODERATE_C_POOR,
	CASE WHEN ((B.PERFORMANCE_RATING >= 80 AND B.PERFORMANCE_RATING <= 100) AND(B.CONDITION_RATING >= 1 AND B.CONDITION_RATING <= 49)) THEN B.SECTION ELSE NULL END  AS P_GOOD_C_POOR,
	CASE WHEN ((B.PERFORMANCE_RATING >= 1 AND B.PERFORMANCE_RATING <= 49) AND(B.CONDITION_RATING >= 50 AND B.CONDITION_RATING <= 79)) THEN B.SECTION ELSE NULL END  AS P_POOR_C_MODERATE,
	CASE WHEN ((B.PERFORMANCE_RATING >= 50 AND B.PERFORMANCE_RATING <= 79) AND(B.CONDITION_RATING >= 50 AND B.CONDITION_RATING <= 79)) THEN B.SECTION ELSE NULL END  AS P_MODERATE_C_MODERATE,
	CASE WHEN ((B.PERFORMANCE_RATING >= 80 AND B.PERFORMANCE_RATING <= 100) AND(B.CONDITION_RATING >= 50 AND B.CONDITION_RATING <= 79)) THEN B.SECTION ELSE NULL END  AS P_GOOD_C_MODERATE,
	CASE WHEN ((B.PERFORMANCE_RATING >= 1 AND B.PERFORMANCE_RATING <= 49) AND(B.CONDITION_RATING >= 80 AND B.CONDITION_RATING <= 100)) THEN B.SECTION ELSE NULL END  AS P_POOR_C_GOOD,
	CASE WHEN ((B.PERFORMANCE_RATING >= 50 AND B.PERFORMANCE_RATING <= 79) AND(B.CONDITION_RATING >= 80 AND B.CONDITION_RATING <= 100)) THEN B.SECTION ELSE NULL END  AS P_MODERATE_C_GOOD,
	CASE WHEN ((B.PERFORMANCE_RATING >= 80 AND B.PERFORMANCE_RATING <= 100) AND(B.CONDITION_RATING >= 80 AND B.CONDITION_RATING <= 100)) THEN B.SECTION ELSE NULL END  AS P_GOOD_C_GOOD
FROM ...
)
SELECT 
	P_POOR_C_POOR,
	P_MODERATE_C_POOR,
	P_GOOD_C_POOR,
	P_POOR_C_MODERATE,
	P_MODERATE_C_MODERATE,
	P_GOOD_C_MODERATE,
	P_POOR_C_GOOD,
	P_MODERATE_C_GOOD,
	P_GOOD_C_GOOD
FROM PerformanceRatings
WHERE
	COALESCE(
		P_POOR_C_POOR,
		P_MODERATE_C_POOR,
		P_GOOD_C_POOR,
		P_POOR_C_MODERATE,
		P_MODERATE_C_MODERATE,
		P_GOOD_C_MODERATE,
		P_POOR_C_GOOD,
		P_MODERATE_C_GOOD,
		P_GOOD_C_GOOD
	) IS NOT NULL;

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com