Re: Can DECODE do this?

John L Dunn <misioror_at_gas.uug.arizona.edu> wrote:

>>Greetings:

Hi John

there had been a lot of answers, but I missed the most important hint. Use your pl/sql-code inside of a USER-DEFINED-Stored Function/Package-Function which SQL-Capabilities.

restrictions:
The function must have purity-levels.
WNDS (Write no data states): => The function may no change tables. WNPS (Write no package states): => The function may no change global package variables.

A function with purity-level WNDS can be used in value-lists (e.g. in Select-clause), set-lists, group-by-clause, order-by-clause

A function with purity-level WNDS and WNPS can be used in where-clause and having-clause too ...

the specification of package-functions, which are be supposed to be SQL-functions, must be followed by a
pragma restricted_reference (<function-name>, WNDS [,WNPS]);

SQL- functions must return scalar data-types, which could be understood by the DBMS.
e.g.
function xyz( p_in in number) return boolean will not work, because of the unknown (i.c.o. DBMS) datatype boolean.

cu gero
>>
>>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...
>>
 

>>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