Hi all,
i'm using eclipselink 2.3.2 on glassfish 3.1.1.
My database is a Postgre 9.1 (and jdbc driver)
SQL query that works in postgre 9.1 :
SELECT sum(count), date_part('WEEK', tcreated) as week
FROM usulTests
GROUP BY week
ORDER BY week DESC
And the JPA (eclipselink 2.3.2) version that doesn't work :
SELECT SUM(b.count), FUNC('date_part','WEEK', b.tcreated) AS week FROM BidLEntreprise b GROUP BY week ORDER BY week
The exception :
org.postgresql.util.PSQLException: ERROR: column "usulTests.tcreated" must appear in the GROUP BY clause or be used in an aggregate function
If i try
SELECT SUM(b.count), FUNC('date_part','WEEK', b.tcreated) AS week FROM BidLEntreprise b GROUP BY FUNC('date_part','WEEK', b.tcreated) ORDER BY FUNC('date_part','WEEK', b.tcreated)
I have the same error
but if i try
SELECT SUM(b.count), b.tcreated AS week FROM BidLEntreprise b GROUP BY week ORDER BY week
It works