??????????????????

????IF EXISTS(SELECT 1 FROM#FinalLotName)
????BEGIN
????IF EXISTS(SELECT 1 FROM#FinalCO_SN)
????BEGIN--3.1
????SELECT a.matl_def_id??b.Descript??a.MESOrderID AS pom_order_id??a.SN AS LotName??a.SourceSN AS ComLot??
????a.ComMaterials??c.Descript AS ComMatDes??a.VendorID??a.DateCode??a.SNNote??
????OnPlantID??SNCust FROM#FinalCO_SN a
????JOIN MMDefinitions b WITH(NOLOCK)ON a.matl_def_id=b.DefID
????JOIN MMDefinitions c WITH(NOLOCK)ON a.ComMaterials=c.DefID
????WHERE NOT EXISTS(select distinct SN??SourceSN from#FinalCO_SN x
????where x.SN=a.SourceSN and x.SourceSN=a.SN)
????END
????ELSE
????BEGIN--3.2
????--3.2.1???SN???????
????SELECT SN??MaterialID??MESOrderID??OnPlantID INTO#FinalSNX1 FROM CO_SN_GENERATION a WITH(NOLOCK)
????WHERE SNType='IntSN'AND SNRuleName='ProductSNRule'AND OnPlantID=@OnPlant
????AND EXISTS(SELECT 1 FROM#FinalLotName b WHERE a.SN=b.LotName)
????--3.2.2???SourceSN???????
????SELECT a.SN AS SourceLotName??a.VendorID??a.DateCode??a.SNNote??a.MaterialID AS ComMaterials INTO#FinalSNX2 FROM CO_SN_GENERATION a WITH(NOLOCK)WHERE EXISTS(SELECT 1 FROM#FinalLotName b WHERE a.SN=b.SourceLotName)
????SELECT b.MaterialID AS matl_def_id??x.Descript??b.MESOrderID AS pom_order_id??b.SN AS LotName??c.SourceLotName AS ComLot??c.ComMaterials??y.Descript AS ComMatDes??c.VendorID??c.DateCode??c.SNNote??b.OnPlantID??a.SNCust
????FROM#FinalLotName a
????LEFT JOIN#FinalSNX1 b ON a.LotName=b.SN
????LEFT JOIN#FinalSNX2 c ON a.SourceLotName=c.SourceLotName
????JOIN MMDefinitions x WITH(NOLOCK)ON b.MaterialID=x.DefID
????JOIN MMDefinitions y WITH(NOLOCK)ON c.ComMaterials=y.DefID
????WHERE NOT EXISTS(
????SELECT DISTINCT*FROM#FinalLotName z
????WHERE z.LotName=a.SourceLotName and z.SourceLotName=a.LotName
????)
????END
????END
????ELSE
????BEGIN
????IF EXISTS(SELECT 1 FROM#FinalCO_SN)
????BEGIN--3.3
????SELECT a.matl_def_id??b.Descript??a.MESOrderID AS pom_order_id??a.SN AS LotName??a.SourceSN AS ComLot??
????a.ComMaterials??c.Descript AS ComMatDes??a.VendorID??a.DateCode??a.SNNote??
????OnPlantID??SNCust FROM#FinalCO_SN a
????JOIN MMDefinitions b WITH(NOLOCK)ON a.matl_def_id=b.DefID
????JOIN MMDefinitions c WITH(NOLOCK)ON a.ComMaterials=c.DefID
????WHERE NOT EXISTS(select distinct SN??SourceSN from#FinalCO_SN x
????where x.SN=a.SourceSN and x.SourceSN=a.SN)
????END
????ELSE
????BEGIN--3.4
????PRINT'There is no queryable condition??please enter at less a query conditon.'
????END
????END
????END
????GO
??????????????????????????????????????????????????????λ???????
????????SSRS?????滻???洢?????SQL????????????????B/S??????1~2??
??????.???
?????????????????????????????????д???????????SQL??????д??????????SQL???????????????
???????????????????????????????????&???SQL???д??&?????????????????????п??????????θ??￡?????????????????????????with(nolock)??????????????????????