Given a SQL Situation expression with nested Situation expressions, I am searching for the easiest method to emulate a C break or Python pass inside a nested Situation expression in order to assess the relaxation from the outer WHEN options.
For instance, imagine
select CASE WHEN C1 THEN CASE WHEN CA THEN 100 WHEN CB THEN 200 -- implied ELSE NULL END WHEN C2 THEN CASE WHEN CC THEN 300 WHEN CD THEN 400 ELSE 500 END WHEN C3 THEN CASE WHEN CE THEN 600 END WHEN C4 ... etc
think that conditions C1 and C3 are generally true, C2 and C4 are false. also think that CA and CB are false, CE holds true.
The interior situation for C1, takes the implied ELSE NULL. Let me functionally have the ability to say ELSE BREAK and permit C2, C3, etc to become examined included in the outer situation. The preferred outcome is 600.
The easiest way I have found would be to replace the outer situation having a COALESCE of all of the inner cases.
COALESCE( CASE C1 THEN CASE WHEN CA THEN 100 WHEN CB THEN 200 END END , CASE C2 THEN CASE WHEN CC THEN 300 WHEN CD THEN 400 ELSE 500 END END , CASE C3 THEN CASE WHEN CE THEN 600 END END , ... etc )
Anybody produce other ideas? With several amounts of Situation, the origin code fills up in dimensions which causes it to be harder to keep.
I am using standard SQL 2008, no extensions as you will dsicover in T/SQL or SQL/PLUS etc.
You may have the means to format my solution to really make it most readable?
In sql the very best factor to complete is make use of a table along with a join, so inside your example produce a research table such as this (row column is so I'm able to reference later)
Row C1 C2 C3 CA CB CC CD CE Value 1 T F F T F F F F 100 2 T F F F T F F F 200 3 F T F F T F F F 300 4 F T F F F F T F 400 5 F T F T F T F T 500 6 F F T F F F F T 600 ....
Your sql statement may be like this
SELECT l.[value] FROM table t JOIN lookup l ON (t.C1 = l.C1 AND t.C2 = l.C2 AND t.C3 = l.C3) AND (t.CA = l.CA OR t.CB = l.CB OR t.CC = l.CC OR t.CD = l.CD OR t.CE = l.CE)
In my opinion this is actually the logic you'll need for the inner as well as your outer.
Notice row 5 since the last 5 are ORed within the join this can permit the research to do something because the ELSE inside your inner cases.
You can simply have all ands within the join along with a "complete" research table -- this can be better to debug and use.
One of the ways is:
select CASE WHEN C1 AND CA THEN 100 WHEN C1 AND CB THEN 200 --- simulated BREAK WHEN C2 THEN CASE WHEN CC THEN 300 WHEN CD THEN 400 ELSE 500 END WHEN C3 AND CE THEN 600 --- simulated BREAK WHEN C4 ... etc