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

Mime
View raw message