Ok, let me start with my sql. I just ran the below code successfully, please note the "group by trunc(this_.END_DATE, 'DD') order by DAILY asc".
select trunc(this_.END_DATE, 'DD') as DAILY,
min(this_.PH) as Min, avg(this_.PH) as Avg,
max(this_.PH) as Max, stddev(this_.PH) as Std,
count(this_.PH) as Count
from
water_quality_measures_mv this_ where this_.SITE_ID='WC503432-175' and this_.END_DATE between to_date('12/12/2004','mm/dd/yyyy') and to_date('12/12/2005','mm/dd/yyyy')
group by trunc(this_.END_DATE, 'DD') order by DAILY asc
My database is Oracle 10.1.2. The first thing I ve done was to create a StdProjections and TruncProjections classes that implements AggregateProjection. They are working fine. Below is my criteria:
projectionList.add(TruncProjections.trunc("endDate", period),"ANYALIAS");
projectionList.add(Projections.min(attribute), "Min");
projectionList.add(Projections.avg(attribute), "Avg");
projectionList.add(Projections.max(attribute), "Max");
projectionList.add(StdProjections.stddev(attribute).as("std"), "Std");
projectionList.add(Projections.count(attribute), "Count");
criteria.setProjection(projectionList);
if (orderByClause != null && !orderByClause.equals("")) {
if (asc)
criteria.addOrder(Order.asc(orderByCluase));
else
criteria.addOrder(Order.desc(orderByCluase));
}
The above translate into:
Hibernate: select * from ( select trunc(this_.END_DATE, 'DD') as y0_, min(this_.PH) as y1_, avg(this_.PH) as y2_, max(this_.PH) as y3_, stddev(this_.PH) as y4_, count(this_.PH) as y5_ from water_quality_measures_mv this_ where this_.SITE_ID=? and this_.END_DATE between ? and ? order by y0_ asc ) where rownum <= ?
20:49:00,081 ERROR JDBCExceptionReporter:72 - ORA-00937: not a single-group group function
So what is missing is the group by expression. I cannot do Projections.groupProperty(arg0) since the arg0 cannot be trunc(this_.END_DATE, 'DD'). It has to be a property (correct me if I am wrong please).
I also tried:
projectionList.add(Projections.sqlGroupProjection(
"trunc(end_date, " + period + "),"
+ "to_char(min(" + attributeDbName
+ "), '0000.000')," + "to_char(avg("
+ attributeDbName + "), '0000.000'),"
+ "to_char(max(" + attributeDbName
+ "), '0000.000')," + "to_char(stddev("
+ attributeDbName + "), '0000.000'),"
+ "count(" + attributeDbName + ")",
"trunc(end_date, " + period + ")",
new String[] { periodHeader, "Min", "Avg", "Max", "Std",
"Count" }, new Type[] { Hibernate.DATE,
Hibernate.DOUBLE, Hibernate.DOUBLE,
Hibernate.DOUBLE, Hibernate.DOUBLE,
Hibernate.INTEGER }), attributeDbName);
but the criteria.addOrder(Order.asc(periodHeader)); retuen invalid identifier.
Below is my mapping files:
<hibernate-mapping>
<class name="gov.noaa.nwfsc.sdm.stm.model.businessobject.WaterQualityMeasures"
table="water_quality_measures_mv">
<id
name="monitoringEventDbId"
type="integer"
column="MONITORING_EVENT_DB_ID">
<generator class="assigned" />
</id>
<property name="siteId" type="string"
column ="SITE_ID" length="30" />
<property name="streamName" type="string"
column ="STREAM_NAME" length="100" />
<property name="startDate" type="date"
column ="START_DATE" length="7" />
<property name="endDate" type="date"
column ="END_DATE" length="7" />
<property name="startTime" type="date"
column="START_TIME" length="7" />
<property name="endTime" type="date"
column ="END_TIME" length="7" />
<property name="temp" type="big_decimal"
column="TEMP" precision="22" scale="0" />
<property name="ph" type="big_decimal"
column ="PH" precision="22" scale="0" />
<property name="turbsc" type="big_decimal"
column ="TURBSC" precision="32" scale="16" />
<property name="spcond" type="big_decimal"
column ="SPCOND" precision="22" scale="0" />
<property name="dissolvedOxygen" type="big_decimal"
column ="DISSOLVED_OXYGEN" precision="22" scale="0" />
<property name="doPrcnt" type="big_decimal"
column ="DO_PRCNT" precision="22" scale="0" />
<property name="avgDailyFlow" type="big_decimal"
column ="AVG_DAILY_FLOW" precision="22" scale="0" />
<property name="notes" type="string"
column="NOTES" length="4000" />
</class>
</hibernate-mapping>
|