Generating your own JPA queries using COUNT (DISTINCT obj)

Question

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

select a counter (separate evt) from the evt WHERE event (evt.beginDate> =: startOfPeriod and evt.beginDate <=: endOfPeriod) ORDER BY evt.beginDate ascending

At runtime, I get an error message:

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_. EVENT t0 WHERE (t0.BEGIN_DATE> =? AND t0.BEGIN_DATE <=?) Optimized for 1 line [params = (Timestamp) 2010-12-14 00: 00: 00.0, (timestamp) 2010-12-14 23: 59: 59.999]} [code = -119, state = 42803]

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

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

I set to null only to successfully execute SQL queries

It's some kind of mistake?

Answer 1

Oh man ... just delete "ORDER BY evt.beginDate asc". So stupid. I think I don’t need order ... The problem was solved by me. I'm sorry to bother you.