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));
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?
Thank you,
Hal Maner
