When i am trying to execute below query , i am getting column present in distinct must be present in order by. But as seen in below query it is working internal in JPQL.
I am trying to execute query in for loop , i had count of below query and i am trying to fetch and process data in chunk wise like 0 to 5000 the 5001 to 10000 depending on how data size.
One observation i found is if below query has output of more than 5000 data then this throws and error if less than 5000 it works, just an observation.
LogicalCondition condition;
dataManager.load(User.class)
.query("SELECT DISTINCT e FROM Cases e " +
" LEFT JOIN UserCase a " +
" ON e.Id = a.Id "
.condition(
condition
).maxResults(0).maxResults(5000).list();
Below order by getting printed in query if check in debug mode
ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT null)) OFFSET 0 ROWS FETCH NEXT 5000 ROWS ONLY
You passing User.class as a parameter to dataManager.load() method, but then querying Cases objects.
Please pass the same class to the load method as you use in your query.
Correction on above question same class is used in load method
dataManager.load(Cases.class)
.query("SELECT DISTINCT e FROM Cases e " +
" LEFT JOIN UserCase a " +
" ON e.Id = a.Id "
.condition(
condition
).maxResults(0).maxResults(5000).list();
Even if don’t use maxResults just use just load the query with distinct i am still getting exception.
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.9.6-jmix): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Error Code: 145