30

Given the following body of a case statement:

1    WHEN r.code= '00'                        then 'A1'
2    WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'   <
3    WHEN r.code ='0120'                      then 'A3'
4    WHEN r.code ='01'                        then 'A4'   <
5    WHEN r.code ='1560'                      then 'A5'
6    WHEN r.code ='1530'                      then 'A6'
7    WHEN r.code ='1550'                      then 'A7'

I'm assuming line 2 will always execute before line 4? Then I read statements like 'SQL is a declarative language, meaning that it tells the SQL engine what to do, not how' in

Order Of Execution of the SQL query

and wonder if this also relates to the order of execution in the CASE statement. Essentially, can i leave the code above as it is without having to change line 4 to 

4    WHEN r.code ='01' AND r.source != 'PXWeb' then 'A4'
Community
  • 1
  • 1
The Ghost
  • 611
  • 1
  • 6
  • 14

3 Answers3

32

The value that is returned will be the value of the THEN expression for the earliest WHEN clause (textually) that matches. That does mean that if your line 2 conditions are met, the result will be A2.

But, if your THEN expressions were more complex than just literal values, some of the work to evaluate those expressions may happen even when that expression is not required.

E.g.

 WHEN r.code= '00'                        then 'A1'
 WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2'
 WHEN r.code ='0120'                      then 1/0
 WHEN r.code ='01'                        then 'A4'

could generate a division by zero error even if r.code isn't equal to 0120, and even if it's equal to 00, say. I don't know what the standard has to say on this particular issue but I know that it is true of some products.

Damien_The_Unbeliever
  • 220,246
  • 21
  • 302
  • 402
  • 1
    I did a quick lookup in the 2008 draft standard and curiously it doesn't seem to say anything at all about order of cases. It is, however, customary in computer languages in general, that the cases in case expressions are tested in the order as specified. – Erwin Smout Jul 02 '14 at 11:35
  • 1
    @ErwinSmout - I can't believe I never followed up on your comment. The difference here is that SQL is a *declarative* language. Not *procedural*. It's specified in terms of the overall logic and, so long as it still logically computes the result it's asked to, the evaluation order should not matter. Unfortunately, some systems (SQL Server is notable here) produce errors that *should be logically precluded* from affecting the final results. Clash between ideals/reality. – Damien_The_Unbeliever Aug 14 '18 at 17:10
  • "Declarative" was intended with respect to the physical access to the data (the "pointer chasing" that was so prevalent before the RM). And obviously you can't escape from the fact that case expressions are really just a concealed way of writing nested IF/THEN/ELSEs which inevitably has, in a certain sense, "more procedurality" to it than some other language constructs. I agree it would be better if "evaluation order didn't matter" but unfortunately proving that is in general not doable for any compiler. Inevitable and so language definers have to take the consequences. – Erwin Smout Aug 15 '18 at 12:04
  • @ErwinSmout - well, it was also designed to help expose *parallelism*. That's why in many places in SQL, the evaluation order is considered to be "as if all expressions are being computed in parallel". I.e. it's why `SELECT` clause expressions cannot depend on other expressions in the same clause. – Damien_The_Unbeliever Sep 04 '18 at 14:40
9

Never mind:

"The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied."

http://msdn.microsoft.com/en-gb/library/ms181765.aspx

The Ghost
  • 611
  • 1
  • 6
  • 14
  • 2
    But note the caveat lower down, which is what my answer is warning about: "In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input" – Damien_The_Unbeliever Jul 02 '14 at 11:01
2

AFAIK, The order of CASE evaluation will be the order you have specified in your query. So in your case the order of evaluation will be 1,2,3,4 ... , 7

can i leave the code above as it is without having to change line 4 to

You can change your 2nd CASE and include an ELSE part like below which will take care of 4th CASE evaluation and you can remove the 4th evaluation altogether

2    WHEN r.code ='01' AND r.source = 'PXWeb' then 'A2' ELSE 'A4'
Rahul
  • 71,392
  • 13
  • 57
  • 105
  • This solution will output 'A4' when r.code = '02' which is not the expected output as per the question – Erdnase Jan 19 '19 at 07:08