Re: SQL question

Hi James,

The interesting part was limiting it to one record per line item, especially if two part master records have the same lead time.

I set this up:

create table pdow/partmaster
(
      partNbr char(10)
    , leadTime integer
    , warehouse char(10)
);

insert into pdow/partmaster (partNbr, leadTime, warehouse) values ('PART1',7,'NY');
insert into pdow/partmaster (partNbr, leadTime, warehouse) values ('PART1',7,'CA');
insert into pdow/partmaster (partNbr, leadTime, warehouse) values ('PART3',11,'SC');

create table pdow/lineItem
(
      orderNbr char(10)
    , lineNbr integer
    , partNbr char(10)
    , orderQty integer
);

insert into pdow/lineitem (orderNbr, lineNbr, partNbr, orderQty) values ('order1', 1, 'PART1', 5);
insert into pdow/lineitem (orderNbr, lineNbr, partNbr, orderQty) values ('order2', 1, 'PART1', 4);
insert into pdow/lineitem (orderNbr, lineNbr, partNbr, orderQty) values ('order2', 2, 'PART3', 3);
insert into pdow/lineitem (orderNbr, lineNbr, partNbr, orderQty) values ('order3', 1, 'PART1', 6);


Then borrowed from Luis Rodriguez and came up with this:

With pm as
 (
 Select partNbr, min(warehouse) as min_warehouse, min(leadTime) as min_lead_time
   from partMaster
   group by partNbr
   )
 Select li.*, pm.*
   from lineItem li
   join pm
     on li.partNbr = pm.partNbr
  order by 1,2,3;

which resulted in

ORDERNBR
LINENBR
PARTNBR
ORDERQTY
PARTNBR
MIN_WAREHOUSE
MIN_LEAD_TIME
order1 1 PART1 5 PART1 CA 7
order2 1 PART1 4 PART1 CA 7
order2 2 PART3 3 PART3 SC 11
order3 1 PART1 6 PART1 CA 7


--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /