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.

for example

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?

thanks.

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