Re: PL/SQL LOOP where statement not matching

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

(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. Received on Sat May 03 2003 - 03:26:45 CDT