so i was wondering if there's anyone who can help me sort something out.

i have an SQL query that should show the details of a person (a search feature) that has a subquery inside an Inner Join, and the subquery is a Select statement.

Currently there's no problem with the sql query, but when i test the query as db2 i have this error:

An ON clause associated with a JOIN operator or in a MERGE statement is not valid.. SQLCODE=-338, SQLSTATE=42972, DRIVER=4.11.69

So, I'm asking, are there some ways to rewrite the query without having to use a subquery within the join statement?

I'm using DB2 For Linux, UNIX and Windows and v9.7

enlighten me

Show exact statement(or all of FROM clause) and all text of error message which you received.
.

basically, here's the query:

SELECT DISTINCT p.id, p.lname, p.fname, e.no from table1 AS p
INNER JOIN table2 as e ON e.no=(SELECT no FROM table3 AS f
WHERE p.id=f.id AND f.isExit=0
AND f.pstatus <> 'cancelled'
AND f.status NOT IN ('deleted','hidden','inactive','void')
ORDER BY f.edate DESC FETCH FIRST 1 ROWS ONLY))

WHERE ...

so the problem is, the select statement in the Inner Join Statement

Join the subquery with a keyword LATERAL(TABLE can be specified in place of LATERAL.), like this...

thanks lots tonkuma you're a lifesaver