Re: Can DECODE do this?

From: Danny Roosens <roosens>
Date: 1996/08/26
Message-ID: <4vst0u$40h6_at_news-s01.ny.us.ibm.net>#1/1


John L Dunn <misioror_at_gas.uug.arizona.edu> wrote:
>Greetings:
>
>I'm hoping someone can share their knowledge of DECODE with me. I've used
>it for fairly simple purposes and it has worked well, but the current
>problem has be stumped.
>
>Here's what I'd like to do...
>
Hi,

Tou say you have the answer in a procedural form:

so use the procedural capacities of ORACLE and write what you want in PL/SQL, it will be the easist way.

if you reaqlly want to work with deocde

try something like sign(date-begin)

you can decode on this being -1,0 or 1 representing <,= and > respectively.

>I have a view of patient enrollment information that has 2 date fields I
>want to use to calculate the number of days the patient was enrolled in a
>given plan. The enrollment table has 2 dates, enroll_date(the date the
>patient enrolled) and term_date(the date the patient left the plan). I
>want to supply 2 more date fields, which are the beginning(begin_date) and
>ending(end_date) dates of the reporting period, and calculate how many
>member days the plan had for the reporting period.
>
>In procedural code, I'd write something like
> if enroll_date<begin_date and term_date>end_date
> then total_days=end_date - start_date
>
> else if enroll_date < begin_date and term_date < end_date
> then total_days=term_date - start_date
>
> else if enroll_date > begin_date and term_date > end_date
> then total_days=end_date - enroll_date
>
> else if enroll_date > begin_date and term_date < end_date
> then total_days=term_date - enroll_date
>
>Now, how can I do something like this using DECODE? Any ideas
>appreciated. I've looked at several examples but can't find any where the
>ifs contain an "AND" clause.
>
>Thanks for your help!
>
>John Dunn
>The University Physicians
Received on Mon Aug 26 1996 - 00:00:00 CEST

Original text of this message