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

    Unanswered: COALESCE Function conversion

    Hi All,

    i am new to DB2 and i have to convert below query into CASE function, i should not use COALESCE function in my query, inplace of COALESCE function i should use CASE function. can some one please help me

    COALESCE(((Case
    When SUBSTR(L.LOCATION,1,3) = '123' Then
    (Select SUBSTR(LOC_CD,1,3) from POST Where POST_CD = L.POST_TX)
    When SUBSTR(L.Location,1,3) = '234' Then
    (Select REG_CD from RGN Where L.POST_TX BETWEEN CD1 AND CD2)
    Else ' '
    End ) ),( ' ' )) as ADDRESS_CODE from LOCATION_TB L

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... i have to convert below query into CASE function, i should not use COALESCE function in my query, ...
    I couldn't find any reason not to use COALESCE function, except for exercise.
    Could you explain the reason?

    Anyway, your query might be rewritten to...
    Code:
    SELECT
           COALESCE(
              SUBSTR(LOC_CD,1,3)
            , REG_CD
            , ' '
           ) as ADDRESS_CODE
     from  LOCATION_TB L 
     LEFT  OUTER JOIN
           POST
       ON  SUBSTR(L.LOCATION,1,3) = '123'
       AND L.POST_TX = POST_CD
     LEFT  OUTER JOIN
           RGN
       ON  SUBSTR(L.LOCATION,1,3) = '234'
       AND L.POST_TX BETWEEN CD1 AND CD2
    ;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or, calculate SUBSTR(LOCATION,1,3) once.

    Code:
    SELECT
           COALESCE(
              SUBSTR(LOC_CD,1,3)
            , REG_CD
            , ' '
           ) as ADDRESS_CODE
     from  (SELECT L.*
                 , SUBSTR(LOCATION,1,3) AS cd3
             FROM  LOCATION_TB L
           ) L 
     LEFT  OUTER JOIN
           POST
       ON  L.cd3     = '123'
       AND L.POST_TX = POST_CD
     LEFT  OUTER JOIN
           RGN
       ON  L.cd3     = '234'
       AND L.POST_TX BETWEEN CD1 AND CD2
    ;

  4. #4
    Join Date
    May 2011
    Posts
    11
    Thank you ...solved my problem

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

Posting Permissions

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