各位大大:
不好意思,能否幫我看看以下的SQL語法.....我加了GROUP BY後,就出現錯誤訊息了......謝謝!
---
SELECT
CASE WHEN TE200='Y' THEN QRY2.CMTA006 ELSE TA006 END NTA006,
CASE WHEN TE200='Y' THEN QRY2.CMTA007 ELSE TA007 END NTA007,
CASE WHEN TE200='Y' THEN QRY2.INVAMB002 ELSE INV1.MB002 END NIMB002,
CASE WHEN TE200='Y' THEN QRY2.INVAMB003 ELSE INV1.MB003 END NIMB003,
TE004,INV2.MB002 AS INV2MB002,INV2.MB003 AS INV2MB003,INV2.MB004 AS INV2MB004 ,LA011,LA005,LA013,LA005,MA003,MF005,INV2.MB005 AS TYPE
FROM CONTREL53..MOCTE AS MOCTE
INNER JOIN CONTREL53..MOCTC AS MOCTC ON TE001=TC001 AND TE002=TC002
INNER JOIN CONTREL53..MOCTA AS MOCTA ON TE011=TA001 AND TE012=TA002
INNER JOIN CONTREL53..INVLA AS INVLA ON TE001=LA006 AND TE002=LA007 AND TE003=LA008
INNER JOIN CONTREL53..INVMB AS INV1 ON TA006=INV1.MB001
INNER JOIN CONTREL53..INVMB AS INV2 ON TE004=INV2.MB001
LEFT JOIN CONTREL53..CMSMF AS CMSMF ON MF001= 'NTD'
INNER JOIN CONTREL53..CMSMC AS CMSMC ON LA009=MC001 AND MC004="1"
LEFT JOIN CONTREL53..INVMA AS INVMA ON MA001='1' AND INV2.MB005=MA002
LEFT JOIN
(
SELECT YB001,YB002,YB003,YB004,YB008,YB009,YB010,YB011,YB012,
YC004,YC005,YC006,YC007,YC008,YC009,YC010,YC013,CM.TA001,CM.TA002,CM.TA006 AS CMTA006,CM.TA007 AS CMTA007,INVA.MB002 AS INVAMB002,INVA.MB003 AS INVAMB003
FROM CONTREL53..COPYB
INNER JOIN CONTREL53..COPYC ON YB001=YC001 AND YB002=YC002
INNER JOIN CONTREL53..MOCTA AS CM ON CM.TA001=YB010 AND CM.TA002=YB011
INNER JOIN CONTREL53..INVMB AS INVA ON CM.TA006=INVA.MB001
) AS QRY2 ON QRY2.YB008=TE011 AND QRY2.YB009=TE012 AND QRY2.YC004=TE001 AND QRY2.YC005=TE002 AND QRY2.YC006=TE003
WHERE ( (TC009='Y') AND (TE016='1') and (SUBSTRING(TC003,1,6) Between '200702' and '200707') and (TA006='RD301518-2000A'))
GROUP BY NTA006,NTA007,NIMB002,NIMB003,TE004,INV2.MB002,INV2.MB003,INV2.MB004 ,LA011,LA005,LA013,LA005,MA003,MF005,INV2.MB005
ORDER BY TA006,TYPE,TE004

---
ERROR CODE:
無效的資料行名稱NTA006
無效的資料行名稱NTA007
無效的資料行名稱NIMB002
無效的資料行名稱NIMB003

1.GROUP BY 搭配count(*).. 使用

2.請去掉ORDER By.

------
What do we live for if not to make life less difficult for each other?

不好意思...我試出來了....原本的GROUP BY段應該改成

GROUP BY
CASE WHEN TE200='Y' THEN QRY2.CMTA006 ELSE TA006 END ,
CASE WHEN TE200='Y' THEN QRY2.CMTA007 ELSE TA007 END ,
CASE WHEN TE200='Y' THEN QRY2.INVAMB002 ELSE INV1.MB002 END ,
CASE WHEN TE200='Y' THEN QRY2.INVAMB003 ELSE INV1.MB003 END ,
TE004,INV2.MB002 ,INV2.MB003,INV2.MB004 ,LA011,LA005,LA013,LA005,MA003,MF005,INV2.MB005

謝謝各位!
===================引 用 cswang1210 文 章===================
GROUP BY NTA006,NTA007,NIMB002,NIMB003,TE004,INV2.MB002,INV2.MB003,INV2.MB004 ,LA011,LA005,LA013,LA005,MA003,MF005,INV2.MB005
ORDER BY TA006,TYPE,TE004

---
ERROR CODE:
無效的資料行名稱NTA006
無效的資料行名稱NTA007
無效的資料行名稱NIMB002
無效的資料行名稱NIMB003