Re: Help with doing recursion in oracle: how to have sql go thru list of values

From: John P. Higgins <jh33378_at_deere.com>
Date: Thu, 25 Jun 1998 19:16:21 -0500
Message-ID: <3592E854.66700BC6@deere.com>

How about this:

select tasknum

        , (lastcompdate + daysfrequency) duedate
        , decode(daysfrequency
                , 1, 'Daily'
                , 7, 'Weekly'
                , 'Other'

    from ______ where duedate between A and B union
select tasknum

        , (lastcompdate + (daysfrequency * 2) ) duedate
        , decode(daysfrequency
                , 1, 'Daily'
                , 7, 'Weekly'
                , 'Other'

    from ______ where duedate between A and B union
.
.
select tasknum

        , (lastcompdate + (daysfrequency * 6) ) duedate
        , decode(daysfrequency
                , 1, 'Daily'
                , 7, 'Weekly'
                , 'Other'

    from ______ where duedate between A and B ;

dan wrote:

> I have a table of tasks, each with a due date and a frequency(30 days, 60,
> 180, ...), so that once the task is done it calculates the next due date based
> on the frequency. Assuming all the tasks are done and completed on the due
> date, I want a table or report that lists which tasks will be due within a
> specified time frame, say 1 year. For example, if the frequency is 1 day and
> my time frame is one week, the task would be due 7 times within the time
> frame,
>
> the table would have :
>
> TASKNUM LASTCOMPDATE DAYSFREQUENCY
> ======== ==================== ============
> 2 01-MAR-1998 12:00:00 30
> A sample table or report would list:
>
> task due date freq
>
> 1 3/1/98 daily
> 3/2/98
> 3/3/98 ....
> 3/31/99
>
> 2 3/1/98 monthly
> 4/1/98...
> 2/1/99
> etc
>
> I need one SQL statement that will read each row on the table and produce
> results with multiple rows -- one row for each successive time the task would
> be due within the time frame.
>
> Is this possible with Oracle? Our DB2 guy told us he can do it, but he is on
> another platform(MVS/mainframe), while we have Oracle 7 on Unix.
>
> If not, what other choices are there. We have SQR/REPORTMATE which takes sql
> and produces reports, the needed sql would feed into it.
>
> please respond in group or to Danilo.c.ramirez_at_ucm.com take the nospam out
> :)
>
> Dan Ramriez
>
> ramdan_at_mailexcite.com
> Development Analyst
> Information Systems
> Commonwealth Edison
>
> The contents of this message express only the sender's opinion. This message does not necessarily reflect the policy or views of my employer, Commonwealth Edison. All responsibility for the statements made in this Usenet posting resides solely and completely with the sender.
Received on Thu Jun 25 1998 - 19:16:21 CDT