ISNULL() function requires two arguments? Simple solution

Question

I have misplaced something in the ISNULL() function and need another set of eyes to find it. I've stared at it so much I've become brainlocked (I know what ISNULL() is, this is just a simple syntax error)

(SELECT tn.teamtext, tn.teamid, ISNULL(sum(ISNULL(case when CONVERT(smalldatetime,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 isactive = 1 AND groupid = 18 AND accountid = 2 AND referteamid1 = 31 AND auditnote <> 'Suspend Case'
  GROUP BY tn.teamtext, tn.teamid) AS c

@jpm0004, your question was ambiguous as it wasn't clearly stated in the body what you were wanting help with. We didn't know if you actually understood what ISNULL() was supposed to do, or if you just needed help tracking down a syntax error. Instead of getting snippy, try to help us help you next time. — Josh Weatherly
– Josh Weatherly, Commented Jun 6, 2012 at 15:07
Alright, I edited it. I thought the question was understood, but I understand how it may have been confusing. Sorry everyone! Thanks for the help! — Josh McKearin
– Josh McKearin, Commented Jun 6, 2012 at 15:13

Kev · Accepted Answer · 2014-07-29 13:16:23Z

2

The ISNULL() function requires two arguments which you can see here:

http://msdn.microsoft.com/library/ms184325.aspx

The first one is the expression being tested, the second is the value to return if the expression tested evaluates to NULL.

Your second ISNULL() function only specifies one parameter:

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

You should try:

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

edited Jul 29, 2014 at 13:16

Kev

120k53 gold badges308 silver badges396 bronze badges

answered Jun 6, 2012 at 15:00

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kev Over a year ago

+1 This is a better answer, complete with reference material.

Kapil Khandelwal · Accepted Answer · 2012-06-06 15:00:49Z

1

Try this:

(SELECT tn.teamtext, tn.teamid, ISNULL(sum(ISNULL(case when CONVERT(smalldatetime,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 isactive = 1 AND groupid = 18 AND accountid = 2 AND referteamid1 = 31 AND auditnote <> 'Suspend Case'
  GROUP BY tn.teamtext, tn.teamid) AS c

answered Jun 6, 2012 at 15:00

Kapil Khandelwal

16.2k3 gold badges49 silver badges55 bronze badges

1 Comment

Kev Over a year ago

Would have been better if you'd explained what was wrong and possibly citing reference material.

Josh Weatherly · Accepted Answer · 2012-06-06 15:02:12Z

1

You have a paren in the wrong spot:

ISNULL(sum(ISNULL(case when CONVERT(smalldatetime,dModLast,101) BETWEEN '2012-03-01' AND '2012-03-10' then 1 else 0 end , 0)), 0) AS cnt3

Notice after the 'end' of your CASE statement

answered Jun 6, 2012 at 15:02

Josh Weatherly

1,7401 gold badge16 silver badges28 bronze badges

Comments

Josh McKearin · Accepted Answer · 2012-06-06 15:04:22Z

0

Here is the answer... I figured it out.

Notice the difference...

Original: ISNULL(sum(ISNULL(case when CONVERT(smalldatetime,dModLast,101) BETWEEN '2012-03-01' AND '2012-03-10' then 1 else 0 end), 0), 0)

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

answered Jun 6, 2012 at 15:04

Josh McKearin

7524 gold badges19 silver badges42 bronze badges

1 Comment

Josh McKearin Over a year ago

After posting the answer... seems 3 other people answered before me... they were all correct. Thanks to all!

Collectives™ on Stack Overflow

ISNULL() function requires two arguments? Simple solution

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related