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

How to Query MAX SQLFunction by Query Builder?\

jluo
1-Newbie

How to Query MAX SQLFunction by Query Builder?\

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?

3 REPLIES 3
jessh
5-Regular Member
(To:jluo)

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
21-Topaz I
(To:jluo)

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
4-Participant
(To:jluo)

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.

 

 

 

 

Announcements