I am using Derby 10.4 supplied with JavaEE5 SDK/Glassfish 2.1. The following function works perfectly well with Oracle 10g XE using the Oracle ojdbc6 driver -

SELECT COUNT(*) FROM (SELECT file.md5, instCollection.collectionId FROM file, collection leafCollection, collection instCollection WHERE (file.status=1 OR file.status=2) and file.collectionId = leafCollection.collectionId AND leafCollection.instanceCollectionId = instCollection.collectionId GROUP BY file.md5, instCollection.collectionId)

That query under Oracle works fine. Under Derby I get the following error:

[42X01][30000] Syntax error: Encountered "<EOF>" at line 1, column 313.

If I run the query without the SELECT COUNT(*) it will run fine in Derby too, but I am trying to get the count :-)

I feel like I must be missing something obvious here.