How to Query MAX SQLFunction by Query Builder?\

[jluo]

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

querySpec：

 QuerySpec qs1 = new QuerySpec(); 

 qs1.setAdvancedQueryEnabled(true);

 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?

[jessh]

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).

[MikeLockwood]

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.

[timo_san]

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

PersistenceServerHelper.manager.query(statement);

instead of

PersistenceHelper.manager.find(statement);

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.