Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    17

    Unanswered: How to convert DECODE to CASE???

    hello,

    I have an ORACLE SQL statement like below;

    TK.HESAPKODU||'.'||TK.DUZEYKODU1
    ||DECODE(TK.DUZEYKODU2,NULL,'','.'||TK.DUZEYKODU2)
    ||DECODE(TK.DUZEYKODU3,NULL,'','.'||TK.DUZEYKODU3)
    ||DECODE(TK.DUZEYKODU4,NULL,'','.'||TK.DUZEYKODU4)
    ||DECODE(TK.DUZEYKODU5,NULL,'','.'||TK.DUZEYKODU5)
    ||DECODE(TK.DUZEYKODU6,NULL,'','.'||TK.DUZEYKODU6) KODU

    How can i change it to DB2 CASE???


    thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If changed straighforward to CASE, it would be like this:
    CASE WHEN TK.DUZEYKODU2 IS NULL THEN '' ELSE '.'||TK.DUZEYKODU2 END

    But, in this case, COALESCE would be simpler.
    Like this:
    Code:
    TK.HESAPKODU||'.'||TK.DUZEYKODU1
    ||COALESCE('.'||TK.DUZEYKODU2, '') 
    ||COALESCE('.'||TK.DUZEYKODU3, '') 
    ||COALESCE('.'||TK.DUZEYKODU4, '') 
    ||COALESCE('.'||TK.DUZEYKODU5, '') 
    ||COALESCE('.'||TK.DUZEYKODU6, '') KODU

  3. #3
    Join Date
    Jan 2003
    Posts
    1,636
    Provided Answers: 1
    raysefo,
    you will have much less problem if you use standard SQL in Oracle too. Decode is Oracle specifics, coalesce is SQL standard. See also this blog post:
    http://mennan.kagitkalem.com/NVLCOALESCEDECODE.aspx
    Hope it helps,
    Grofaty

Posting Permissions

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