Hi,
I am trying a similar JPQL:
select c.name, count(distinct d) as od from Corporation c, IN(c.divisions) d
group by c.name order by od
In the trunk level code, it works fine. The generated SQL is:
SELECT t0.CORPORATION_NAME, COUNT(DISTINCT t1.ID) AS od FROM
user318.CORPORATION
t0 INNER JOIN user318.DIVISION t1 ON t0.ID = t1.CORPORATION_ID GROUP BY
t0.CORPORATION_NAME ORDER BY od ASC
The same JPQL, when running against openjpa 1.2.x, throws the exception as you
reported.
Removing the alias as you suggested:
select c.name, count(distinct d) from Corporation c, IN(c.divisions) d group by
c.name order by count(distinct d)
The generated SQL is:
SELECT t0.CORPORATION_NAME, COUNT(DISTINCT t1.ID), COUNT(DISTINCT t1.ID) FROM
user318.CORPORATION t0 INNER JOIN user318.DIVISION t1 ON t0.ID =
t1.CORPORATION_ID GROUP BY t0.CORPORATION_NAME ORDER BY COUNT(DISTINCT t1.ID)
ASC
The generated SQL has the order by clause, supposedly the result should be
ordered. Can you turn on the openjpa trace to see what sql is generated?
Regards,
Fay
----- Original Message ----
From: Christopher Giblin <c...@zurich.ibm.com>
To: users@openjpa.apache.org
Sent: Mon, October 11, 2010 7:36:42 AM
Subject: Select aliases not working in OpenJPA 1.2.1
Hi,
I am using the following OpenJPA version in an Eclipse RCP app:
OpenJPA 1.2.1
version id: openjpa-1.2.1-r2180:4612
I receive errors when using aliases in a select statement:
Example:
select s.name, count(distinct g) as c from Systems s, IN(s.groups) g group
by s.name order by c
Error msg:
Encountered "as" at character 34, but expected: [",", "FROM"].by c"
When I drop the alias and order by the count, as in the following example,
select s.name, count(distinct g) from Systems s, IN(s.groups) g group by
s.name order by count(g)
the query is executed but the result is not ordered properly. Is this due
to the use of "distinct" in the query, but not the ORDER? According to the
query syntax, "distinct" cannot be used with ORDER.
Is the use of aliases in the select as I describe here part of JPA 2.0 and
therefore not supported in my OpenJPA 1.2.1?
If so, what any suggestions on how to order on count of a distinct
attribute?
Thanks,
Chris
