DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Decode statement

  1. #1
    Join Date
    Feb 2002
    Posts
    25

    Decode statement

    Hello,

    I need to write the following decode statement and I am having trouble getting it right:
    if an end_date is null or less than today then return X, else return Y.

    Can anyone help,

    Thanks in advance,

    Boris Volf

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Example:

    Code:
    ccreate table test_decode(a date, b number);
    insert into test_decode (a, b) values(trunc(sysdate-5),1);
    insert into test_decode (a, b) values(trunc(sysdate+5),2);
    insert into test_decode (a, b) values(to_date(null),3);
    
    SQL> select sysdate, a, b, decode(a,null,'X',decode(sign(a-sysdate),-1,'X','Y'))
    from test_decode;
      2  
    SYSDATE   A		     B D
    --------- --------- ---------- -
    18-NOV-02 23-NOV-02	     2 Y
    18-NOV-02		     3 X
    18-NOV-02 13-NOV-02	     1 X
    Last edited by Shestakov; 11-18-2002 at 04:43 PM.

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use case

    something like

    Code:
    select
      case 
      when end_date is null 
           or end_date < trunc(sysdate)
      then 'X'
      else 'Y'
      end end_date
    from table;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width