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,605
    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
  •