The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

Wondering if anyone has an opinion on the following. I have query where I need to eliminate the data in a field under a certain condition: if the quantity field of a record is 0 then I don't need to see the location field of that same record.

I've set up a case statement as such:

case when quantity = 0 then NULL when quantity > 0 then [Location] end) as [Location]

Then I saw if/else and set this up:

if quantity > 0 then [Location] else NULL endif as [Location]

They appear to pull and display the same data. Is there a benefit to either?

Thank you.

asked 01 Mar '11, 16:22

zippidydo's gravatar image

accept rate: 0%

In general, I would prefer

  • if-expressions for typical boolean comparisons and
  • case-expressions for more than two cases.

That's comparable to the question whether to use if or switch/case in C/C++ and similar programming languages.

So, for your sample, I would suggest to use the if-expression.

Note that there's another variant of the case-expression (though not possible in your sample):

CASE expression0
WHEN expression1 THEN expression2, ...
WHEN expression3 THEN expression4, ...
                 ELSE expressionX

This does test expression0 against a list of expressions (expression1, expression3, ...) and is somewhat shorther than the CASE WHEN search-condition ... syntax.

Just to add:

When the expression in the ELSE clause (both with IF and CASE) should return NULL (as in your example), then you can leave out the ELSE clause - omiting it is identical to ELSE NULL. So your expression could be reduced to:

if quantity > 0 then [Location] endif as [Location]

As always, leaving out the ELSE part may decrease the readibility based on whether one is used to the default NULL result or not.

permanent link

answered 01 Mar '11, 17:30

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 02 Mar '11, 09:13

Just to add one big difference between IF and CASE expressions with ELSE clauses I came about based on Siger's FAQ on CASE exporessions with NULL values:

  • With IF expressions, if the condition is UNKNOWN, the IF expression returns NULL - it does not return the value from the ELSE clause (which has been a surprise to me now and then...).

  • With CASE expressions, if expression0 is NULL, then the value from the ELSE clause will be returned.

A simple repro:

   declare n int = null;
      if n = 1 then 'true' else 'false' end if, 
      case n when 1 then 'true' else 'false' end case,
      case when n = 1 then 'true' else 'false' end case;

returns NULL, 'false', 'false'

permanent link

answered 08 Aug '13, 03:56

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 01 Mar '11, 16:22

question was seen: 1,274 times

last updated: 08 Aug '13, 03:56