sqlcase

If there are multiple possible outcomes for nested case when in SQL which one is chosen as the end path?

I'd like to preface this with the following:

  1. the data tables I'm going to describe are 3rd party I have no control over why they were created in such away
  2. I don't have the power to dictate to other departments how they use the tables but I'm asking this question to help point out the pit falls should there not be a consensus on how to the tables are used.
  3. I've tried describing this as best I can without publishing too much sensitive information so apologies if some of it is vague I've tried my best.

Our company has a table where they can store certain data. The important columns are:

edate: datetime.
field 1,2,3,4: all floats.
customer ID: int

This table is in a 3rd party schema and frontend allows users to store data in any of the floats - again I can't control that.

I've been asked to run tests to find out any customers where there is no data (either zero or null in the floats for certain years).

To facilitate this I've created a parameters table (alias sp) for each customer with the following:

customer Id
4 booleans

The 4 booleans are one for each of the float fields and one should be set to true to indicate which of the float fields has been used for that customer (the same floatfield should be used across each year).

-- WorkAnalysis.dbo.fnCheckvaluesIsvalid():
CASE 
    WHEN sp.floatfield1 = 1 THEN floatfield1 
    WHEN sp.floatfield2 = 1 THEN floatfield2 
    WHEN sp.floatfield3 = 1 THEN floatfield3 
    WHEN floatfield4 = 1 THEN floatfield4 
END = 1

If there was an instance where some end user marked more than one of parameter booleans as true? For example let's say the end user has marked both sp.floatfield1 and sp.floatfield4 as true which of the floatfields would be passed to the function for checking? I'm familiar with how nested if work in languages such as VB.NET and C# as in they flow from line to line top to bottom as written but I don't know how nested case when expressions are handled in SQL.

Solution

  • The CASE expression goes through conditions and returns a value when the first encountered (from left to right) condition is met.

    See https://www.w3schools.com/sql/sql_case.asp

    Thus in your case (CASE WHEN sp.floatfield1 = 1 THEN floatfield1 […] WHEN floatfield4 = 1 THEN floatfield4 END),
    floatfield1 will be checked.