Is there a translation problem in EJBQL with the DISTINCT construct and MSSQL? I have a query:
SELECT DISTINCT OBJECT(a) FROM Account a LEFT JOIN a.lastAccountActivity laa JOIN a.territory t JOIN t.collectors c JOIN c.team tm JOIN tm.supervisors s WHERE s.employee.username = :username ORDER BY laa.time ASC, a.accountNumber
This works fine in MySQL and returns as list of distinct Account objects (that are in some way related to me as an employee with "supervisor" role) sorted by the time field on the Account.lastAccountActivity object.
When I run this query against MSSQL I get the following error:
Preparing the statement failed: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
I'm using the correct MSSQL dialect for the hibernate settings.
Here is the issue:
I'm using an older version of the EJB3 spec (the first public draft from June 2005). Since I know that the first reponse will be to migrate to a more recent release: Does anyone know if this is resolved in the more recent releases?
Thanks!
