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 |
In general, I would prefer
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):
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:
As always, leaving out the ELSE part may decrease the readibility based on whether one is used to the default NULL result or not. answered 01 Mar '11, 17:30 Volker Barth |
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:
A simple repro: begin declare n int = null; select 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; end; returns NULL, 'false', 'false' answered 08 Aug '13, 03:56 Volker Barth |