CODEWITHSUNDEEP
sqlisnull
Josh McKearin
Josh McKearin

Reputation: 742

ISNULL() function requires two arguments? Simple solution

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

Upvotes: 1

Views: 26122

Answers (4)

Gonzalo.-
Gonzalo.-

Reputation: 12682

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)

Upvotes: 2

Josh McKearin
Josh McKearin

Reputation: 742

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)

Upvotes: 0

Josh Weatherly
Josh Weatherly

Reputation: 1740

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

Upvotes: 1

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16144

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

Upvotes: 1

Related Questions