On the first query, it should be possible to nest the lateral references like that. What version are you using? That may have been addressed by [TEIID-5272] Improve error message when using LATERAL reference in RIGHT or FULL joins - JBoss Issue Tracker as that also improved the resolving behavior. If not please open an issue: - JBoss Issue Tracker
On the second query, does Aantal_OD_Pax_Vertrek have a ROW_ID column? If not that message is expected as select clause is only visible to the order by, not to the other clauses.
Thanks for you reply. We're using Teiid Designer 11.1.1 and I see that the issue was solved in 10.0.
It is not AANTAL_OD_Pax_Vertrek but TabA and yes that table has a column called ROW_ID.
Any new thougths are highly appreciated.
Sorry, I see what is going on. Since you are not using a table function, you need to use an explicit lateral or table keyword to perform a lateral join:
... INNER JOIN
LATERAL (SELECT x.ROW_ID ...
Thanks! That solved the problem.