请U8 sql 高手帮忙看下语句错在哪里

janse_wang

DECLARE @STRCN VARCHAR(8000)
SELECT @STRCN=ISNULL(@STRCN+',','')+ K.[母件编码] FROM
(select
a.BomId as 'BOMID',
c.InvCode as '母件编码',d.cInvName as '母件名称' ,
j.InvCode as '子件编码',h.cInvName as '子件名称',
d.cInvStd as '子件规格',e.cComUnitName as '计量单位',
SUM(F.BaseQtyD) as '基本用量' ,CompScrap as '子件损耗率',
EffBegDate as '子件生效日',EffEndDate as '子件失效日'
from bom_bom as a
join bom_parent as b on a.BomId=b.BomId
join bom_opcomponent as f on b.BomId=f.BomId
join bas_part as c on b.ParentId=c.PartId
join bas_part as j on f.componentId=j.PartId
join Inventory as d on c.InvCode=d.cInvCode
join Inventory as h on j.InvCode=h.cInvCode
join ComputationUnit as e ON h.cComUnitCode =e.cComUnitCode
GROUP BY
a.BomId,
c.InvCode,d.cInvName,
j.InvCode,h.cInvName,
d.cInvStd ,e.cComUnitName,CompScrap,
EffBegDate,EffEndDate) as k
GROUP BY K.[母件编码]
--PRINT(@STRCN)
DECLARE @SQLSTR VARCHAR(8000)
SET @SQLSTR='
SELECT * FROM
(
select
c.InvCode as '母件编码',
j.InvCode as '子件编码',
SUM(F.BaseQtyD) as '基本用量'
from bom_bom as a
join bom_parent as b on a.BomId=b.BomId
join bom_opcomponent as f on b.BomId=f.BomId
join bas_part as c on b.ParentId=c.PartId
join bas_part as j on f.componentId=j.PartId
join Inventory as d on c.InvCode=d.cInvCode
join Inventory as h on j.InvCode=h.cInvCode
join ComputationUnit as e ON h.cComUnitCode =e.cComUnitCode
GROUP BY
c.InvCode,
j.InvCode,
) AS T
PIVOT(MAX(T.[基本用量])
FOR T.[母件编码] IN ( '+@STRCN+' ) ) AS W'
--PRINT(@SQLSTR)
EXEC(@SQLSTR)

janse_wang

提示PIVOT(MAX(T.[基本用量])
FOR T.[母件编码] IN ( '+@STRCN+' ) ) AS W' 附近错误

jojo_bj

1. DECLARE @STRCN VARCHAR(8000)
   
2. SELECT @STRCN=ISNULL(@STRCN+',','')+ K.[母件编码] FROM
   
3. (select
   
4. a.BomId as 'BOMID',
   
5. c.InvCode as '母件编码',d.cInvName as '母件名称' ,
   
6. j.InvCode as '子件编码',h.cInvName as '子件名称',
   
7. d.cInvStd as '子件规格',e.cComUnitName as '计量单位',
   
8. SUM(F.BaseQtyD) as '基本用量' ,CompScrap as '子件损耗率',
   
9. EffBegDate as '子件生效日',EffEndDate as '子件失效日'
   
10. from bom_bom as a
    
11. join bom_parent as b on a.BomId=b.BomId
    
12. join bom_opcomponent as f on b.BomId=f.BomId
    
13. join bas_part as c on b.ParentId=c.PartId
    
14. join bas_part as j on f.componentId=j.PartId
    
15. join Inventory as d on c.InvCode=d.cInvCode
    
16. join Inventory as h on j.InvCode=h.cInvCode
    
17. join ComputationUnit as e ON h.cComUnitCode =e.cComUnitCode
    
18. GROUP BY
    
19. a.BomId,
    
20. c.InvCode,d.cInvName,
    
21. j.InvCode,h.cInvName,
    
22. d.cInvStd ,e.cComUnitName,CompScrap,
    
23. EffBegDate,EffEndDate) as k
    
24. GROUP BY K.[母件编码]
    
25. --PRINT(@STRCN)
    
26. DECLARE @SQLSTR VARCHAR(8000)
    
27. SET @SQLSTR='
    
28. SELECT * FROM
    
29. (
    
30. select
    
31. c.InvCode as ''母件编码'',
    
32. j.InvCode as ''子件编码'',
    
33. SUM(F.BaseQtyD) as ''基本用量''
    
34. from bom_bom as a
    
35. join bom_parent as b on a.BomId=b.BomId
    
36. join bom_opcomponent as f on b.BomId=f.BomId
    
37. join bas_part as c on b.ParentId=c.PartId
    
38. join bas_part as j on f.componentId=j.PartId
    
39. join Inventory as d on c.InvCode=d.cInvCode
    
40. join Inventory as h on j.InvCode=h.cInvCode
    
41. join ComputationUnit as e ON h.cComUnitCode =e.cComUnitCode
    
42. GROUP BY
    
43. c.InvCode,
    
44. j.InvCode,
    
45. ) AS T
    
46. PIVOT(MAX(T.[基本用量])
    
47. FOR T.[母件编码] IN ( '+@STRCN+' ) ) AS W'
    
48. --PRINT(@SQLSTR)
    
49. EXEC(@SQLSTR)

复制代码

janse_wang

[Err] 42000 - [SQL Server]')' 附近有语法错误。

janse_wang

jojo_bj 发表于 2016-6-19 11:54

问题依旧 [Err] 42000 - [SQL Server]'母件编码' 附近有语法错误。

jojo_bj

你前面的变量@STRCN做下处理，然后查询字段AS也改下，最后参照效果如下

1. SELECT * FROM
   
2. (select c.InvCode as [母件编码],j.InvCode as [子件编码],SUM(F.BaseQtyD) as [基本用量] from bom_bom as a
   
3. join bom_parent as b on a.BomId=b.BomId
   
4. join bom_opcomponent as f on b.BomId=f.BomId
   
5. join bas_part as c on b.ParentId=c.PartId
   
6. join bas_part as j on f.componentId=j.PartId
   
7. join Inventory as d on c.InvCode=d.cInvCode
   
8. join Inventory as h on j.InvCode=h.cInvCode
   
9. join ComputationUnit as e ON h.cComUnitCode =e.cComUnitCode
   
10. GROUP BY
    
11. c.InvCode,
    
12. j.InvCode
    
13. ) T
    
14. PIVOT(MAX([基本用量])
    
15. FOR [母件编码] IN ([0301],[0337]) ) W

复制代码

janse_wang

jojo_bj 发表于 2016-6-19 13:31
你前面的变量@STRCN做下处理，然后查询字段AS也改下，最后参照效果如下

感谢，已解决！！！！！！！！！！

q小白菜

厉害厉害，学习了。

37724861

兔神真是厉害的不要不要的

用友软件（15209

学习了

zhopeful

这个是查询bom的么？

gamekid

认真学习下