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!

How to achieve case functionality in decode.,

Discussion in 'SQL PL/SQL' started by Vicky, Nov 6, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi,

    select (case when to_char(sysdate, 'HH24:MI') between '09:30' and '06:30'
    then 'Work Hours'
    else 'O-T'
    end) as "LOG"
    from dual


    LOG
    ----------
    O-T


    Could u tell me, how can I achieve the same result as above using decode statement.,/
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    For example :

    1)

    Code (SQL):


    SELECT
        CASE WHEN sysdate  BETWEEN to_date(to_char(sysdate,'dd.mm.yyyy')||' 09:30','dd.mm.yyyy hh24:mi')
                                   AND
                                   to_date(to_char(sysdate,'dd.mm.yyyy')||' 18:30','dd.mm.yyyy hh24:mi')
        THEN '
            Work Hours'

        ELSE
            'O-T'
    END AS "LOG"
    FROM dual;


     

    2)
    TS - short time
    Code (SQL):



    SELECT

        CASE WHEN to_dsinterval('0 '||to_char(sysdate,'ts'))   BETWEEN TO_DSINTERVAL('0 9:30:0')
                                   AND
                                   TO_DSINTERVAL('0 18:30:0')
        THEN '
            Work Hours'

        ELSE
            'O-T'
    END AS "LOG"
    FROM dual;


     


    3)
    Code (SQL):


    SELECT
        CASE WHEN to_dsinterval('0 '||to_char(sysdate,'hh24:mi:ss'))  
             BETWEEN
                    numtodsinterval(9.50,'HOUR')
                    AND
                    numtodsinterval(18.50,'HOUR')
        THEN '
            Work Hours'

        ELSE
            'O-T'
        END AS "LOG"
    FROM dual;


     
     
    Vicky likes this.
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Sergey .,

    Is it possible to achieve the same result using "DECODE" Statement.,
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    First of all, there is a small mistake in your CASE example: the time should be
    instead of
    (I think Sergey already corrected it in his post).

    As for the DECODE version, try the following:

    Of course, it's not as elegant and simple as the CASE version.
     
    Vicky likes this.