java.sql.SQLSyntaxErrorException: unexpected token: ON

Question

I have this query which works fine on DBeaver for Postgres:

select profileId from (
    select distinct on (p.profile_id) p.profile_id as profileId, pr.creation_date as creationDate
    from table1 p
    
             join table2 o on (p.profile_id = o.offeree_profile_id) 
             join table3 pr on (o.offer_id = pr.offer_id ) 
    where 
     
             o.offer_status_id = 'ACCEPTED' 
             
    and (pr.status != 'TERMINATED') 
             and o.offeror_profile_id in 
    
    (select p.profile_id from table4 u 
    join table1 p on (p.user_id = u.user_id) 
    where customer_id = 'A'
    AND o.offeror_profile_id != o.offeree_profile_id)
    ) sub
order by creationDate desc limit 6

But when I run it from Java code, it throws error:

String 
queryString = "select profileId from (" +
    "select distinct on (p.profile_id) p.profile_id as profileId, pr.creation_date as creationDate " +
    "from table1 p " +
    "join table2  o on (p.profile_id = o.offeree_profile_id ) " +
    "join table3 pr on (o.offer_id = pr.offer_id ) " +
    "where " +
    "o.offer_status_id = 'ACCEPTED' " +
    "and (pr.status != 'TERMINATED') " +
    "and o.offeror_profile_id in " +
    "(select p.profile_id from table4 u " +
    "join table1 p on (p.user_id = u.user_id) " +
    "where customer_id = :CustomerId " +
    "and o.offeror_profile_id != o.offeree_profile_id)" +
    ") sub " +
    "order by creationDate desc limit 6";


final Query profileIdQuery = getCurrentSession().createSQLQuery(queryString).setString("CustomerId", CustomerId);

List<BigInteger> profileIdList = profileIdQuery.list();

Above line throws this error:

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ON

P.S. The reason for using distinct on is that I need table1.profile_id to be returned but need to sort by table3.creation_date. If I copy/paste the query I got from Java code into database client, it still runs with no error.

Answer 1

on is reserved word you can not us as alias. If you want to use wrapped it 'on' ` sign