galaxy0815
Technical User
Hello,
I searched already through the web and the forum but could not find a solution for my issue. The issue is I have a query with several left and inner joins and I want to limit one of the joins with a subselect. But obviously this is not working. Here is the pseydocode of my query:
Error message im am getting is: SQL0338N An on clause associated with a join operator or in a merge statement is not valid.
But how can I limit one of my left joins with a bit more complex condition (as the subselect really seems to fail.)
Thanks in advance for any help!
I searched already through the web and the forum but could not find a solution for my issue. The issue is I have a query with several left and inner joins and I want to limit one of the joins with a subselect. But obviously this is not working. Here is the pseydocode of my query:
Code:
select * from FROM (((Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
and Table2.Field2='01' and Table2.Field3=1) LEFT JOIN Table3 ON Table1.Field4 = Table3.Field4
and Table3.Field5<=CURRENT DATE AND Table3.Field6>CURRENT DATE and
Table3.Field7 = (select max(Field7) from Table4 where Table4.Field4 = Table1.Field4 AND
Table4.Field5<=CURRENT DATE AND Table4.Field6>CURRENT DATE and Table4.Field7 in ('01','02','35')))
LEFT JOIN Table5 ON Table3.Field8= Table5.Field1 and Table5.Field2='PE')
Error message im am getting is: SQL0338N An on clause associated with a join operator or in a merge statement is not valid.
But how can I limit one of my left joins with a bit more complex condition (as the subselect really seems to fail.)
Thanks in advance for any help!