| distinct with order by sql query not working in eclipsleink [message #1861708] |
Mon, 30 October 2023 11:26  |
 Sanjana C
Messages: 31
Registered: December 2022 |
Member |
|
|
Hi ,
When eclipselink hits below query ,it fails to execute with below error.The expression is written using ReadAllQuery .
ORA-01791: not a SELECTed expression
Query: ReadAllQuery(referenceClass=KursZeitpunktImpl ,SELECT DISTINCT t0.ID AS a1, t0.Abr_Alert_Mail AS a2, t0.Abr_Alert_Postkorb AS a3, t0.AnzahlLKRGeloescht AS a4, t0.GueltigAb AS a12, TRUNC(NVL(t0.GueltigAb, t1.Beginn), 'DDD') FROM xyz t1, abc t0 WHERE (t1.ID = t0.KUR_ID) ORDER BY TRUNC(NVL(t0.GueltigAb, t1.Beginn), 'DDD') DESC;
below is the stack trace;
Caused by: java.sql.SQLSyntaxErrorException: ORA-01791: Kein mit SELECT ausgewählter Ausdruck
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:340)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2062)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:309)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:260)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:246)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:317)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:712)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2740)
at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2693)
at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:559)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1175)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:911)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1134)
at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:460)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1222)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2899)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1863)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1845)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1796)
But the same query works in sql developer. It should work because expression is used in orderby and same is declared in select statement
I saw one blog saying to change the version of eclipselink.
Iam using eclipselink 2.7.6, java 8 and i tried with eclipselink 2.6.5 too but didnot work.
It would be helpful if anyone has idea. Thanks in advance!
|
|
|
|
Powered by
FUDForum. Page generated in 0.02880 seconds
] 
Search
Help
Register
Login
Home
