I'd like to preface this with the following:
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.
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.