Results 1 to 11 of 11
  1. #1
    Join Date
    May 2011
    Posts
    11

    Unanswered: CASE statement help

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If A=1 satisfies, SQL wouldn't evaluate the following WHEN conditions.
    It wouldn't be necessary to use BREAK.

    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.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2011
    Posts
    11
    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you tried the examples in these posts which put WHEN conditions into scalar-subqueries?
    Quote Originally Posted by tonkuma View Post
    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 View Post
    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
    ;

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  7. #7
    Join Date
    May 2011
    Posts
    11
    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 01-04-12 at 07:30. Reason: Add Note.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    ;

  10. #10
    Join Date
    May 2011
    Posts
    11
    Thanks!!!
    Tried Example 3 and it worked for me.....

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by madsongtel View Post
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •