Re: Between function in Decode statement

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,

Saeed Rana

NMS Systems Ltd

Email: saeed.rana_at_nmsystemsltd.com

Tel: 01422 300 395
Fax: 01422 300 399
Mob: 07958 797 505