COALESCE Function conversion

madsongtel · #1 (**permalink**) 01-02-12, 10:33

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

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

Quote:

... 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
;

tonkuma · #3 (**permalink**) 01-02-12, 12:24

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
;

madsongtel · #4 (**permalink**) 01-02-12, 12:26

Thank you ...solved my problem

tonkuma · #5 (**permalink**) 01-02-12, 13:45

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 
;

Internet Services

Web Development

Digital Marketing

Consumer Tech