SQL有錯，無法執行，請大家幫忙... |
|
尚未結案
|
|
cully
一般會員   發表：1 回覆：1 積分：0 註冊：2003-03-31 發送簡訊給我 |
以下這段SQL，執行後會有錯誤訊息，請問各位該如何修正，謝謝！！ 錯誤訊息是：ORA-01790: expression must have same datatype as corresponding expression 應該是資料型態的問題，但我實在看不出來錯在哪，請大家幫忙囉... SELECT 'SUM' AS TECH, AVG(SUM_E_QTY) AS "EQU_Q'TY", SUM("EQU.%") AS "S_EQU.%", SUM(MOVE) AS "MOVE", NULL AS STAGE,
SUM(OUT) AS "OUT", SUM(SCRAP) AS SCRAP, NULL AS "YIELD%"
FROM (
SELECT TECH, ROUND(E_QTY) AS "EQU_Q'TY", DECODE(SUM_E_QTY,0,0,ROUND((TO_NUMBER(E_QTY)/TO_NUMBER(SUM_E_QTY))*100,1)) AS "EQU.%",
TOTAL_MOVE AS MOVE, ROUND(AVG_STAGE,1) AS STAGE, OUT, SCRAP_QTY AS SCRAP, ROUND(YIELD,1) AS "YIELD%"
, SUM_E_QTY
FROM (
SELECT MOVE.TECH, MOVE.TOTAL_MOVE, STAGE.AVG_STAGE, OUT.OUT,
SCRAP.SCRAP_QTY,
DECODE((SCRAP.SCRAP_QTY OUT.OUT),0,0,(OUT.OUT)/(SCRAP.SCRAP_QTY OUT.OUT)*100) AS YIELD,
DECODE((STAGE.AVG_STAGE)*(OUT.OUT SCRAP.SCRAP_QTY),0,0,
((MOVE.TOTAL_MOVE*OUT.OUT)/((STAGE.AVG_STAGE)*(OUT.OUT SCRAP.SCRAP_QTY)))) AS E_QTY
FROM (
SELECT B.TECH, NVL(A.TOTAL_MOVE,0) AS TOTAL_MOVE
FROM (
SELECT PROCESS AS TECH,
SUM(MOVE) AS "TOTAL_MOVE"
FROM DM_TBL_MOVE_HIST
WHERE HISTTYPE = 'D'
AND HISTDATE >= TRUNC(SYSDATE-1,'MM')
AND HISTDATE < TRUNC(SYSDATE)
GROUP BY PROCESS
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
) MOVE,
(
SELECT B.TECH, NVL(A.AVG_STAGE,0) AS AVG_STAGE
FROM (
SELECT P.PROCESS AS TECH,
SUM(W.QTY*P.STAGENO)/SUM(W.QTY) AS AVG_STAGE
FROM RPT_TBL_WAFEROUTDTL W, DM_TBL_INFO_PART P
WHERE W.PARTID = P.PARTID
AND W.OUTTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND W.OUTTIME <= TRUNC(SYSDATE-7/24) 7/24
GROUP BY P.PROCESS
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
) STAGE,
(
SELECT B.TECH, NVL(A.OUT,0) AS OUT
FROM (
SELECT SUBSTR(PROCESS,1,3) AS TECH,
SUM(QTY) AS OUT
FROM RPT_TBL_WAFEROUTDTL W, DM_TBL_INFO_PART P
WHERE W.PARTID = P.PARTID
AND W.OUTTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND W.OUTTIME <= TRUNC(SYSDATE-7/24) 7/24
GROUP BY SUBSTR(PROCESS,1,3)
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
)OUT,
(
SELECT B.TECH, NVL(A.SCRAP_QTY,0) AS SCRAP_QTY
FROM (
SELECT P.PROCESS AS TECH,
SUM(S.WAFERQTY) AS "SCRAP_QTY"
FROM MES_TBL_ACTL A, DM_TBL_WIP_SCRAP S, DM_TBL_INFO_PART P
WHERE S.RECTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND S.RECTIME < TRUNC(SYSDATE-7/24) 7/24
AND A.LOTID = S.LOTID
AND A.PARTID = P.PARTID
AND S.RECTYPE='S'
AND (A.LOTTYPE ='PP' OR A.LOTTYPE = 'RP' OR A.LOTTYPE = 'PR' OR A.LOTTYPE = 'ED')
GROUP BY P.PROCESS
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
)SCRAP
WHERE MOVE.TECH = STAGE.TECH( )
AND MOVE.TECH = OUT.TECH
AND MOVE.TECH = SCRAP.TECH
)AA,
(
SELECT SUM(E_QTY) AS SUM_E_QTY
FROM (
SELECT MOVE.TECH, MOVE.TOTAL_MOVE, STAGE.AVG_STAGE, OUT.OUT,
SCRAP.SCRAP_QTY,
DECODE((SCRAP.SCRAP_QTY OUT.OUT),0,0,(OUT.OUT)/(SCRAP.SCRAP_QTY OUT.OUT)*100) AS YIELD,
DECODE((STAGE.AVG_STAGE)*(OUT.OUT SCRAP.SCRAP_QTY),0,0,
((MOVE.TOTAL_MOVE*OUT.OUT)/((STAGE.AVG_STAGE)*(OUT.OUT SCRAP.SCRAP_QTY)))) AS E_QTY
FROM (
SELECT B.TECH, NVL(A.TOTAL_MOVE,0) AS TOTAL_MOVE
FROM (
SELECT PROCESS AS TECH,
SUM(MOVE) AS "TOTAL_MOVE"
FROM DM_TBL_MOVE_HIST
WHERE HISTTYPE = 'D'
AND HISTDATE >= TRUNC(SYSDATE-1,'MM')
AND HISTDATE < TRUNC(SYSDATE)
GROUP BY PROCESS
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
) MOVE,
(
SELECT B.TECH, NVL(A.AVG_STAGE,0) AS AVG_STAGE
FROM (
SELECT P.PROCESS AS TECH,
SUM(W.QTY*P.STAGENO)/SUM(W.QTY) AS AVG_STAGE
FROM RPT_TBL_WAFEROUTDTL W, DM_TBL_INFO_PART P
WHERE W.PARTID = P.PARTID
AND W.OUTTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND W.OUTTIME <= TRUNC(SYSDATE-7/24) 7/24
GROUP BY P.PROCESS
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
) STAGE,
(
SELECT B.TECH, NVL(A.OUT,0) AS OUT
FROM (
SELECT SUBSTR(PROCESS,1,3) AS TECH,
SUM(QTY) AS OUT
FROM RPT_TBL_WAFEROUTDTL W, DM_TBL_INFO_PART P
WHERE W.PARTID = P.PARTID
AND W.OUTTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND W.OUTTIME <= TRUNC(SYSDATE-7/24) 7/24
GROUP BY SUBSTR(PROCESS,1,3)
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
)OUT,
(
SELECT B.TECH, NVL(A.SCRAP_QTY,0) AS SCRAP_QTY
FROM (
SELECT P.PROCESS AS TECH,
SUM(S.WAFERQTY) AS "SCRAP_QTY"
FROM MES_TBL_ACTL A, DM_TBL_WIP_SCRAP S, DM_TBL_INFO_PART P
WHERE S.RECTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND S.RECTIME < TRUNC(SYSDATE-7/24) 7/24
AND A.LOTID = S.LOTID
AND A.PARTID = P.PARTID
AND S.RECTYPE='S'
AND (A.LOTTYPE ='PP' OR A.LOTTYPE = 'RP' OR A.LOTTYPE = 'PR' OR A.LOTTYPE = 'ED')
GROUP BY P.PROCESS
)A,
(
SELECT DISTINCT(PROCESS) AS TECH
FROM DM_TBL_INFO_PART
WHERE PROCESS IN ('014','015','018','022','025','030','035')
)B
WHERE B.TECH = A.TECH ( )
)SCRAP
WHERE MOVE.TECH = STAGE.TECH( )
AND MOVE.TECH = OUT.TECH
AND MOVE.TECH = SCRAP.TECH
)
)BB
)
UNION
-- AVG
SELECT A.TECH AS TECH, NULL AS "EQU_Q'TY", NULL AS "EQU.%", A.AVG_MOVE AS MOVE,
B.AVG_STAGE AS STAGE, C.AVG_OUT AS OUT, D.AVG_SCRAP AS SCRAP,
DECODE(C.AVG_OUT D.AVG_SCRAP,0,0,(C.AVG_OUT/(C.AVG_OUT D.AVG_SCRAP))*100) AS "YIELD%"
FROM (
SELECT 'AVG' AS TECH,
DECODE((TRUNC(SYSDATE) - TRUNC(SYSDATE-1,'MM')),0,0,
SUM(MOVE)/(TRUNC(SYSDATE) - TRUNC(SYSDATE-1,'MM'))) AS "AVG_MOVE"
FROM DM_TBL_MOVE_HIST
WHERE HISTTYPE = 'D'
AND HISTDATE >= TRUNC(SYSDATE-1,'MM')
AND HISTDATE < TRUNC(SYSDATE)
)A,
(
SELECT 'AVG' AS TECH, SUM(W.QTY*P.STAGENO)/SUM(W.QTY) AS AVG_STAGE
FROM RPT_TBL_WAFEROUTDTL W, DM_TBL_INFO_PART P
WHERE W.PARTID = P.PARTID
AND W.OUTTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND W.OUTTIME <= TRUNC(SYSDATE-7/24) 7/24
)B,
(
SELECT 'AVG' AS TECH,
DECODE((TRUNC(SYSDATE) - TRUNC(SYSDATE-1,'MM')),0,0,
SUM(QTY)/(TRUNC(SYSDATE) - TRUNC(SYSDATE-1,'MM'))) AS AVG_OUT
FROM RPT_TBL_WAFEROUTDTL W, DM_TBL_INFO_PART P
WHERE W.PARTID = P.PARTID
AND W.OUTTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND W.OUTTIME <= TRUNC(SYSDATE-7/24) 7/24
)C,
(
SELECT 'AVG' AS TECH,
DECODE((TRUNC(SYSDATE) - TRUNC(SYSDATE-1,'MM')),0,0,
SUM(S.WAFERQTY)/(TRUNC(SYSDATE) - TRUNC(SYSDATE-1,'MM'))) AS AVG_SCRAP
FROM MES_TBL_ACTL A, DM_TBL_WIP_SCRAP S, DM_TBL_INFO_PART P
WHERE S.RECTIME >= TRUNC(SYSDATE-1-7/24,'MM') 7/24
AND S.RECTIME < TRUNC(SYSDATE-7/24) 7/24
AND A.LOTID = S.LOTID
AND A.PARTID = P.PARTID
AND S.RECTYPE='S'
AND (A.LOTTYPE ='PP' OR A.LOTTYPE = 'RP' OR A.LOTTYPE = 'PR' OR A.LOTTYPE = 'ED')
)D 發表人 - cully 於 2003/05/23 17:49:15
|
|
Mickey
版主  發表：77 回覆：1882 積分：1390 註冊：2002-12-11 發送簡訊給我 |
|
|
huangzanchan
一般會員 發表：1 回覆：6 積分：1 註冊：2002-12-02 發送簡訊給我 |
|
|
cully
一般會員 發表：1 回覆：1 積分：0 註冊：2003-03-31 發送簡訊給我 |
發表時間：2003-05-23 17:43:25
IP:203.66.xxx.xxx 未訂閱
系統時間：2024-05-06 0:17:54
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |