SQL Multiple Joins + Subquery limitation

[galaxy0815]

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:

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!

 

[papadba]

Suggest you re-post the actuial query rather than pseudo-code and use proper indentation.

 

[galaxy0815]

Hi,

here is the query without pseudocode and I tried to format it manually a bit better.

Select Employeegroup.* 
FROM
(
 (
  ( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
    ON Employeegroup.ID = Employeedetail.ID 
    and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
  LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY
  and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE
  and Address.ADCD =
        (select max(ADCD) from SRS.Address as Address2 where Address2.PRKEY = Employeegroup.PRKEY
        AND Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE
        and Address2.ADCD in ('01','02','35')))
LEFT JOIN SRS.Lookuptable as Lookuptable_COUNTRY ON Address.ADDR_CNTRY = Lookuptable_COUNTRY.CODE
and Lookuptable_COUNTRY.GROUPCODE='PE')

The issue is as said the subquery with the select max()... thing, as since I entered this one, I get the below mentioned error.

Reason why I need to enter it is because an Employeegroup can have more that one valid address (a mailing address, a registered address, and addtional mailing address, etc) and I want to show the valid one with the highest ADCD (internal number field in the database) value.

Thanks for any help,
Martin

 

[MarcLodge]

Hi Martin,
I could be wrong as I'm finding the SQL very difficult to understand, but from your brackets and everything, it looks to me as if the very last join

LEFT JOIN SRS.Lookuptable as Lookuptable_COUNTRY ON Address.ADDR_CNTRY = Lookuptable_COUNTRY.CODE
and Lookuptable_COUNTRY.GROUPCODE='PE')

refers to the 'Address' table, but that table is not defined in that part of the query. It seems to me that 'Address' is defined one level in and so is not avaiable for reference in this outer query.

As I said, very tricky bit of SQL to get one's head around, so I might not have got this correct.

Marc

 

[fredericofonseca]

lots of other things wrong on that SQL

the first inner query (Qry 1)
( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
ON Employeegroup.ID = Employeedetail.ID
and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
does not have any select list fields.

the second inner query (Qry 2)
(
( SRS.Employeegroup AS Employeegroup INNER JOIN SRS.Employeedetail AS Employeedetail
ON Employeegroup.ID = Employeedetail.ID
and Employeedetail.ENTITYTYPE='01' and Employeedetail.ENTITYTYPSEQ=1)
LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY
and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE
and Address.ADCD =
(select max(ADCD) from SRS.Address as Address2 where Address2.PRKEY = Employeegroup.PRKEY
AND Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE
and Address2.ADCD in ('01','02','35')))
again does not have any select list and the join fields are wrong as the inner select (Qry 1) does not have an alias, so can not be joined at all, hence the join clauses are wrong.

and the final join again is wrong as (Qry 2) does not have a alias so again it can not be joined to SRS.Lookuptable


and finally... depending on your version of DB2 (which you did not tell us) this type of related subquery is not allowed

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[galaxy0815]

Hello,

Please be advised that I tried to simplify the SQL a bit (so showed pseudocode). Therefore I just wrote select * from. The important part was the integration of the select max().. into my multiple joins. And this is the solution (again just showing the join part):

...
LEFT JOIN SRS.Address AS Address ON Employeegroup.PRKEY = Address.PRKEY) INNER JOIN (select Address2.PRKEY, max(ADCD) as ADCD from SRS.Address as Address2 where Address2.DEFF<=CURRENT DATE AND Address2.STOPDATE>CURRENT DATE and Address2.ADCD in ('01','02','35') group by Address2.PRKEY) as ADDRESS_MAXIMUM on Address.PRKEY = ADDRESS_MAXIMUM.PRKEY and Address.ADCD = ADDRESS_MAXIMUM.ADCD and Address.DEFF<=CURRENT DATE AND Address.STOPDATE>CURRENT DATE)
...

Thanks!

 

[fredericofonseca]

Well.. if you don't show us the full SQL, or at least a fully working subset of it, we can't really tell what the error is.

Some of us go through the trouble of creating tables that simulate the ones supplied (table names and field names) and then see what the issue is with the SQL - We can't do this if the SQL is incorrect, neither will we try and guess what is missing

But glad you found your solution

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[galaxy0815]

Sorry for this. I was not aware that this "truncation" of the Sql would have such an impact as I thought it would be enough to post just the part were I have the issue with. The next time I face such an issue I will post the full SQL statement.


Thanks!