CASE statement help

madsongtel · #1 (**permalink**) 01-04-12, 02:42

Hi All,

i am having below query

CASE when A=1 Then 'X' when A=2 Then 'Y' When A=3 Then 'Z' Else 'W' End

If A=1 satisfies Then it should jump out from the CASE statement and it should not execute next A=2 condition.
My requirement is need to put break statement after each When clause, i tried but giving syntax problem.
can we use BREAK in CASE statement?

tonkuma · #2 (**permalink**) 01-04-12, 02:57

If A=1 satisfies, SQL wouldn't evaluate the following WHEN conditions.
It wouldn't be necessary to use BREAK.

Quote:

CASE when A=1 Then 'X' when A=2 Then 'Y' When A=3 Then 'Z' Else 'W' End

'X' in your example is not a statement.
So, it must be CASE expression.

How did you used the CASE expression(or CASE statement as you wrote)?
Please publish whole statements using the CASE.

stolze · #3 (**permalink**) 01-04-12, 03:13

I don't remember the exact details, but I dimly recall that DB2 evaluated all expressions and predicates in a CASE expression. In the given example, the expressions 'X', 'Y', 'Z', and 'W' were computed as well as all predicates in the WHEN clauses. So there was, in fact, no early termination. The result of the CASE expression was still correct because DB2 picked the correct result expression.

At that time, I had expressions with side effects or non-deterministic expressions in the THEN branches and wanted to avoid their computation if the THEN clause didn't match. I could work around it like this:

Code:

CASE
    WHEN A = 1 THEN ( SELECT 'X' FROM sysibm.sysdummy1 WHERE A = 1 )
    WHEN A = 2 THEN ( SELECT 'Y' FROM sysibm.sysdummy1 WHERE A = 2 )
    WHEN A = 3 THEN ( SELECT 'Z' FROM sysibm.sysdummy1 WHERE A = 3 )
    ELSE ( SELECT 'W' FROM sysibm.sysdummy1 WHERE A NOT IN (1, 2, 3 ) )
END

I'm not sure if this behavior has changed since then - it's been a few years ago. I also don't know if DB2 is really evaluating all predicates in the WHEN clauses or if that just applied to the THEN expressions.

In any case, it would be good to know the whole statement and to see what kind of predicates are used for the different branches. In the example above, I wouldn't even start thinking about such simple predicates in the tests.

madsongtel · #4 (**permalink**) 01-04-12, 04:53

Thanks Tonkuma and Stolze!!!
My exact query is

Select A.CNUM, COALESCE(((Case
When A.ADDR = '1' Then
(Select DCD from GRAPH Where AREA = a.TEXT)
When A.ADDR = '2' Then
(Select DCD from POST Where a.TEXT BETWEEN IDMIN AND IDMAX)
Else '3'
End
) ),( '4' )) as TERMINATE
FROM
CARD_MEMBER A

in CARD_MEMBER table ADDR is having value 1 Then TERMINATE column should return DCD value from GRAPH table for matching AREA column, but in POST table for DCD value has two records for Between condition, so query is giving -811 error.
If ADDR is 1 then it should satisfy first when clause and CASE statement should jump out of it, but it is not happening. Second expression is executing even if it not satisfies second when caluse.

SO i am planning to put break after each When statement.
Guess i gave clear information, if you need more info please let me know.

tonkuma · #5 (**permalink**) 01-04-12, 05:37

Did you tried the examples in these posts which put WHEN conditions into scalar-subqueries?

Quote:

Originally Posted by tonkuma

Another simple way is to put WHEN condition into scalar-subquery, like...

Code:

SELECT
       COALESCE(
          (Select SUBSTR(LOC_CD,1,3)
            from  POST
            Where SUBSTR(L.LOCATION,1,3) = '123'
              AND POST_CD = L.POST_TX
          )
        , ' '
       ) as ADDRESS_CODE
 from  LOCATION_TB L

or

Quote:

Originally Posted by tonkuma

