6 Replies Latest reply: Dec 13, 2012 3:59 AM by Mahir M. Quluzade RSS

    CASE WHEN statement in DECODE

    Rami Reddy R
      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
          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
            Mahir M. Quluzade
            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
              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
                Rami Reddy R
                Hi theoa,

                Thanks for you answer.
                • 5. Re: CASE WHEN statement in DECODE
                  Rami Reddy R
                  Hi Mahir,

                  Thanks for your answer..:)
                  • 6. Re: CASE WHEN statement in DECODE
                    Mahir M. Quluzade
                    You are welcome !