Re: PL/SQL LOOP where statement not matching

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]
> CURSOR part_list is
> SELECT unique part
> FROM t_1; -- [currently 124 parts]
> curr_date DATE;
> curr_part varchar2(20);
> BEGIN
> DBMS_OUTPUT.enable(32000);
> OPEN part_list;
> LOOP
> FETCH part_list INTO curr_part;
> EXIT WHEN part_list%NOTFOUND;
> -- *** --
> OPEN dates;
> LOOP
> FETCH dates INTO curr_date;
> EXIT WHEN dates%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE(curr_part || ' => ' || curr_date);
> -- *** here's where I try to insert missing rows *** --
> insert into t_1 (part, t_date, qty)
> select curr_part, curr_date, '0'
> from t_1;
> -- where part = curr_part;
> -- where (t_date - to_char(t_date, 'D')+2) !=
> to_char(curr_date);
> -- where part = curr_part and
> -- (t_date - to_char(t_date, 'D')+2) !=
> to_char(curr_date);
> END LOOP;
> 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.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)