Incorporating ISNULL into a CASE expression

Question

I have the following CASE expression and the ISNULL portion is not registering:

CASE WHEN IsNull(2010) THEN 0 END) * 
CASE WHEN IsNumeric([Dur_In_Hours]) = 1 THEN 
  CAST([Dur_In_Hours] AS FLOAT) ELSE 0 END) AS Cost

Also it is always useful to explain what things like "not registering" mean. Do you get an error message? If so, what is it? — Aaron Bertrand, Jan 24 '13 at 15:01
Then it is a terrible column name. Not only does it start with a number (meaning it will always have to be escaped with `[square brackets]`, but it also begs for normalization, and is not descriptive in the least. It's something for the year 2010, but what? — Aaron Bertrand, Jan 24 '13 at 15:02
Finally, why oh why are you storing duration in hours in a string-based column? — Aaron Bertrand, Jan 24 '13 at 15:05
And why are you casting what is presumably an integer or a 2-digit decimal, multiplied by an integer, as float? — Aaron Bertrand, Jan 24 '13 at 15:21

score 5 · Accepted Answer · answered Jan 24 '13 at 14:57

Unfortunately, you are using the IsNull() function wrong. If you are testing if a column is null then you will use:

CASE 
  WHEN 2010 is null 
  THEN 0 
END 
* 
CASE 
  WHEN IsNumeric([Dur_In_Hours]) =1 
  THEN CAST([Dur_In_Hours] AS FLOAT) 
  ELSE 0 
END AS Cost

If you want to use IsNull() then you can use IsNull([2010], 0) to replace that CASE expression:

IsNull([2010], 0)
* 
CASE 
  WHEN IsNumeric([Dur_In_Hours]) =1 
  THEN CAST([Dur_In_Hours] AS FLOAT) 
  ELSE 0 
END AS Cost

Or you can use COALESCE to replace the first CASE:

COALESCE([2010], 0)
* 
CASE 
  WHEN IsNumeric([Dur_In_Hours]) =1 
  THEN CAST([Dur_In_Hours] AS FLOAT) 
  ELSE 0 
END AS Cost

Aaron Bertrand · Answer 2 · 2021-12-28T03:23:32.233

2

CASE is an expression, not a statement. It's a subtle nit-pick but the distinction is important, IMHO.

I think you meant:

CASE WHEN [2010] IS NULL THEN 0 END

But that doesn't make any sense at all unless you are hard-coding 2010 there but plan to use a variable or column name later.

ISNULL is something completely different than what you are after (at least as far as I can tell). It does not work the same way as it does in Access. For that reason I usually recommend COALESCE instead, which functions similarly in most cases. I wrote this tip last year to help differentiate:

Deciding between COALESCE and ISNULL in SQL Server

edited Dec 28 '21 at 03:23

answered Jan 24 '13 at 14:56

Aaron Bertrand

272,866
37
466
490

I have it down to 'CASE WHEN [2010] IS NULL THEN 0 ELSE CAST([2010] AS FLOAT)*CASE WHEN IsNumeric([Dur_In_Hours]) =1 THEN CAST([Dur_In_Hours] AS FLOAT ELSE 0 END) AS Cost' ANd I am getting Unable to Parse Query Text – Andrew Jan 24 '13 at 15:12
@Andrew you are missing the `END` on the first `CASE` statement. You always have to have `CASE..WHEN..THEN..END` – Taryn Jan 24 '13 at 15:14
It is all one large case statement though, I tried adding the END and it did not work – Andrew Jan 24 '13 at 15:16
@Andrew Then you should post the full statement. Posting bits and pieces of the query will make it hard to debug. Also you should format your query, that will help you find the issues. – Taryn Jan 24 '13 at 15:17
What tool is saying "Unable to Parse Query Text"? Maybe you should stop using that tool, I don't think it is doing you any favors. A proper query window in Management Studio with IntelliSense enabled will highlight all of these trivial and repeated syntax errors... – Aaron Bertrand Jan 24 '13 at 15:19

Incorporating ISNULL into a CASE expression

2 Answers2