- ----------------------------------
- --生成物料级次
- ----------------------------------
- CREATE TABLE #ItemLevel(
- FNumber1 Varchar(355),
- FName1 Varchar(355),
- FNumber2 Varchar(355),
- FName2 Varchar(355),
- FNumber3 Varchar(355),
- FName3 Varchar(355),
- FItemID int,
- FNumber Varchar(355))
- INSERT Into #ItemLevel
- SELECT
- CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END, '',
- CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1) END,
- '', CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1) END,
- '', FItemID,FNumber
- FROM t_Item
- WHERE FItemClassID=4 AND FDetail=1 --And exists (Select FItemID From #Happen Where #Happen.FItemID=t_Item.FItemID)
- UPDATE t0 Set t0.FName1=t1.FName,t0.FName2=t2.FName,t0.FName3=t3.FName
- FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber AND t1.FItemClassID=4 AND t1.FDetail=0
- left join t_Item t2 On t0.FNumber2=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0
- left join t_Item t3 On t0.FNumber3=t3.FNumber AND t3.FItemClassID=4 AND t3.FDetail=0
- --生成物料级次完成 select * from #ItemLevel DROP TABLE #ItemLevel
- --如果自已的物料级次多于上面代码的范围可自行扩展。
K3中 生产物料级次 charindex的应用
最新推荐文章于 2023-03-23 08:20:20 发布