Re: SQL question

[Peter Dow <petercdow@xxxxxxxxx>]

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 /