Errors

未找到列或全域變數

  • August 20, 2021

錯誤資訊:

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.AADATXB.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