nested join in Db2

Question

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

d does not contain a column named salesid so this cannot work : ON d.salesid = e.salesid — GMB
You would need to show us sample data and expected output so we can try to help with the query. As it is, we are just guessing. — GMB
It's obviously that s.salesid or a.salesid is forgotten in subquery — Alex Yu
@AlexYu : adding an additional field to the subquery might affect the results of the outer query, since DISTINCT is used... — GMB

Alex Yu Alex Yu · Accepted Answer · 2019-02-07T23:18:35

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

nested join in Db2

2 Answers