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