db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Wilson <ch...@aptivate.org>
Subject Re: Issue with COUNT function
Date Fri, 21 Jan 2011 18:03:37 GMT
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.
Mime
  • Unnamed multipart/mixed (inline, None, 0 bytes)
View raw message