This content has been marked as final. Show 4 replies
I'm not sure I understand the point of the select in the from clause, since you are returning the exact same data with only a table name change. If you are using JPQL, you must have an Entity mapped to the table you are selecting from. Are you looking to get entities back or raw data?
Something like "Select trap from Trap trap where trap.agentip="220.127.116.11" and trap.acknowledged=false order by trap.receivedTimestamp desc" would work if you mapped an Entity Trap to table "trap".
Thanks Chris for the reply.
I have basically a table with name
I have to write a query where I have to group by trapname & get the count of each traps. The rows returned should be in the descending order of receivedTimeStamp.
In the query I have mentioned:
select * from (select * from trap where agentip="18.104.22.168" and acknowledged=false order by receivedTimestamp desc) as traps group by trapname order by receivedtimestamp desc;
The inner query gets me the receivedTimeStamp Descending, later on I am grouping it by trapName. If I don't use order by received timestamp in the inner query, it is not fetching me the proper results.
The query I have mentioned works fine in MYSQL, I want to write a similar Query in JPQL, but I am unable to do so. I tried the below, it says unexpected token found in column 15.
select t from (select tp from trap where tp.agentip="22.214.171.124" and tp.acknowledged=false order by tp.receivedTimestamp desc) as traps t group by t.trapname order by t.receivedtimestamp desc;
Edited by: 924448 on Apr 1, 2012 10:10 PM
I am not sure why ordering on the inner query would make the results any different from executing SQL:
"select * from trap where agentip="126.96.36.199" and acknowledged=false group by trapname order by receivedtimestamp desc"
Since the where clause should be processed before the group by, and the broup by operate over the results independent of the inner query order by statement.
You can always just use native SQL if a JPQL query "select t.fiield1, t.field2, <etc> from trap t where t.agentip="188.8.131.52" and t.acknowledged=false group by t.trapname order by t.receivedtimestamp desc"
doesn't work for you.
EclipseLink is also seems to have support for subselects in the from clause through feature: https://bugs.eclipse.org/bugs/show_bug.cgi?id=350597
described here: http://wiki.eclipse.org/EclipseLink/DesignDocs/312146
You can try this support with the EclipseLink 2.4 nightly
Finally the below query worked for my requirements..
FROM trap t WHERE t.agentip IN ("184.108.40.206","220.127.116.11")
AND t.receivedTimestamp = (SELECT MAX(t2.receivedTimestamp)
FROM trap t2
WHERE t2.trapname = t.trapname
and t2.agentip IN ("18.104.22.168","22.214.171.124")
and t2.acknowledged=false) ORDER BY receivedTimestamp DESC;