Re: PL/SQL LOOP where statement not matching

Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message news:<3EB35C7B.B0A80DCC_at_exxesolutions.com>...
> Richard Brust wrote:
>
> > I have a table of parts, dates, and quantity. Each part *should* be
> > in the table 13 times, with 13 different dates (weeks) and quantities.
> >
> > However, while some parts do have the 13 weeks, some have just a few,
> > not necessarily in any consecutive order.
> >
> > What I have done is try to write a PL/SQL loop to load the 13 dates
> > into a cursor, go through the table and pick out any rows that do
> > *not* have an entry with that date, and insert the part, (missing)
> > date, and quantity of '0'.
> >
> > Below is the statement, but the jist of my problem is the sql times
> > out because it cannot expand the tablespace - which, of course means
> > that my statement is in an endless loop somewhere, which is the real
> > issue. (There are a few additional commented out attempts to match
> > the part and date of the current cursor(s) in the loop, but still no
> > luck.)
> >
> > I guess my question is why are the 'where' statements failing. I have
> > also tried NOT IN and NOT EXISTS statements on the part/date match,
> > but, again, no good.
> >
> > (I don't even know if I need the part_list cursor.)
> >
> > Thanks for any help!
> >
> > DECLARE
> > CURSOR dates is
> > SELECT unique (t_date - to_char(t_date, 'D')+2)
> > FROM t_1
> > ORDER BY 1; -- [returns 13 dates]
... pl/sql removed
> > CLOSE dates;
> > END LOOP;
> > commit;
> > CLOSE part_list;
> > END;
>
> It appear to me that you don't need the loops, don't need the cursors, and
> don't need any of this. And you defnitely don't need and shouldn't have
> DBMS_OUTPUT anywhere near this except for debugging purposes.
>
> The question I have is ... are the 13 dates static dates that can be
> loaded into a table or hard coded into the procedure ... or are they just
> any 13 random dates? It makes a difference.
>
> Please include Oracle version number and edition in your response.

Oracle 8.0.6, and the 13 dates are not static, but they certainly could be loaded into a temp table weekly. As you can tell by the initial CURSOR, what I am doing in grouping all the dates on weeks (Monday), and this thing will run weekly.

So, they could be loaded into a temp table, or, what I was trying to accomplish in the CURSOR was a pseudo 'hard-code array' of dates.

Thanks for you help!!! Received on Sat May 03 2003 - 09:38:12 CDT