Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Between function in Decode statement

Re: Between function in Decode statement

From: Saeed <sr_ng_at_goawaynms-sys-lts.demon.co.uk>
Date: Sat, 3 Jan 2004 00:20:29 +0000
Message-ID: <fVK$NBKNrg9$EwOc@nms-sys-ltd.demon.co.uk>


In message <bsur9i$1bamn$1_at_ID-157477.news.uni-berlin.de>, DFN <Iam_at_cyberspace.net> writes
>For example, if the difference is between:
>0 and 30 days assign a null value for that record,
>31 and 60 days assign a value of 30 for that record,
>61 and 90 days assign a value of 60 for that record,
>91 and 120 days assign a value of 90 for that record,
>121 and over days assign a value of 120 for that record.
>
>This is how I started out but didn't get far:
>
>decode((round(sysdate - REQUEST_LOG_DATE)),(between 0 and 30),null,(between
>31 and 60),30,null)

I would tackle this as follows using something like:

DECODE ( CEIL ( ROUND ( SYSDATE - REQUEST_LOG_DATE ) / 30 ),

          1, NULL,
          2, 30,
          3, 60,
          4, 90,
          120    )

I can't be bothered to test this, as you might need to tweak the boundaries a bit with - or + 1 here and there, but it should probably work as is.

Kind regards,

Kind regards,

Saeed

sr_ng 786 Received on Fri Jan 02 2004 - 18:20:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US