1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

getting trouble in converting nested decode in oracle to case statements in sql

Discussion in 'SQL PL/SQL' started by hari508, Jun 7, 2015.

  1. hari508

    hari508 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    can anyone convert the below code to sql please......

    select DECODE ('Y',
    DECODE ('SUSPENDED', phg.current_status, 'Y', 'N'), 'SUSPENDED',
    DECODE ('N', phg.outgoing_required, 'Y', 'N'), 'ORIG_DENIED',
    phg.ild_required, 'INTERNATIONAL',
    phg.nld_required, 'LONG_DISTANCE',
    phg.icldrequired, 'STATE_LONG_DISTANCE',
    'LOCAL_ONLY'
    ) service_level
    from table1 phg
     
  2. hari508

    hari508 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    Does any one have idea in converting the above decode function to sql case statements?if so please reply...
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Two things:

    First -- expecting an answer to your question in an hour -- especially on the weekend is optimistic.
    Second -- Posting your SQL question in the SQL forum would make it more likely to get a response faster.

    That said -- I read the logic of the supplied DECODE as follows:

    IF phg.current_status = 'SUSPENDED' THEN RETURN 'SUSPENDED'
    ELSIF phg.outgoing_required = 'N' THEN RETURN 'ORIG_DENIED'
    ELSIF phg.ild_required = 'Y' THEN RETURN 'INTERNATIONAL'
    ELSIF phg.nld_required = 'Y' THEN RETURN 'LONG_DISTANCE'
    ELSIF phg.icldrequired = 'Y' THEN RETURN 'STATE_LONG_DISTANCE'
    ELSE RETURN 'LOCAL_ONLY'

    That would make the CASE something like the following. Without the table data, there is no feasible way for me to test it.

    Code (Text):
    SELECT  
      (CASE
         WHEN phg.current_status = 'SUSPENDED' THEN 'SUSPENDED'
         WHEN phg.outgoing_required = 'N' THEN 'ORIG_DENIED'
         WHEN phg.ild_required = 'Y' THEN 'INTERNATIONAL'
         WHEN phg.nld_required = 'Y' THEN 'LONG_DISTANCE'
         WHEN phg.icldrequired = 'Y' THEN RETURN 'STATE_LONG_DISTANCE'
         ELSE 'LOCAL_ONLY'
      END) service_level
    FROM  table1 phg;
     
  4. hari508

    hari508 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi ocprep,

    Thanks for your reply.