SELECT (CASE WHEN MASTERJOB='''' THEN JOBNO ELSE MASTERJOB END) WO,JOBNO,CHILDJOB,LOC,PRODLOC,CODE,BDESC,DDATE,QTY,
(SELECT SUM(QTY) FROM V_LOCQTY WHERE V_LOCQTY.CODE=RESVT.CODE AND (LOC BETWEEN 'S01' AND 'S15')) STOCK,
(SELECT SUM(QTY) FROM HC_POT WHERE HC_POT.WO=(CASE WHEN RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)
AND HC_POT.CODE=RESVT.CODE) POTQTY,
(SELECT SUM(QTY) FROM HC_ANT WHERE HC_ANT.WO=(CASE WHEN RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)
AND HC_ANT.CODE=RESVT.CODE) ANTQTY,
(SELECT SUM(QTY) FROM HC_VRT WHERE HC_VRT.WO=(CASE WHEN RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)
AND HC_VRT.CODE=RESVT.CODE) VRTQTY,
(SELECT SUM(QTY) FROM PDT WHERE (CASE WHEN PDT.MASTERJOB='''' THEN PDT.JOBNO ELSE PDT.MASTERJOB END)=(CASE WHEN
RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END) AND PDT.CODE=RESVT.CODE) PDTQTY,
(SELECT SUM(QTY) FROM PCT WHERE (CASE WHEN PCT.MASTERJOB='''' THEN PCT.JOBNO ELSE PCT.MASTERJOB END)=(CASE WHEN
RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END) AND PCT.CODE=RESVT.CODE) PCTQTY,
(SELECT SUM(QTY) FROM MRT WHERE (CASE WHEN MRT.MASTERJOB='''' THEN MRT.JOBNO ELSE MRT.MASTERJOB END)=(CASE WHEN
RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END) AND MRT.CODE=RESVT.CODE) MRTQTY,
(SELECT SUM(QTY) FROM PRT WHERE PRT.JOBNO=RESVT.JOBNO AND PRT.CODE=RESVT.CODE) PRTQTY
FROM RESVT WHERE WO='090845' OR JOBNO='090845'
本来是用视图但是不行主要是因为查询的速度太慢了 。 我写成表连接怎么出错啊
(SELECT SUM(QTY) FROM V_LOCQTY WHERE V_LOCQTY.CODE=RESVT.CODE AND (LOC BETWEEN 'S01' AND 'S15')) STOCK,
(SELECT SUM(QTY) FROM HC_POT WHERE HC_POT.WO=(CASE WHEN RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)
AND HC_POT.CODE=RESVT.CODE) POTQTY,
(SELECT SUM(QTY) FROM HC_ANT WHERE HC_ANT.WO=(CASE WHEN RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)
AND HC_ANT.CODE=RESVT.CODE) ANTQTY,
(SELECT SUM(QTY) FROM HC_VRT WHERE HC_VRT.WO=(CASE WHEN RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END)
AND HC_VRT.CODE=RESVT.CODE) VRTQTY,
(SELECT SUM(QTY) FROM PDT WHERE (CASE WHEN PDT.MASTERJOB='''' THEN PDT.JOBNO ELSE PDT.MASTERJOB END)=(CASE WHEN
RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END) AND PDT.CODE=RESVT.CODE) PDTQTY,
(SELECT SUM(QTY) FROM PCT WHERE (CASE WHEN PCT.MASTERJOB='''' THEN PCT.JOBNO ELSE PCT.MASTERJOB END)=(CASE WHEN
RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END) AND PCT.CODE=RESVT.CODE) PCTQTY,
(SELECT SUM(QTY) FROM MRT WHERE (CASE WHEN MRT.MASTERJOB='''' THEN MRT.JOBNO ELSE MRT.MASTERJOB END)=(CASE WHEN
RESVT.MASTERJOB='''' THEN RESVT.JOBNO ELSE RESVT.MASTERJOB END) AND MRT.CODE=RESVT.CODE) MRTQTY,
(SELECT SUM(QTY) FROM PRT WHERE PRT.JOBNO=RESVT.JOBNO AND PRT.CODE=RESVT.CODE) PRTQTY
FROM RESVT WHERE WO='090845' OR JOBNO='090845'
本来是用视图但是不行主要是因为查询的速度太慢了 。 我写成表连接怎么出错啊
ELSE MASTERJOB
END ) WO ,
JOBNO ,
CHILDJOB ,
LOC ,
PRODLOC ,
CODE ,
BDESC ,
DDATE ,
QTY ,
( SELECT SUM(QTY)
FROM V_LOCQTY
WHERE V_LOCQTY.CODE = RESVT.CODE
AND ( LOC BETWEEN 'S01' AND 'S15' )
) STOCK ,
( SELECT SUM(QTY)
FROM HC_POT
WHERE HC_POT.WO = ( CASE WHEN RESVT.MASTERJOB = ''''
THEN RESVT.JOBNO
ELSE RESVT.MASTERJOB
END )
AND HC_POT.CODE = RESVT.CODE
) POTQTY ,
( SELECT SUM(QTY)
FROM HC_ANT
WHERE HC_ANT.WO = ( CASE WHEN RESVT.MASTERJOB = ''''
THEN RESVT.JOBNO
ELSE RESVT.MASTERJOB
END )
AND HC_ANT.CODE = RESVT.CODE
) ANTQTY ,
( SELECT SUM(QTY)
FROM HC_VRT
WHERE HC_VRT.WO = ( CASE WHEN RESVT.MASTERJOB = ''''
THEN RESVT.JOBNO
ELSE RESVT.MASTERJOB
END )
AND HC_VRT.CODE = RESVT.CODE
) VRTQTY ,
( SELECT SUM(QTY)
FROM PDT
WHERE ( CASE WHEN PDT.MASTERJOB = '''' THEN PDT.JOBNO
ELSE PDT.MASTERJOB
END ) = ( CASE WHEN RESVT.MASTERJOB = ''''
THEN RESVT.JOBNO
ELSE RESVT.MASTERJOB
END )
AND PDT.CODE = RESVT.CODE
) PDTQTY ,
( SELECT SUM(QTY)
FROM PCT
WHERE ( CASE WHEN PCT.MASTERJOB = '''' THEN PCT.JOBNO
ELSE PCT.MASTERJOB
END ) = ( CASE WHEN RESVT.MASTERJOB = ''''
THEN RESVT.JOBNO
ELSE RESVT.MASTERJOB
END )
AND PCT.CODE = RESVT.CODE
) PCTQTY ,
( SELECT SUM(QTY)
FROM MRT
WHERE ( CASE WHEN MRT.MASTERJOB = '''' THEN MRT.JOBNO
ELSE MRT.MASTERJOB
END ) = ( CASE WHEN RESVT.MASTERJOB = ''''
THEN RESVT.JOBNO
ELSE RESVT.MASTERJOB
END )
AND MRT.CODE = RESVT.CODE
) MRTQTY ,
( SELECT SUM(QTY)
FROM PRT
WHERE PRT.JOBNO = RESVT.JOBNO
AND PRT.CODE = RESVT.CODE
) PRTQTY
FROM RESVT
WHERE WO = '090845'
OR JOBNO = '090845'帮忙整了下格式先,慢慢看...
错误:消息 207,级别 16,状态 1,第 16 行
Invalid column name 'WO'.
(CASE WHEN MASTERJOB='''' THEN JOBNO ELSE MASTERJOB END) WO
WO是别名,最后Where不能使用:WHERE (CASE WHEN MASTERJOB='''' THEN JOBNO ELSE MASTERJOB END) = '090845'
OR JOBNO = '090845'
SQL:
SELECT top 100 *,(QTY-(ISNULL(MRTQTY,1)-ISNULL(PRTQTY,1))) L_LOCQTY,((ISNULL(ANTQTY,1)-ISNULL(VRTQTY,1))+ISNULL(PCTQTY,1)-QTY)
L_JOBQTY,(STOCK-(QTY-(ISNULL(MRTQTY,1)-ISNULL(PRTQTY,1)))) L_STKQTY FROM HC_RESVT WHERE WO='1009034' OR
JOBNO='1009034' ORDER BY LOC