Re: Between function in Decode statement

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 31 Dec 2003 08:12:58 -0800
Message-ID: <1072887089.975019@yasure>

DFN wrote:

> "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> news:3ff2e738$0$28697$626a54ce_at_news.free.fr...
>

>>"DFN" <Iam_at_cyberspace.net> a écrit dans le message de
>>news:bsuo2i$1jghm$1_at_ID-157477.news.uni-berlin.de...
>>
>>>My apologies if this is not the right group for this question.
>>>
>>>I am trying to use a BETWEEN function in a DECODE statement and I am

>
> having
>

>>>a hard time.
>>>Does anyone know if it is not allowed?
>>>I have successfully used some of the other equality tests such as

>
> greater
>

>>>than, less than and equal to.
>>>
>>>
>>
>>If you have a 9i version, use CASE instead of DECODE.
>>If you have a pre-9i version and want to use between with numbers, have a

>
> look at SIGN.
>

>>Post your version and your entire issue and we can help you.
>>
>>Regards
>>Michel Cadot

>
>
> We have recently installed 9i and here is what I was trying to do:
>
> I want to create a view that brings in a column named REQUEST_LOG_DATE which
> a date/time stamp. I also need another column that will subtract the log
> date from the sysdate and assigning a value depending on the age of the
> request.
> 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)

Use CASE not DECODE.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Dec 31 2003 - 10:12:58 CST