SQL Case statement returning true although should be false

I have a SQL statement that is something like this

SELECT t1.*
    CASE 
        WHEN t1.COL1 = 0 THEN 0
        WHEN 
            (t1.COL2 = 'val' OR t1.COL3 = 'val' 开发者_Go百科etc) AND ((SELECT COUNT(*) FROM t1 WHERE etc...) > 0)
        THEN Run same sub query
        WHEN 
            something else
        THEN defaultvalue
    END as brokencase

Now, the second WHEN statement above, when run alone, is returning zero. However when it is run here, I am getting a bunch of null values back because that statement is somehow returning true.

So why is the count statement returning zero, but the WHEN statement thinks its true? I have left out a lot of details here for privacy reasons, but this is the general problem.

Usually when I see this, the programmer is missing an ELSE or DEFAULT case in their statements. To find out what scenario you're not capturing, set up a select like this:

SELECT DISTINCT columnTested1, columnTested2, columnTestedX, case statement FROM blah

Show all the columns being tested, then your case, what you'll see in the result is the combination of columns that are causing your NULL. You need to capture those in your case statement or you'll continue to miss those options

When you test different columns in the different options, you might need multiple else statements to catch them all. Once you've identified the combination of columns causing you a problem, it becomes dead simple to fix the case statement.

If you want to exclude results that have null values you will have to make sure you are checking for that in your WHEN condition. You haven't specified your WHERE clause in your count query so I can't say for sure if this is the problem. It would look something like this:

SELECT COUNT(*) FROM t1 WHERE ExcludeValue IS NOT NULL