"Halit M. Maner" <[EMAIL PROTECTED]> writes: > The statement below has been working fine in Cloudscape 5.1 but it is not > working in Derby (we are using 10.1.2.4). > > ij> SELECT * FROM admapphdr LEFT JOIN admappdtl ON (aah_id=aad_aah_id AND > aad_seq_num = (SELECT MAX(a2.aad_seq_num) FROM admappdtl a2)); > > It is throwing: > > ERROR 42972: An ON clause associated with a JOIN operator is not valid. > > It works fine if we remove the SELECT MAX (but we need it!)... > > admapphdr is a header table with an INTEGER id. > admappdtl is admapphdr's detail table with the same id plus a sequence number > for the various detail rows... > > Is this a limitation of Derby or are we overlooking something?
Sorry about the late reply. You probably have found a workaround by now. Couldn't you just perform the 'select MAX' first, and insert the value in your join condition? (A pain, I know...) In the general case where the sub-query returns multiple rows, you could create a view of the sub-query, and then join the two tables with this view in a three-way join. -- dt However, experience shows that for many people and many applications a dose of paranoia is reasonable - Bjarne Stroustrup
