Removing these darn NULLS

Question

I have an issue that would seem straight forward but for some reason I cannot get rid of my null values from the blow select. All I need this to do is return one row, that one without the NULL value. Can someone please point out the error in my ways? :)

The result that I get when running:

EffectiveDate            Refund
2015-05-18 00:00:00.000  NULL
2015-05-18 00:00:00.000   1

What I expect back:

EffectiveDate            Refund
2015-05-18 00:00:00.000   1

My query:

select md.EffectiveDate,
       CASE 
         WHEN 
           ISNULL(ConfigID,'') = 3 THEN '1'
         WHEN 
           ISNULL(ConfigID,'') = 4 THEN '2' 
       END AS Refund
from dbo.PartnerBankConfig md
where md.PartnerID= 100000509
and md.EffectiveDate = (select max(EffectiveDate)
                        from dbo.PartnerBankConfig
                        where PartnerID = 100000509
                        and ISNULL(ConfigID,'') IS NOT NULL)

Answer 1

You get this null because the data doesn't match any condition in your case statement. In other words, in that row you have a value for ConfigID that is neither 3 nor 4. The behavior of a case statement when none of the conditions match is to evaluate to null, and thus null is being returned for this row.

In addition, this function: ISNULL(ConfigID,'') replaces any null with an empty string (a non-null value).

Therefore, ISNULL(ConfigID,'') IS NOT NULL doesn't make sense. It is always going to be true, because ISNULL is always returning a non-null value. You should remove every use of ISNULL() from your query, as none of them are necessary.