Re: LEFT JOIN with a subquery not working in Derby?

[Dy...@Sun.COM]

"Halit M. Maner" <hm...@maner.net> 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