Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Between function in Decode statement
"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) Received on Wed Dec 31 2003 - 09:51:01 CST