Hello,
I´m running this query from a session EJB against Glassfish:
SELECT DISTINCT m.PK.num, m.PK.id
FROM HMed m
GROUP BY m.PK.num, m.PK.id
where PK is a primary key entity class.
And it works fine, but now I´m trying to do a SELECT COUNT() for that query, which it turns out to be:
SELECT COUNT(t.PK.num)
FROM (
SELECT DISTINCT m.PK.num, m.PK.id
FROM HMed m
GROUP BY m.PK.num, m.PK.id
) AS t
then glassfish throws an EJBQLException, whose description is: "Syntax error parsing the query...", saying : "unexpected token: (".
That is, it seems that the problem is the left parenthesis just after the FROM word.
If I don´t use parenthesis, then it complains on the DISTINCT word.
But when I translate this kind of queries into SQL they work fine, at least in MySQL Query Browser, returning the correct number of rows.
The alias "t" seems to be necessary, at least for MySQL.
Maybe this kind of query is not supported by Toplink or Glassfish?
Any kind of help would be very much appreciated.
Thanks,,
[Message sent by forum member 'dvacasp' (dvacasp)]
http://forums.java.net/jive/thread.jspa?messageID=319837