Sorry!
I forgot a simple modification for your original query, like...

Code:

SELECT
       COALESCE(
          (SELECT SUBSTR(LOC_CD,1,3)
            FROM  POST
            WHERE SUBSTR(L.LOCATION,1,3) = '123'
              AND L.POST_TX = POST_CD
          )
        , (SELECT REG_CD
            FROM  RGN
            WHERE SUBSTR(L.LOCATION,1,3) = '234'
              AND L.POST_TX BETWEEN CD1 AND CD2
          )
        , ' '
       ) as ADDRESS_CODE
 from  LOCATION_TB L 
;

Anyway, try this...

Example 1:

Code:

Select A.CNUM
     , COALESCE(
          (Select DCD
            from  GRAPH
            Where A.ADDR = '1'
              AND AREA = a.TEXT
          )
        , (Select DCD
            from  POST
            Where A.ADDR = '2'
              AND a.TEXT BETWEEN IDMIN AND IDMAX
          )
        , (SELECT dcd
            FROM  (VALUES '3') t(dcd)
            WHERE A.ADDR NOT IN('1' , '2')
          )
        , '4'
       ) as TERMINATE 
 FROM
       CARD_MEMBER A
;

tonkuma · #6 (**permalink**) 01-04-12, 05:55

Another example, but i'm not sure it is better than Example 1.

Example 2:

Code:

Select A.CNUM
     , (SELECT COALESCE(r1.dcd , r2.dcd , r3.dcd , r4.dcd)
         FROM  (VALUES '4') r4(dcd)
         LEFT  OUTER JOIN
               GRAPH        r1
           ON  A.ADDR  = '1'
           AND r1.AREA = a.TEXT
         LEFT  OUTER JOIN
               POST         r2
           ON  A.ADDR  = '2'
           AND a.TEXT  BETWEEN r2.IDMIN AND r2.IDMAX
         LEFT  OUTER JOIN
               (VALUES '3') r3(dcd)
           ON  A.ADDR  NOT IN('1' , '2')
       ) as TERMINATE
 FROM
       CARD_MEMBER A
;

madsongtel · #7 (**permalink**) 01-04-12, 06:15

Thanks for the prompt response Tonkukma!!!

not tried all those queries...Example 1 also will fail at executing the second when clause only.
i should keep the query like that only...dont have rights to change to me...if it is simple change then i can request them to alter the statement.

cant we give break statement after when clause in CASE statement?
please suggest the ways in CASE statement only

tonkuma · #8 (**permalink**) 01-04-12, 06:24

Quote:

Example 1 also will fail at executing the second when clause only.

Please publish sample data which failed the Example 1.
Don't conclude without trying the queries.

Note: I thought that those queries looks like Stolze's solution.

tonkuma · #9 (**permalink**) 01-04-12, 08:38

A little easier than Example 1.

Example 3:

Code:

Select A.CNUM
     , COALESCE(
          (Select DCD
            from  GRAPH
            Where A.ADDR = '1'
              AND AREA = a.TEXT
          )
        , (Select DCD
            from  POST
            Where A.ADDR = '2'
              AND a.TEXT BETWEEN IDMIN AND IDMAX
          )
        , CASE
          WHEN A.ADDR NOT IN('1' , '2') THEN
               '3'
          ELSE '4'
          END
       ) as TERMINATE 
 FROM
       CARD_MEMBER A
;

madsongtel · #10 (**permalink**) 01-04-12, 08:52

Thanks!!!
Tried Example 3 and it worked for me.....

stolze · #11 (**permalink**) 01-04-12, 12:03

Quote:

Originally Posted by madsongtel

cant we give break statement after when clause in CASE statement?
please suggest the ways in CASE statement only

Why would you want to do that? Such a construct would potentially restrict the optimizer from exploiting parallelism on such branches.

p.s: You're thinking in procedural ways like most programming languages do things. SQL is set-oriented and, thus, closer to functional programming models.

Internet Services

Web Development

Digital Marketing

Consumer Tech