CODEWITHSUNDEEP
sqlsql-serversyntax
PeteMiller
PeteMiller

Reputation: 67

Removing these darn NULLS

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)

Upvotes: 3

Views: 65

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

As Dan explains, your use of ISNULL() is just not appropriate. From your description, you seem to want 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 more simply:

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;

Upvotes: 3

user1919238
user1919238

Reputation:

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.

Upvotes: 3

Related Questions