ISNULL() is not working... possibly wrong placement?

Question

The following query works great outside of it is still returning null values as NULL instead of 0. Specifically, this is summing up the amount of times something has happened in a specified date range. If the value just doesn't exist within the parameters (i.e. NULL,) then for readability sake I would like for it to return a 0 (the table, which is a join of multiple temp tables, only contains integers.) I have researched and found this case " How can I change NULL to 0 when getting a single value from a SQL function? " and have tried all of the suggestions there, including COALESCE.

(SELECT tn.teamtext, tn.teamid, ISNULL(sum(ISNULL(case when CONVERT(smalldatetime,ca.dModLast,101) BETWEEN '2012-03-01' AND '2012-03-10' then 1 else 0 end, 0)), 0) AS cnt3
  FROM teamnames AS tn 
    LEFT OUTER JOIN caseaudit AS ca
    ON tn.teamID = ca.referteamID2
  WHERE ca.referteamid1 <> ca.referteamid2 AND ca.isactive = 1 AND ca.groupid = 18 AND ca.accountid = 2 AND ca.referteamid1 = 31 AND ca.auditnote <> 'Suspend Case'
  GROUP BY tn.teamtext, tn.teamid) AS c

To give an idea of the returned table I am looking at, here it is:

4H BOSS                    55   59  3
4H BSG                     0    3   2
4H SALES AND MKTG          0    0   0
ACCOUNTS RECEIVABLE        0    0   0
ASSET MANAGEMENT           9    16  0
AUDIT                      0    0   NULL
BOSS                       4    5   0
CORPORATE BSG              0    7   5
CUSTOMER SUPPORT           87   133 NULL
NETWORK ENGINEERING        11   15  0
PRODUCTION ENGINEERING     116  142 5
PRODUCTION OPERATIONS      0    1   0
SECURITY                   2    6   3
SNFAL PRODUCT TEAM         0    14  11
VOICE SERVICES             18   21  0
XEROX                      4    8   0

Yeah. everything is coming from the caseaudit table except for teamtext and teamid. Sorry, I'll put in the aliases. — Josh McKearin, Jun 06 '12 at 15:50
which rdbms? that isnull wrapping the sum function should not allow those nulls to come through. Everything inside that case statement would not matter... — dotjoe, Jun 06 '12 at 16:38
I am using SQL Server 2005. And I agree with you completely, which is why I have become dumbfounded =P — Josh McKearin, Jun 06 '12 at 16:43
Is there another join to the `c` aliased subquery? Or are those results from executing only that query? — dotjoe, Jun 06 '12 at 18:09
I figured it out. The ISNULL logic was fine. The problem was the way I was trying to implement the sub-query. — Josh McKearin, Jun 06 '12 at 19:31

score 2 · Accepted Answer · answered Jun 06 '12 at 18:19

I agree with the (so far) two other responses--the NULL logic in this query should work as you say. But...

I ran something like your query on a local table, and everything ran ok. I then added the outermost parens:

(SELECT...
 ...) AS c

and got an error, because SQL doesn't support that format. This, combined with your sample showing four columns where only three are listed in the query, makes me strongly suspect that this is a subquery in a larger query... in which case, the null/not null problem is probably tangled up in the logic of the full query, and not just this subquery. If my surmises are right, please post the full query for our consideration.

This is actually correct. I figured it out, however, actually putting the answer online could cause security problems. Thank you for everyone's assistance. Technically, there never was a problem with the ISNULL logic. The problem was the way I was implementing the sub-query. — Josh McKearin, Jun 06 '12 at 19:30

Diego · Answer 2 · 2012-06-06T16:22:38.840

1

I assume since CONVERT(smalldatetime,dModLast,101) BETWEEN '2012-03-01' AND '2012-03-10' references the right table, sometimes it can evaluate to NULL and the case is not getting it.

Can you try explicitly doing when (dModLast is null) then 0

EDIT:

case
   when dModLast is null then 0
   when CONVERT(smalldatetime,dModLast,101) BETWEEN '2012-03-01' AND '2012-03-10' then 1 
   else 0 
end

edited Jun 06 '12 at 16:22

answered Jun 06 '12 at 15:56

Diego

34,802
21
91
134

dModLast is definitely coming from the correct table. And using the case when before my CONVERT function is the same as what you are suggesting here (I think.) I can not do `when (dModLast is null) then 0` inside of my convert function. That is why I tried using the ISNULL function, which unfortunately still is not working. – Josh McKearin Jun 06 '12 at 16:04
why cant you do that? it would be the same behavior – Diego Jun 06 '12 at 16:16
I tried it... may have been a syntax error on my part though. What would be the correct syntax for this? – Josh McKearin Jun 06 '12 at 16:19
@jpm0004 It's because you are doing a left join. Look at the other answer, I updated it. – Justin Jun 06 '12 at 16:34
what do you mean by Refresh ? – Diego Jun 06 '12 at 16:37
I was talking to Justin. I tried his answer after he edited it but he didn't know that because he didn't refresh the page before commenting here under your answer. – Josh McKearin Jun 06 '12 at 16:38

Justin · Answer 3 · 2012-06-06T16:21:38.590

1

Change to

ISNULL(SUM(CASE WHEN ISNULL(ca.dModLast,0)=0 THEN 0 ELSE CASE WHEN CONVERT(smalldatetime,ca.dModLast,101) BETWEEN '2012-03-01' AND '2012-03-10' THEN 1 ELSE 0 END END),0) as cnt3

edited Jun 06 '12 at 16:21

answered Jun 06 '12 at 16:06

Justin

2,093
1
16
13

This looked extremely hopeful, but no cigar. Returned the same. – Josh McKearin Jun 06 '12 at 16:13
Tried this as well... still didn't work. I think we are looking at it all wrong. – Josh McKearin Jun 06 '12 at 16:33

ISNULL() is not working... possibly wrong placement?

3 Answers3