Hi NBW, On Fri, 21 Jan 2011, NBW wrote:
> 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. I think that every sub-SELECT must have a table alias in Derby, so add "AS t" to the end of the query (after the closing parenthesis) and it might work :) Cheers, Chris. -- Aptivate | http://www.aptivate.org | Phone: +44 1223 760887 The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES Aptivate is a not-for-profit company registered in England and Wales with company number 04980791.
