Column or globale Variable not found

All we need is an easy explanation of the problem, so here it is.

Error msg:

DataSource.Error: ODBC: ERROR [42S22] [IBM][System i Access
ODBC-stuurprogramma][DB2 for i5/OS]SQL0206 – Kolom of globale
variabele AADETX niet gevonden. Details:
DataSourceKind=Odbc
DataSourcePath=dsn=I-Make
OdbcErrors=[Table]

Google translate of the error message: Column or global variable AADETX not found.

Request:

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'

The column AADETX the error reffers to does exist.
If i look it up in just the table i can find the specifiek data.

I’m trying to fix the duplicates errors i got from the request below:

First attempt:

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'

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

In this fragment of your code,

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

you have references like B.AADATX, B.AADATX, B.AADETX, which are invalid, because there is no table or alias called B defined in that context. The B name is assigned to the derived table itself but inside the derived table, the B reference is invalid.

To resolve the issue, either assign the B alias to 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

or remove B. from all column references inside the B derived table:

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

The same goes for the C derived table further in your code.

Additionally, both B and C rename the columns they are pulling from their respective underlying tables. Therefore, you must reference those columns using the names they are exposed as. Therefore this:

on B.AAAATX = A.G1AATX

should actually be this:

on B.Artikel = A.G1AATX

And this:

on C.ELAATX = A.G1AATX

should be rewritten like this:

on C.Artikel = A.G1AATX

(I was in the middle of adding these last two when nbk posted an answer pointing them out.)

Method 2

You subselect BN has no such column B.AADETX anymore it is called now B.Artikeltype.

If you use an alias in a subselect, this will be the column name you can address it with outside of the subselect

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'

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply