Search code examples
sqldb2db2-luw

nested join in Db2

Folks, what is wrong with this query i get error stating below in DB2 LUW.

[42703][-206] "d.sales" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.23.42 [56098][-727] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "d.sales".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.23.42

SELECT listagg(e.name,';'), e.address,d.sales
FROM emp e
JOIN (
    SELECT distinct sales, org
    FROM sales s
    JOIN address a ON S.ID = a.id
) d ON d.salesid = e.salesid
Solution

  • Look at your query:

    SELECT listagg(e.name,';'), e.address,d.sales
FROM emp e
JOIN (SELECT distinct sales, org  -- you need to add salesid to your columns set
         FROM sales s
         JOIN address a ON S.ID = a.id
) d ON d.salesid = e.salesid

    You forgot to select salesid in your subquery d

    You need to add either s.salesid or a.salesid