This discussion is archived
6 Replies Latest reply: Dec 13, 2012 1:59 AM by MahirM.Quluzade RSS

CASE WHEN statement in DECODE

RamiReddyR Newbie
Currently Being Moderated
SELECT v_startdate, v_enddate,
(CASE WHEN SYSDATE BETWEEN v_startdate AND v_enddate THEN
‘active’
ELSE
‘inactive’
END) status
FROM correction_tab;

Could you kindly guide us as to how can we use DECODE and get the desired output.


Thanks,
Rami Reddy.
  • 1. Re: CASE WHEN statement in DECODE
    Purvesh K Guru
    Currently Being Moderated
    One way of doing this:
    with data as
    (
      select sysdate - 2 st_date, sysdate - 1 ed_date from dual
    )
    select decode(sign(sysdate - ed_date), -1, decode(sign(sysdate - st_date), 1, 'TRUE', 'FALSE'), 'FALSE') val
      from data;
    VAL   
    ----- 
    FALSE
    
    with data as
    (
      select sysdate - 2 st_date, sysdate + 1 ed_date from dual
    )
    select decode(sign(sysdate - ed_date), -1, decode(sign(sysdate - st_date), 1, 'TRUE', 'FALSE'), 'FALSE') val
      from data;
    VAL   
    ----- 
    TRUE 
    Use your values instead of TRUE/FALSE; These were just used for Indication purpose.
    However, whats wrong in using Case? It is much more easier to read and understand for Range Comparison.
  • 2. Re: CASE WHEN statement in DECODE
    MahirM.Quluzade Guru
    Currently Being Moderated
    976681 wrote:
    SELECT v_startdate, v_enddate,
    (CASE WHEN SYSDATE BETWEEN v_startdate AND v_enddate THEN
    ‘active’
    ELSE
    ‘inactive’
    END) status
    FROM correction_tab;

    Could you kindly guide us as to how can we use DECODE and get the desired output.


    Thanks,
    Rami Reddy.
    Then you can try :
     SELECT  v_startdate, v_enddate,
                    DECODE
                    (SIGN(v_startdate -sysdate) + SIGN(v_enddate -sysdate),
                     0, 
                    'active',
                    'inactive') status
     FROM correction_tab;
    Regards
    Mahir M. Quluzade
  • 3. Re: CASE WHEN statement in DECODE
    theoa Pro
    Currently Being Moderated
    You can, like this.
    However, the CASE seems a lot clearer to me so why bother with a DECODE?
    sql> with correction_tab as
      2    ( select trunc(sysdate)-1 as v_startdate, trunc(sysdate)   as v_enddate from dual
      3    union all
      4      select trunc(sysdate)   as v_startdate, trunc(sysdate)+1 as v_enddate from dual
      5    union all
      6      select trunc(sysdate)+1 as v_startdate, trunc(sysdate)+2 as v_enddate from dual
      7    )
      8  SELECT v_startdate
      9  ,      v_enddate
     10  ,      CASE
     11           WHEN SYSDATE BETWEEN v_startdate AND v_enddate
     12             THEN 'active'
     13           ELSE 'inactive'
     14         END status
     15  ,      decode ( sign(sysdate-v_startdate), 1, decode(sign(v_enddate-sysdate), 1, 'active', 'inactive'), 'inactive')
     as status2
     16  FROM correction_tab
     17  /
    
    V_STARTDA V_ENDDATE STATUS   STATUS2
    --------- --------- -------- --------
    12-DEC-12 13-DEC-12 inactive inactive
    13-DEC-12 14-DEC-12 active   active
    14-DEC-12 15-DEC-12 inactive inactive
  • 4. Re: CASE WHEN statement in DECODE
    RamiReddyR Newbie
    Currently Being Moderated
    Hi theoa,

    Thanks for you answer.
  • 5. Re: CASE WHEN statement in DECODE
    RamiReddyR Newbie
    Currently Being Moderated
    Hi Mahir,

    Thanks for your answer..:)
  • 6. Re: CASE WHEN statement in DECODE
    MahirM.Quluzade Guru
    Currently Being Moderated
    You are welcome !

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points