CODEWITHSUNDEEP
jpacountdistinctjpql
Capacytron
Capacytron

Reputation: 3729

JPA native query generation with COUNT (DISTINCT obj)

please help me with JPQL Query on OpenJPA 1.2.x Here is the text of my query:

select count (distinct evt) from Event evt WHERE (evt.beginDate >= :startOfPeriod and evt.beginDate <= :endOfPeriod) ORDER BY evt.beginDate asc

At runtime I get an error:

org.apache.openjpa.persistence.ArgumentException: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803 , SQLERRMC=BEGIN_DATE, DRIVER=3.59.81 {prepstmnt 1520523937 SELECT COUNT(DISTINCT t0.ID), t0.BEGIN_DATE FROM COMPANY.EVENT t0 WHERE (t0.BEGIN_DATE >= ? AND t0.BEGIN_DATE <= ?) optimize for 1 row [params=(Timestamp) 2010-12-14 00:00:00.0, (Timestamp) 2010-12-14 23:59:59.999]} [code=-119, state=42803]

Why does JPA prints field t0.BEGIN_DATE in select clause? Why did JPA miss ORDER BY clause in native SQL? For example, this query (without t0.BEGIN_DATE in select clause) is valid:

SELECT COUNT(DISTINCT t0.ID) 
    FROM CALENDAR.EVENT t0
    WHERE (t0.BEGIN_DATE >= null AND t0.BEGIN_DATE <= null)

I've set null just for successful SQL query execution

Is it a some kind of a bug?

Upvotes: 3

Views: 4901

Answers (1)

Capacytron
Capacytron

Reputation: 3729

Oh, man... just delete "ORDER BY evt.beginDate asc". So stupid. I do count, I don't need order by... Problem has been solved by myself. Excuse me for disturbing.

Upvotes: 3

Related Questions