Re: PL/SQL LOOP where statement not matching

Richard Brust wrote:

> "Bosco Ng" <boscong_at_leccotech.com> wrote in message news:<3eb37c70$1_at_shknews01>...
> > Can it just be a SQL?
> >
> > Assume the table is
> >
> > create table parts
> > (id number,
> > ship_date date,
> > quantity number);
> >
> > And assume the 13 dates all exist in the parts table, so the following query
> > will try to insert a record for each id that does not have that particular
> > date, with quantity 0
> >
> > insert into parts
> > select id.id,
> > dates.ship_date,
> > 0
> > from
> > (select distinct id from parts) id,
> > (select distinct ship_date from parts) dates
> > where not exists (select 'X'
> > from parts b
> > where b.id = id.id
> > and b.ship_date = dates.ship_date);
> >
> > If that 13 dates are hardcoded or been stored in just another table, then
> > just replace the dates inline view in the from clause with that table will
> > do.
>
> I think between you message, and the prev from Daniel, I know what you
> are getting at. Basically, all the other ps/sql is not necessary, as
> all the data is already there.
>
> I will try your example - thanks !!!

You've got it.

It looks to me like a simple outer join between the table with the dates and your result set solves the problem. That way you get all of the dates and data where it corresponds. Alternatively you could use a UNION and have one part for the matches and one part for those that don't.

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