Showing results for 
Search instead for 
Did you mean: 
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

How to Query MAX SQLFunction by Query Builder?\


How to Query MAX SQLFunction by Query Builder?\

In order to query the latest WTpart 'version', etc A,B.C...etc.


 QuerySpec qs1 = new QuerySpec(); 


int classIndex = qs1.appendClassList( wt.part.WTPart.class, false );

SQLFunction fun=new SQLFunction(SQLFunction.MAXIMUM,new ClassAttribute( wt.part.WTPart.class , "versionInfo.identifier.versionId" ) );

qs1.appendSelect(fun, new int[] { classIndex }, false);

qs1.appendWhere(new SearchCondition( wt.part.WTPartMaster.class , "number" , "=" , "test_number" ),WTPartMasterIndex );

The query result always throw exception

"A statement with aggregate function can not be used with access control."

I try to change SQLFunction MAXIMUM to "LENGTH", and It is work.

What's the hell? What can I do resovle the query requirement?

5-Regular Member

The MAX function is performed at the SQL level prior to and without regards to access control filtering -- finding the MAX amongst all records. Thus in order to use it you must by-pass access control (in part to acknowledge this).

22-Sapphire I

If you're intending to use MAX in order to get the latest Revision, you have to use a different approach - since the Revision series could be anything (e.g. 1, 2, -, A, B....). The SQL function has no way to "know" what the next value is. So - Have to use a convoluted sub select in query builder - there are some CS examples.


I know this is an old post - but I had the same problem and found this message.


The other applies are right - version search should be done differently, but the error message was likely created because you use


instead of



as the find() method in persistence helper is using access control, which causes the problem, while the server helper query() method does not.

This applies to all aggregate function.