请 u8 SQL 高手帮忙看下错在哪里，感谢~~~~

janse_wang

with wo_order as
(select DISTINCT
mom_orderdetail.socode,--销售订单号
mom_order.mocode,--生产订单号
mom_motype.Description,--生产订单类型
mom_orderdetail.invcode,--母件编号
inventory.cinvname,--母件名称
inventory.cinvstd,--规格型号
mom_orderdetail.qty,--生产订单数量
CONVERT(VARCHAR(10),mom_morder.startdate,120) as [input_date],--投入时间
CONVERT(VARCHAR(10),mom_morder.Duedate,120) as [output_date],--预计完成时间
recordinsq.iquantity,--入库数量
CONVERT(VARCHAR(10),recordinq.dnverifytime,120) as [actully_date],--成品审核时间
recordinq.cwhname--入库仓位
from
(
mom_orderdetail
left join mom_order on mom_order.moid=mom_orderdetail.moid
left join mom_morder on mom_morder.moid=mom_order.moid
left join mom_motype on mom_motype.MoTypeId=mom_orderdetail.MoTypeId
left JOIN INVENTORY ON INVENTORY.CINVCODE=mom_orderdetail.InvCode
LEFT JOIN recordinsq ON recordinsq.impoids = mom_orderdetail.MoDId
LEFT JOIN recordinq ON recordinsq.ID = recordinq.ID
)
WHERE mom_orderdetail.Status NOT IN ('4')
),
wo_order_list as
(
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.qty,'投入时间' as [status_r],wo_order.input_date as [date_r] from wo_order where wo_order.Description is not NULL
UNION all
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.qty,'完成时间' as [status_r],wo_order.output_date as [date_r]from wo_order where wo_order.Description is not NULL
union ALL
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.iquantity,'实际完成时间' as [status_r],wo_order.actully_date from wo_order where wo_order.Description is not NULL
)
DECLARE @sql varchar(8000)
select @sql=isnull(@sql+',','') + ' sum(case wo_order_list.date_r when '''+wo_order_list.date_r+''' then wo_order_list.qty else 0 end)['+wo_order_list.date_r+']'
from (select DISTINCT wo_order_list.date_r from wo_order_list) as a
set @sql='select wo_order_list.socode,wo_order_list.mocode,wo_order_list.invcode,wo_order_list.cinvname,wo_order_list.description,wo_order_list.status_r'
+@sql+'from wo_order_list group by wo_order_list.socode,wo_order_list.mocode,wo_order_list.invcode,wo_order_list.cinvname,wo_order_list.description,wo_order_list.status_r
order by wo_order_list.mocode'
exec(@sql)

提示[Err] 42000 - [SQL Server]关键字 'DECLARE' 附近有语法错误。

janse_wang

这个已经解决


DECLARE @STRCN VARCHAR(8000)
with wo_order as
(select DISTINCT
mom_orderdetail.socode,--销售订单号
mom_order.mocode,--生产订单号
mom_motype.Description,--生产订单类型
mom_orderdetail.invcode,--母件编号
inventory.cinvname,--母件名称
inventory.cinvstd,--规格型号
mom_orderdetail.qty,--生产订单数量
CONVERT(VARCHAR(10),mom_morder.startdate,120) as [input_date],--投入时间
CONVERT(VARCHAR(10),mom_morder.Duedate,120) as [output_date],--预计完成时间
recordinsq.iquantity,--入库数量
CONVERT(VARCHAR(10),recordinq.dnverifytime,120) as [actully_date],--成品审核时间
recordinq.cwhname--入库仓位
from
(
mom_orderdetail
left join mom_order on mom_order.moid=mom_orderdetail.moid
left join mom_morder on mom_morder.moid=mom_order.moid
left join mom_motype on mom_motype.MoTypeId=mom_orderdetail.MoTypeId
left JOIN INVENTORY ON INVENTORY.CINVCODE=mom_orderdetail.InvCode
LEFT JOIN recordinsq ON recordinsq.impoids = mom_orderdetail.MoDId
LEFT JOIN recordinq ON recordinsq.ID = recordinq.ID
)
WHERE mom_orderdetail.Status NOT IN ('4')
),
wo_order_list as
(
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.qty,'投入时间' as [status_r],wo_order.input_date as [date_r] from wo_order where wo_order.Description is not NULL
UNION all
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.qty,'完成时间' as [status_r],wo_order.output_date as [date_r]from wo_order where wo_order.Description is not NULL
union ALL
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.iquantity,'实际完成时间' as [status_r],wo_order.actully_date from wo_order where wo_order.Description is not NULL
)

SELECT @STRCN=ISNULL(@STRCN+',','')+QUOTENAME(wo_order_list.date_r) FROM wo_order_list GROUP BY wo_order_list.date_r
print(@STRCN)

DECLARE @SQLSTR VARCHAR(8000)
SET @SQLSTR='
with wo_order as
(select DISTINCT
mom_orderdetail.socode,--销售订单号
mom_order.mocode,--生产订单号
mom_motype.Description,--生产订单类型
mom_orderdetail.invcode,--母件编号
inventory.cinvname,--母件名称
inventory.cinvstd,--规格型号
mom_orderdetail.qty,--生产订单数量
CONVERT(VARCHAR(10),mom_morder.startdate,120) as [input_date],--投入时间
CONVERT(VARCHAR(10),mom_morder.Duedate,120) as [output_date],--预计完成时间
recordinsq.iquantity,--入库数量
CONVERT(VARCHAR(10),recordinq.dnverifytime,120) as [actully_date],--成品审核时间
recordinq.cwhname--入库仓位
from
(
mom_orderdetail
left join mom_order on mom_order.moid=mom_orderdetail.moid
left join mom_morder on mom_morder.moid=mom_order.moid
left join mom_motype on mom_motype.MoTypeId=mom_orderdetail.MoTypeId
left JOIN INVENTORY ON INVENTORY.CINVCODE=mom_orderdetail.InvCode
LEFT JOIN recordinsq ON recordinsq.impoids = mom_orderdetail.MoDId
LEFT JOIN recordinq ON recordinsq.ID = recordinq.ID
)
WHERE mom_orderdetail.Status NOT IN (''4'')
),
wo_order_list as
(
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.qty,''投入时间'' as [status_r],wo_order.input_date as [date_r] from wo_order where wo_order.Description is not NULL
UNION all
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.qty,''完成时间'' as [status_r],wo_order.output_date as [date_r]from wo_order where wo_order.Description is not NULL
union ALL
select distinct wo_order.socode,wo_order.mocode,wo_order.invcode,wo_order.cinvname,wo_order.Description,wo_order.iquantity,''实际完成时间'' as [status_r],wo_order.actully_date from wo_order where wo_order.Description is not NULL
)
SELECT * FROM wo_order_list
PIVOT(sum(qty)
FOR date_r IN ( '+@STRCN+' )) as t'

EXEC(@SQLSTR)

这种方式也请教一下
提示：
[Err] 42000 - [SQL Server]')' 附近有语法错误。

jojo_bj

继续