CODEWITHSUNDEEP
javajpaspring-data-jpajpa-2.0jpa-2.1
Peter Penzov
Peter Penzov

Reputation: 1668

QuerySyntaxException: expecting CLOSE, found '.'

I want to rewrite this SQL query into JPQL and use JPA Projection:

SELECT count(id) as count, status, error_class, error_message, id, settlement_status_raw 
FROM `payment_transactions` 
WHERE `payment_transactions`.`terminal_id` = 16 
AND (created_at > '2019-06-01 00:00:00.000000') 
AND (`payment_transactions`.`status` != 'approved') 
GROUP BY `payment_transactions`.`error_message`  ORDER BY count DESC

I tried this:

@Query(value = "SELECT new org.plugin.service.PaymentTransactionsDeclineReasonsDTO(e.id, count(id) as e.count, e.status, e.error_class, e.error_message) " +
            " FROM payment_transactions e " +
            " WHERE e.terminal_id = :id AND (e.created_at > :created_at) " +
            " AND (e.status != 'approved') " +
            " GROUP BY e.error_message " +
            " ORDER BY e.count DESC")

But I get error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '.' near line 1, column 96 [SELECT new org.plugin.service.PaymentTransactionsDeclineReasonsDTO(e.id, count(id) as e.count, e.status, e.error_class, e.error_message)  FROM payment_transactions e  WHERE e.terminal_id = :id AND (e.created_at > :created_at)  AND (e.status != 'approved')  GROUP BY e.error_message  ORDER BY e.count DESC]"}}

Can you give me some advice how to rewrite this query in JPQL properly?

Upvotes: 4

Views: 9147

Answers (2)

Jens Schauder
Jens Schauder

Reputation: 81862

I agree with Mike.F e.count is not a valid expression. But the statement has more problems:

  • e.id, e.status, e.error_class aren't part of the GROUP BY so they can't be used in the select. Either add them to the GROUP BY or use an aggregate function, like MAX or MIN.

  • You can't define aliases inside a constructor expression.

Upvotes: 2

Mike Feustel
Mike Feustel

Reputation: 1317

It looks like the problem is the dot at e.count.

It seems, it should be count(e.id) as count. Maybe count(1) as count would return the same result.

Upvotes: 1

Related Questions