技术区 › 用友U8+ › 请U8 sql 高手帮忙看下语句错在哪里

janse_wang 发表于 2016-6-19 11:38:47

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

本帖最后由 janse_wang 于 2016-6-20 12:52 编辑

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 发表于 2016-6-19 11:41:24

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

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

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 发表于 2016-6-19 12:18:17

42000 - ')' 附近有语法错误。

janse_wang 发表于 2016-6-19 12:23:32

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


问题依旧 42000 - '母件编码' 附近有语法错误。

jojo_bj 发表于 2016-6-19 13:31:15

你前面的变量@STRCN做下处理，然后查询字段AS也改下，最后参照效果如下
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
) T
PIVOT(MAX([基本用量])
FOR [母件编码] IN (,) ) W

janse_wang 发表于 2016-6-19 14:54:11

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

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

q小白菜 发表于 2016-6-19 20:45:26

厉害厉害，学习了。

37724861 发表于 2016-6-20 09:06:00

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

用友软件（15209 发表于 2016-6-21 10:19:51

学习了

zhopeful 发表于 2023-10-25 15:08:36

这个是查询bom的么？

gamekid 发表于 2023-11-2 15:39:33

认真学习下

查看完整版本: 请U8 sql 高手帮忙看下语句错在哪里