Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.1
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

How do you group by alias, for example:

projectionList.add(Projections.min(xxxx)), I want to group by Projections.min(xxxx). If I projectionList.add(Projections.min(xxxx), alias), is there something like criteria.addOrder(Order.asc(alias)); where alias is the alias name and not a property.

The previous post should say order by not group by. Thank you

You can't. It's not an Hibernate issue, it's just not allowed in SQL. You can't group by projections or subselects: group by accepts only simple columns.

Thank You for your reply. Its my fault that I started the confusion about group by and order by. However, let me explain my situation I have the following:
select trunc(this_.END_DATE, 'DD') as DAILY,
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='xxxxxxx' and this_.END_DATE between to_date('x/x/xxxx','mm/dd/yyyy') and to_date('x/xx/xxxx','mm/dd/yyyy')
group by trunc(this_.END_DATE, 'DD') order by y2_ asc

I just ran this on Oracle and its working just fine. How do I do the part "order by y2_ asc" with criteria. This can be done in oracle with simple sql like the one presented and it just ran with no problems and returned the expected result. Can this be done with hibernate criteria, projections, or sqlgroupprojection, I tried all with no success. Any help is really appreciated

Thank You

There are two examples in section 16.7, "Projections, aggregation and grouping", that show you how to do this. Here's one of them:

I tried it but How do u add the group by.
Projections.groupProperty(arg0) works on attributes and not function. I tried:

projectionList.add( Projections.sqlGroupProjection("trunc(end_date, 'DD')", "trunc(end_date, 'DD')",new String[]{"XXX"}, new Type[]{Hibernate.DATE}))

But when I do criteria.addOrder(Order.asc("XXX")); It complains ORA-00904: "DAILY": invalid identifier. In fact when I see the hibernate generated sql, I do not see an alias XXX.

any hints

Please ignore the previous post:

I tried it but How do u add the group by.
Projections.groupProperty(arg0) works on attributes and not function. I tried:

projectionList.add( Projections.sqlGroupProjection("trunc(end_date, 'DD')", "trunc(end_date, 'DD')",new String[]{"DAILY"}, new Type[]{Hibernate.DATE}))

But when I do criteria.addOrder(Order.asc("DAILY")); It complains ORA-00904: "DAILY": invalid identifier. In fact when I see the hibernate generated sql, I do not see an alias DAILY.

any hints

Why can't you do it the way the example suggests? "group by trunc('xxx')" won't work in SQL, you can't expect it to work with criteria.

Perhaps you need to post the full criteria as you have it at the moment, along with a brief description of the table and mapping.

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>

Oops, sorry, yea, I meant "order by trunc('xxx')" won't work in SQL. Silly me.

In your TruncProjection class, what are you returning for isGrouped? Try returning true. Or create a TruncGroupingProjection class that extends TruncProjection and overrides isGrouped to return true.

BTW, an example grouping projection (in case you need to override toGroupSqlString, too) is SQLProjection. You'll want something along those lines in TuncProjection, I think.

That did it:

below is the class in case anyone needs it. I have not fully reviewed the code but it works.


public class TruncProjection extends SimpleProjection {
protected final String propertyName;
private final String aggregate;
private final String format;

public TruncProjection(String aggregate, String propertyName, String format) {
this.aggregate = aggregate;
this.propertyName = propertyName;
this.format = format;
}

public String toString() {
return aggregate + "(" + propertyName + ","+ format+ ')';
}

public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
return new Type[] { criteriaQuery.getType(criteria, propertyName) };
}

public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery)
throws HibernateException {
StringBuffer buffer = new StringBuffer()
.append(aggregate)
.append("(")
.append( criteriaQuery.getColumn(criteria, propertyName) )
.append( ", '"+format+"'" ).append(") as y")
.append(loc)
.append('_');
return buffer.toString();
}

public boolean isGrouped() {
return true;
}
/*
public String toGroupSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
throw new UnsupportedOperationException("not a grouping projection");
}*/
public String toGroupSqlString(Criteria criteria, CriteriaQuery criteriaQuery)
throws HibernateException {
return StringHelper.replace( aggregate+"("+criteriaQuery.getColumn(criteria, propertyName)+", '"+format+"')", "{alias}", criteriaQuery.getSQLAlias(criteria) );
}
}


Anyy comments are welcomed.

Thanks