Q:

distinct & trim in JPA with hibernate 3.2.6

Is there a way to do select  distinct trim(addressInfo.city) in JPA ?
I got this error (without the trim it works fine)
Caused By: java.sql.SQLSyntaxErrorException: ORA-01791: not a SELECTed expression

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    Truncated. see log file for complete stacktrace

>
and Here the generated SQL:
select distinct trim(addressinf3_.city) as col_0_0_ 
    from t_application applicatio0_ 
    left outer join t_user user1_ on applicatio0_.first_signatory=user1_.id 
    left outer join t_company company2_ on user1_.company=company2_.id 
    left outer join t_address_info addressinf3_ on company2_.address=addressinf3_.id 
where applicatio0_.discriminator='SERVICE' and (addressinf3_.city is not null) order by addressinf3_.city

A:

This exception usually happens when you have some column/expression in order by clause which you don't have in select clause, in combination with distinct. Without knowing the actual JPQL query you are executing, here is an example that should work
select distinct trim(addressInfo.city) 
from AddressInfo addressInfo 
where <some conditions> 
order by trim(addressInfo.city)

If you use trim(addressInfo.city) in select, then you must use it also in order by. Without trim it won't work.

