未找到列或全域變數

錯誤資訊：

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發布一個​​答案指出它們時，我正在添加最後兩個。）

引用自：https://dba.stackexchange.com/questions/298313