Select NEW Issues

[dharga]

So I'm pretty new to using OpenJPA so this might be a simple fix...

I'm doing the following query.

select new com.bcbst.odstats.ejb.beans.RangeStats(count(a), avg(a.loadTime))
FROM ODUsage a
WHERE
  a.accessDate BETWEEN :startDate AND :endDate and
  a.loadTime is not NULL

I've noticed the following.  If I remove the AVG aggregation function (and change the RangeStats constructor accordingly) it works fine in all cases.

The whole function is here.
http://aluink.pastebin.com/m49bbacb8

The RangeStats class is here
http://aluink.pastebin.com/m54beb0d

When I try to loop and do several queries I get this error.  http://aluink.pastebin.com/m164f3d96

I've also noticed that if do the query just once with static :startDate and :endDate parameter values it works fine.  If I run a loop over a date range which includes the static date previously used, it fails.

The end goal is to reproduce the following transact-sql query and have the values in somekind of bean.  I really don't like the idea of doing multiple queries, but have yet to find a better solution.  I'm open to idea.

SELECT count(*), avg(loadTime), month(accessDate)
FROM Table
WHERE
  accessDate BETWEEN :startDate AND :endDate
GROUP BY month(accessDate)

TIA!

[Pinaki Poddar]

Hi,
  JPQL implementation of OpenJPA 1.3 (or before) had this limitation: if you specify a NEW in select projection then rest of the projection clauses are silently ignore.
  This is in agreement with your observation.
   The good news is, the latest version of OpenJPA does not have any such restriction.

Pinaki

dharga wrote:

So I'm pretty new to using OpenJPA so this might be a simple fix...

I'm doing the following query.

select new com.bcbst.odstats.ejb.beans.RangeStats(count(a), avg(a.loadTime))
FROM ODUsage a
WHERE
  a.accessDate BETWEEN :startDate AND :endDate and
  a.loadTime is not NULL

I've noticed the following.  If I remove the AVG aggregation function (and change the RangeStats constructor accordingly) it works fine in all cases.

The whole function is here.
http://aluink.pastebin.com/m49bbacb8

The RangeStats class is here
http://aluink.pastebin.com/m54beb0d

When I try to loop and do several queries I get this error.  http://aluink.pastebin.com/m164f3d96

I've also noticed that if do the query just once with static :startDate and :endDate parameter values it works fine.  If I run a loop over a date range which includes the static date previously used, it fails.

The end goal is to reproduce the following transact-sql query and have the values in somekind of bean.  I really don't like the idea of doing multiple queries, but have yet to find a better solution.  I'm open to idea.

SELECT count(*), avg(loadTime), month(accessDate)
FROM Table
WHERE
  accessDate BETWEEN :startDate AND :endDate
GROUP BY month(accessDate)

TIA!