db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Halit M. Maner" <hma...@maner.net>
Subject LEFT JOIN with a subquery not working in Derby?
Date Mon, 26 Jun 2006 15:57:24 GMT
The statement below has been working fine in Cloudscape 5.1 but it is not working in Derby
(we are using
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?
Thank you,
Hal Maner

View raw message