When Then order of operations

Question

I'm trying to get the correct order of operation in SQL for my case statement and every combination I try seems to be failing me.

When I run my simple statement of:

select CustomerName, AccountNumber, AccountID, VendorID, ClientID
from data

I yield results of:

CustomerName    AccountNumber   AccountID   VendorID    ClientID
Customer 1      3870465001                              38
Customer 2      5135550000                  48          58
Customer 3      8335 10 001     58          55          45

Here is what I'm currently trying:

select CustomerName, AccountNumber, 
  case  
    when AccountID = '' then VendorID
    when AccountID = '' and VendorID = '' then ClientID
    else AccountID 
  end as EffectiveID
from data

and hoping to see:

CustomerName    AccountNumber   EffectiveID 
Customer 1      3870465001      38
Customer 2      5135550000      48          
Customer 3      8335 10 001     58

but I only end with the result for Customer 2

Switch the order of the first 2 branches of CASE. – forpas Sep 16 '20 at 15:44 — forpas, Sep 16 '20 at 15:44

Salil · Accepted Answer · 2020-09-16T16:06:33.207

Your second statement in case will never execute, so you have to switch it with first

SELECT CustomerName, AccountNumber, 
  CASE  
    WHEN AccountID = '' AND VendorID = '' THEN ClientID
    WHEN AccountID = '' THEN VendorID
    ELSE AccountID 
  END AS EffectiveID
FROM data

If you have to check null as well as empty you have to modify it as mentioned here

WHEN (AccountID IS NULL OR AccountID = '') AND (VendorID IS NULL OR VendorID = '') THEN ClientID

aRvi · Answer 2 · 2020-09-16T16:21:07.083

0

select CustomerName, AccountNumber, 
  case  
    when AccountID != '' then AccountID
    when VendorID != '' then VendorID
    else ClientId 
  end as EffectiveID
from data

edited Sep 16 '20 at 16:21

answered Sep 16 '20 at 15:59

aRvi

2,203
1
14
30

I think it will not work as expected when `VendorID` is empty & AccountID, ClientID are not empty. – Salil Sep 16 '20 at 16:07
@Salil CASE WHEN follows the order of execution with the earliest first. If the first statement `VendorID = ''` is not satisfied it will go to the next statement. In short, it works like `if, else if, else` statement – aRvi Sep 16 '20 at 16:17
1

Yeah, but when `VendorID ` is empty it will display `ClientID` even if `AccountID` is present. – Salil Sep 16 '20 at 16:19
3

While code-only answers might answer the question, you could significantly improve the quality of your answer by providing context for your code, a reason for why this code works, and some references to documentation for further reading. From [answer]: _"Brevity is acceptable, but fuller explanations are better."_ – Pranav Hosangadi Sep 16 '20 at 17:50

When Then order of operations

2 Answers2