Errors
未找到列或全域變數
錯誤資訊:
DataSource.Error:ODBC:錯誤
$$ 42S22 $$ $$ IBM $$$$ System i Access ODBC-stuurprogramma $$$$ DB2 for i5/OS $$SQL0206 - 未找到列或全域變數 AADETX。詳細資訊:DataSourceKind=Odbc DataSourcePath=dsn=I-Make OdbcErrors=$$ Table $$
Google翻譯錯誤消息:未找到列或全域變數 AADETX。
要求:
SELECT A.G1AATX AS Artikel, A.GIG4NU ATP_aantal_cum, A.G1HADT BEGIN_periode FROM MIFA.VIS#MOGEM.V1G1REP A LEFT JOIN ( SELECT B.AAAATX AS Artikel, B.AADATX Artikelgroep, B.AADETX Artikeltype FROM MIFA.VIS#MOGEM.VIAAREP WHERE B.AADATX <> 'SMEE' AND B.AADATX <> 'LIJM' AND B.AADATX <> 'UMEC' AND B.AADETX ='I' GROUP BY AAAATX ) B on B.AAAATX = A.G1AATX LEFT JOIN ( SELECT C.ELAATX AS Artikel, C.ELCXST Status FROM MIFA.VIS#MOGEM.VOELREP WHERE C.ELCXST IN ('A',' ') GROUP BY ELAATX ) C on C.ELAATX = A.G1AATX WHERE A.G1AATX = '4022-480-9858'
錯誤所指的 AADETX 列確實存在。如果我只在表格中查找它,我可以找到特定的數據。
我正在嘗試修復從以下請求中得到的重複錯誤:
第一次嘗試:
SELECT A.G1AATX As Artikel, A.G1G4NU ATP_aantal_cum, A.G1HADT Begindatum_periode, B.AADETX I FROM MIFA.VIS#MOGEM.V1G1REP A LEFT OUTER JOIN MIFA.VIS#MOGEM.VIAAREP B on B.AAAATX=A.G1AATX LEFT OUTER JOIN MIFA.VIS#MOGEM.VOELREP C on C.ELAATX=A.G1AATX WHERE B.AADATX <> 'SMEE' AND B.AADATX <> 'LIJM' AND B.AADATX <> 'UMEC' AND C.ELCXST IN ('A',' ') AND B.AADETX ='I' AND A.G1AATX = '4022-480-9858'
在這段程式碼中,
LEFT JOIN ( SELECT B.AAAATX AS Artikel, B.AADATX Artikelgroep, B.AADETX Artikeltype FROM MIFA.VIS#MOGEM.VIAAREP WHERE B.AADATX <> 'SMEE' AND B.AADATX <> 'LIJM' AND B.AADATX <> 'UMEC' AND B.AADETX ='I' GROUP BY AAAATX ) B on B.AAAATX = A.G1AATX
您有 , , 之類的引用
B.AADATX
,B.AADATX
它們B.AADETX
是無效的,因為B
在該上下文中沒有定義表或別名。該B
名稱已分配給派生表本身,但在派生表內部,B
引用無效。要解決此問題,請將
B
別名分配給MIFA.VIS#MOGEM.VIAAREP
:LEFT JOIN ( SELECT B.AAAATX AS Artikel, B.AADATX Artikelgroep, B.AADETX Artikeltype FROM MIFA.VIS#MOGEM.VIAAREP B -- << here WHERE B.AADATX <> 'SMEE' AND B.AADATX <> 'LIJM' AND B.AADATX <> 'UMEC' AND B.AADETX ='I' GROUP BY AAAATX ) B on B.AAAATX = A.G1AATX
或從派生表中
B.
的所有列引用中刪除:B
LEFT JOIN ( SELECT AAAATX AS Artikel, AADATX Artikelgroep, AADETX Artikeltype FROM MIFA.VIS#MOGEM.VIAAREP WHERE AADATX <> 'SMEE' AND AADATX <> 'LIJM' AND AADATX <> 'UMEC' AND AADETX ='I' GROUP BY AAAATX ) B on B.AAAATX = A.G1AATX
程式碼中的
C
派生表也是如此。此外,兩者
B
並重C
命名它們從各自的基礎表中提取的列。因此,您必須使用它們公開的名稱來引用這些列。因此:on B.AAAATX = A.G1AATX
實際上應該是這樣的:
on B.Artikel = A.G1AATX
還有這個:
on C.ELAATX = A.G1AATX
應該這樣重寫:
on C.Artikel = A.G1AATX
(當nbk發布一個答案指出它們時,我正在添加最後兩個。)