view_poinforec_mstr.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

delete from PO_Price_Mstr where item in
(select item from (select Min(Item) as Item,Vendor_ID,HPN,MOQ,MPQ  
from po_information_Record where isnull(hpn,'')<>''
group by  Vendor_ID,HPN,MOQ,MPQ
having count(HPN)>1) a)
go

delete from PO_information_Record where item in
(select item from (select Min(Item) as Item,Vendor_ID,HPN,MOQ,MPQ 
 from po_information_Record
where isnull(hpn,'')<>''
group by  Vendor_ID,HPN,MOQ,MPQ
having count(HPN)>1) a)

go
select Min(Item) as Item,Vendor_ID,HPN,MOQ,MPQ,Lead_Time  
  from po_information_Record where isnull(hpn,'')<>''
group by  Vendor_ID,HPN,MOQ,MPQ,Lead_Time
having count(HPN)>1


if object_id('dbo.Viw_POInfoRec_Mstr') is not null
drop view Viw_POInfoRec_Mstr
go

create view Viw_POInfoRec_Mstr
as
 select  Vendor_ID,HPN,MOQ,MPQ,Lead_Time  from PO_Information_Record
where isnull(hpn,'')<>'' and len(hpn)>5
group by Vendor_ID,HPN,MOQ,MPQ,Lead_Time 

go