CREATE PROCEDURE SAP_CGZX_TZ5@QSRQ DATETIME,@ZZRQ DATETIME ASSELECT T0.ITEMCODE AS '物料编码',T0.ITEMNAME AS '物料名称',T0.INVNTRYUOM AS '单位',T1.WHSNAME AS '仓库名称',T2.ItmsGrpNam AS '物料组',SUM(TZ.QCINQTY)-SUM(TZ.QCOUTQTY) AS '期初库存',
CASE
WHEN SUM(TZ.QCINQTY)-SUM(TZ.QCOUTQTY)='0' THEN '0' WHEN SUM(TZ.QCINQTY)-SUM(TZ.QCOUTQTY)<>'0' THEN (SUM(TZ.QCINQTY*TZ.QCPRICEC)-SUM(TZ.QCOUTQTY*TZ.QCPRICEC))/(SUM(TZ.QCINQTY)-SUM(TZ.QCOUTQTY)) END AS '期初单价',CASE
WHEN SUM(TZ.QCINQTY)-SUM(TZ.QCOUTQTY)='0' THEN '0' WHEN SUM(TZ.QCINQTY)-SUM(TZ.QCOUTQTY)<>'0' THEN (SUM(TZ.QCINQTY*TZ.QCPRICEC)-SUM(TZ.QCOUTQTY*TZ.QCPRICEC)) END AS '期初金额',
TZ.QJINQTY AS '期间入库',TZ.QJOUTQTY AS '期间出库',CASE WHEN SUM(TZ.QJINQTY)-SUM(TZ.QJOUTQTY)='0' THEN '0' WHEN SUM(TZ.QJINQTY)-SUM(TZ.QJOUTQTY)<>'0' THEN (SUM(TZ.QJINQTY*TZ.QJPRICEC)-SUM(TZ.QJOUTQTY*TZ.QJPRICEC))/(SUM(TZ.QJINQTY)-SUM(TZ.QJOUTQTY)) END AS '期间单价',SUM(TZ.QMINQTY)-SUM(TZ.QMOUTQTY) AS '期末库存',CASE
WHEN SUM(TZ.QMINQTY)-SUM(TZ.QMOUTQTY)='0' THEN '0' WHEN SUM(TZ.QMINQTY)-SUM(TZ.QMOUTQTY)<>'0' THEN (SUM(TZ.QMINQTY*TZ.QMPRICEC)-SUM(TZ.QMOUTQTY*TZ.QMPRICEC))/(SUM(TZ.QMINQTY)-SUM(TZ.QMOUTQTY)) END AS '期末单价',CASE WHEN SUM(TZ.QMINQTY)-SUM(TZ.QMOUTQTY)='0' THEN '0' WHEN SUM(TZ.QMINQTY)-SUM(TZ.QMOUTQTY)<>'0' THEN (SUM(TZ.QMINQTY*TZ.QMPRICEC)-SUM(TZ.QMOUTQTY*TZ.QMPRICEC)) END AS '期末金额'
FROM(SELECT ITEMCODE AS WL,DfltWH AS CK,0 AS QCINQTY,0 AS QCOUTQTY,0 AS QCPRICEC,0 AS QJINQTY,0 AS QJOUTQTY,0 as cgth,0 as cgfpth,0 as xsth,0 as xsfpth,
0 AS QJPRICEC,0 AS QMINQTY,0 AS QMOUTQTY,0 AS QMPRICECFROM OITM
UNION ALLSELECT ITEMCODE AS WL,WAREHOUSE AS CK,INQTY AS QCINQTY,OUTQTY AS QCOUTQTY,PRICEC AS QCPRICEC,0 AS QJINQTY,0 AS QJOUTQTY,
0 AS QJPRICEC,0 AS QMINQTY,0 AS QMOUTQTY,0 AS QMPRICECFROM OINM
WHERE DOCDATE<@QSRQUNION ALLSELECT ITEMCODE AS WL,WAREHOUSE AS CK,0 AS QCINQTY,0 AS QCOUTQTY,0 AS QCPRICEC, sum(inqty-cgth-cgfpth-xsth-xsfpth) AS QJINQTY, (select sum(inqty) from oinm where transtype='16') as cgth,(select sum(inqty) from oinm where transtype='13') as cgfpth,(select sum(outqty) from oinm where transtype='21') as xsth,(select sum(inqty) from oinm where transtype='18') as xsfpth, sum(outqty-cgth-cgfpth-xsth-xsfpth) AS QJOUTQTY,
PRICEC AS QJPRICEC,0 AS QMINQTY,0 AS QMOUTQTY,0 AS QMPRICEC
FROM OINM
WHERE DOCDATE>=@QSRQ AND DOCDATE<=@ZZRQUNION ALLSELECT ITEMCODE AS WL,WAREHOUSE AS CK,0 AS QCINQTY,0 AS QCOUTQTY,0 AS QCPRICEC,0 AS QJINQTY,0 AS QJOUTQTY,0 as cgth,0 as cgfpth,0 as xsth,0 as xsfpth,
0 AS QJPRICEC,INQTY AS QMINQTY,OUTQTY AS QMOUTQTY,PRICEC AS QMPRICEC
FROM OINM
WHERE DOCDATE<=@ZZRQ
)
AS TZ INNER JOIN OITM AS T0 ON T0.ITEMCODE=TZ.WL INNER JOIN OWHS T1 ON T1.WHSCODE=TZ.CK INNER JOIN OITB T2 ON T2.ItmsGrpCod=T0.ItmsGrpCodWHERE TZ.CK LIKE 'Y-%'GROUP BY T0.ITEMCODE,T0.ITEMNAME,T0.INVNTRYUOM,T1.WHSNAME,ItmsGrpNamORDER BY T1.WHSNAME,ItmsGrpNam,T0.ITEMCODEGO
运行提示列名cgth,xsts,cgfpth,xsfpth 列名无效 应该怎么解决!
这几个后面的from字句的查询结果集或者是数据表视图等有没有这几列.
太长了,没有细看
cgth,xsts,cgfpth,xsfpth 列名无效就是没有列的意思,有什么难懂的?我看你这些列一会儿又在SUM函数中用,一会又是一个SUM的结果,没表不懂。
引用混乱而已帮你排一下版吧,有空的人慢慢看
CREATE PROCEDURE SAP_CGZX_TZ5
@QSRQ DATETIME,
@ZZRQ DATETIME
AS
SELECT T0.ITEMCODE AS '物料编码',
T0.ITEMNAME AS '物料名称',
T0.INVNTRYUOM AS '单位',
T1.WHSNAME AS '仓库名称',
T2.ItmsGrpNam AS '物料组',
SUM(TZ.QCINQTY) - SUM(TZ.QCOUTQTY) AS '期初库存',
CASE
WHEN SUM(TZ.QCINQTY) - SUM(TZ.QCOUTQTY) = '0' THEN '0'
WHEN SUM(TZ.QCINQTY) - SUM(TZ.QCOUTQTY) <> '0' THEN (SUM(TZ.QCINQTY * TZ.QCPRICEC) - SUM(TZ.QCOUTQTY * TZ.QCPRICEC)) / (SUM(TZ.QCINQTY) - SUM(TZ.QCOUTQTY))
END AS '期初单价',
CASE
WHEN SUM(TZ.QCINQTY) - SUM(TZ.QCOUTQTY) = '0' THEN '0'
WHEN SUM(TZ.QCINQTY) - SUM(TZ.QCOUTQTY) <> '0' THEN (SUM(TZ.QCINQTY * TZ.QCPRICEC) - SUM(TZ.QCOUTQTY * TZ.QCPRICEC))
END AS '期初金额',
TZ.QJINQTY AS '期间入库',
TZ.QJOUTQTY AS '期间出库',
CASE
WHEN SUM(TZ.QJINQTY) - SUM(TZ.QJOUTQTY) = '0' THEN '0'
WHEN SUM(TZ.QJINQTY) - SUM(TZ.QJOUTQTY) <> '0' THEN (SUM(TZ.QJINQTY * TZ.QJPRICEC) - SUM(TZ.QJOUTQTY * TZ.QJPRICEC)) / (SUM(TZ.QJINQTY) - SUM(TZ.QJOUTQTY))
END AS '期间单价',
SUM(TZ.QMINQTY) - SUM(TZ.QMOUTQTY) AS '期末库存',
CASE
WHEN SUM(TZ.QMINQTY) - SUM(TZ.QMOUTQTY) = '0' THEN '0'
WHEN SUM(TZ.QMINQTY) - SUM(TZ.QMOUTQTY) <> '0' THEN (SUM(TZ.QMINQTY * TZ.QMPRICEC) - SUM(TZ.QMOUTQTY * TZ.QMPRICEC)) / (SUM(TZ.QMINQTY) - SUM(TZ.QMOUTQTY))
END AS '期末单价',
CASE
WHEN SUM(TZ.QMINQTY) - SUM(TZ.QMOUTQTY) = '0' THEN '0'
WHEN SUM(TZ.QMINQTY) - SUM(TZ.QMOUTQTY) <> '0' THEN (SUM(TZ.QMINQTY * TZ.QMPRICEC) - SUM(TZ.QMOUTQTY * TZ.QMPRICEC))
END AS '期末金额'
FROM (SELECT ITEMCODE AS WL,
DfltWH AS CK,
0 AS QCINQTY,
0 AS QCOUTQTY,
0 AS QCPRICEC,
0 AS QJINQTY,
0 AS QJOUTQTY,
0 AS cgth,
0 AS cgfpth,
0 AS xsth,
0 AS xsfpth,
0 AS QJPRICEC,
0 AS QMINQTY,
0 AS QMOUTQTY,
0 AS QMPRICEC
FROM OITM
UNION ALL
SELECT ITEMCODE AS WL,
WAREHOUSE AS CK,
INQTY AS QCINQTY,
OUTQTY AS QCOUTQTY,
PRICEC AS QCPRICEC,
0 AS QJINQTY,
0 AS QJOUTQTY,
0 AS QJPRICEC,
0 AS QMINQTY,
0 AS QMOUTQTY,
0 AS QMPRICEC
FROM OINM
WHERE DOCDATE <@QSRQ
UNION ALL
SELECT ITEMCODE AS WL,
WAREHOUSE AS CK,
0 AS QCINQTY,
0 AS QCOUTQTY,
0 AS QCPRICEC,
SUM(inqty - cgth - cgfpth - xsth - xsfpth) AS QJINQTY,
(SELECT SUM(inqty)
FROM oinm
WHERE transtype = '16') AS cgth,
(SELECT SUM(inqty)
FROM oinm
WHERE transtype = '13') AS cgfpth,
(SELECT SUM(outqty)
FROM oinm
WHERE transtype = '21') AS xsth,
(SELECT SUM(inqty)
FROM oinm
WHERE transtype = '18') AS xsfpth,
SUM(outqty - cgth - cgfpth - xsth - xsfpth) AS QJOUTQTY,
PRICEC AS QJPRICEC,
0 AS QMINQTY,
0 AS QMOUTQTY,
0 AS QMPRICEC
FROM OINM
WHERE DOCDATE >=@QSRQ
AND DOCDATE <=@ZZRQ
UNION ALL
SELECT ITEMCODE AS WL,
WAREHOUSE AS CK,
0 AS QCINQTY,
0 AS QCOUTQTY,
0 AS QCPRICEC,
0 AS QJINQTY,
0 AS QJOUTQTY,
0 AS cgth,
0 AS cgfpth,
0 AS xsth,
0 AS xsfpth,
0 AS QJPRICEC,
INQTY AS QMINQTY,
OUTQTY AS QMOUTQTY,
PRICEC AS QMPRICEC
FROM OINM
WHERE DOCDATE <=@ZZRQ) AS TZ
INNER JOIN OITM AS T0
ON T0.ITEMCODE = TZ.WL
INNER JOIN OWHS T1
ON T1.WHSCODE = TZ.CK
INNER JOIN OITB T2
ON T2.ItmsGrpCod = T0.ItmsGrpCod
WHERE TZ.CK LIKE 'Y-%'
GROUP BY T0.ITEMCODE,T0.ITEMNAME,T0.INVNTRYUOM,T1.WHSNAME,ItmsGrpNam
ORDER BY T1.WHSNAME,
ItmsGrpNam,
T0.ITEMCODE
GO