Removing those darned NULLS
I have a problem that seems straight forward, but for some reason I can't get rid of my nulls from hitting selection. All I need to do this is to return one row and the other without NULL. Can anyone point out an error in my paths? :)
The result I get on startup is:
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 request:
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)
source to share
You get this zero because the data doesn't match any of the conditions in your case. In other words, on this line you have a value for the ConfigID that is not 3 or 4. The behavior of the case statement when none of the conditions match is zero, and therefore null is returned for this line.
Also, this function: ISNULL(ConfigID,'')
replaces any zero with an empty string (non-zero value).
Hence, it ISNULL(ConfigID,'') IS NOT NULL
doesn't make sense. This will always be true because ISNULL always returns nonzero. You should remove any usage ISNULL()
from your request as none of them are needed.
As Dan explains, your usage ISNULL()
just doesn't fit. From your description, you seem to need this simpler query:
select md.EffectiveDate,
(CASE WHEN ConfigID = 3 THEN 1
WHEN ConfigID = 4 THEN 2
END) as Refund
from (select md.*, max(EffectiveDate) over (partition by PartnerId) as maxed
from dbo.PartnerBankConfig md
where md.PartnerID = 100000509 and
configId in (3, 4)
) md
where md.EffectiveDate = maxed;
Or, even easier:
select top (1) with ties md.EffectiveDate,
(CASE WHEN ConfigID = 3 THEN 1
WHEN ConfigID = 4 THEN 2
END) as Refund
from (select md.*, max(EffectiveDate) over (partition by PartnerId) as maxed
from dbo.PartnerBankConfig md
where md.PartnerID = 100000509 and
ConfigId in (3, 4)
order by EffectiveDate desc;
source to share