列或全局变量未找到

Question

错误信息：

DataSource.Error: ODBC: ERROR 42S22系统I访问ODBC-stuurprogrammaSQL0206 - Kolom of globale variabele AADETX niet gevonden.详细信息: DataSourceKind=Odbc DataSourcePath=dsn=I-Make OdbcErrors=表格

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错误reffers要存在。如果我只在表中查找它，我就可以找到专门的数据。

我正在试图修复从下面的请求中获得的重复错误：

第一次尝试：

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'

Answer 1

在这段代码中，

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发布了一个答复，指出了这两个问题。)

Answer 2

您可以选择BN，不再有这样的列B.AADETX，现在称为B.Artikeltype。

如果在子选择中使用别名，则这将是可以在子选择的外部寻址的列名。

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 B

    WHERE B.AADATX <> 'SMEE'
    AND B.AADATX <> 'LIJM'
    AND B.AADATX <> 'UMEC'
    AND B.AADETX ='I'

    GROUP BY AAAATX
) B on B.Artikeltype = A.G1AATX

LEFT JOIN (
    SELECT 
        C.ELAATX AS Artikel,
        C.ELCXST Status
    FROM MIFA.VIS#MOGEM.VOELREP C

    WHERE C.ELCXST IN ('A',' ') 
    GROUP BY ELAATX
) C on C.ELAATX = A.G1AATX
WHERE A.G1AATX = '4022-480-9858'