DISTINCT with ORDER BY Flexible search query giving error .

[Former Member]

Hi,

Try to execute below query in code and from HAC, but giving error.Please help me.

Query :

      SELECT DISTINCT {customer:uid} FROM {B2BCustomer AS customer JOIN PrincipalGroupRelation AS pg ON {customer:pk}={pg:source} JOIN B2BUnit AS bu ON {pg:target}={bu:pk} JOIN UserTypeEnum AS userTypeEnum ON {customer:userType}={userTypeEnum:PK}} WHERE {userTypeEnum:code}='CUSTOMER' AND {bu:uid} IN ('100418') ORDER BY {customer:creationtime} DESC

Error:

    org.hsqldb.HsqlException: invalid ORDER BY expression

[crescenzorega · Accepted solution]

Probably, you have a exception "invalid order by expression" because you specify a column in ORDER BY that are not explicit returning in SELECT clause

[Former Member · Accepted solution]

Thanks, I need customers in same criteria.

[crescenzorega · Accepted solution]

If my answer was useful for you please click on accept

[former_member387866]

Hi Nagajuna,

You need to include the creationtime in the SELECT area.

Inner Query:

 SELECT {customer:uid} AS ID, {customer:creationtime}
 ...
 ORDER BY {customer:creationtime} DESC
 AS id_creation

You can then SELECT DISTINCT ID FROM id_creation in the Wrapper Query.

Regards,
Luke