dear all...

while getting a sum of a Field(Double) from DB,
i get sometimes NULL when the query unsatisfies the conditions.
To avoid getting NULL at such times, i have used my query like this...

select if(sum(TripSheet.TotalDistance),sum(TripSheet.TotalDistance),0) from routing.cab.domain.TripSheet TripSheet where TripSheet.Cab=3.

while trying this i get the error below...
(i want the query to return Zero(0) when it is NULL)
any help will be highly appreciated.

- New Bie(?)

Hibernate version: 2.0.3

Mapping documents:
<hibernate-mapping>
<class name="routing.cab.domain.TripSheet" table="Trip_Sheet">
<id name="Id" column="Trip_Sheet_Id" type="java.lang.Long">
<generator class="increment"></generator>
</id>
<many-to-one name="Cab" class="routing.cab.domain.Cab" column="Cab_Id"/>
<property name="TripDate" type="java.util.Date" column="Trip_Date"/>
<property name="TotalDistance" type="java.lang.Long" column="Total_Distance"/>
<property name="TotalTime" type="java.lang.Long" column="Total_Time"/>
<property name="TimeIndex" type="java.lang.String" column="Time_Index"/>
<property name="ActualDistance" type="java.lang.Long" column="Actual_Distance"/>
<property name="ActualTime" type="java.lang.Long" column="Actual_Time"/>

<set name="TripRoutes"
table="Trip_Route"
order-by="Trip_Route_Id asc"
lazy="true"
inverse="false">
<key column="Trip_Sheet_Id"/>
<one-to-many
class="routing.cab.domain.TripRoute"/>
</set>

</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
public Collection getTripSheetByQuery(String query) {
Session session = ConnectionFactory.getConnectionInstance().getSession();
Collection tripSheets;
try
{
tripSheets = session.find(query);
}catch ( HibernateException ex ){
ex.printStackTrace();
sfLogger.log( Level.SEVERE, "getTripSheet failed", ex );
throw new RuntimeException( "getTripSheet failed", ex );
}
finally
{
try
{
session.close();
}catch ( HibernateException ex ){
ex.printStackTrace();
sfLogger.log( Level.SEVERE, "closing session failed", ex );
throw new RuntimeException( "closing session failed", ex );
}
}
return tripSheets;
}

Full stack trace of any exception that occurs:
Caused by: net.sf.hibernate.QueryException: undefined alias: if [select if(sum(TripSheet.TotalDistance),sum(TripSheet.TotalDistance),0) from com.celestials.routing.cab.domain.TripSheet TripSheet where TripSheet.TripDate>='2004-09-06' and TripSheet.TripDate <='2004-09-21' and TripSheet.Cab=3]
at net.sf.hibernate.hql.PathExpressionParser.token(PathExpressionParser.java:103)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:154)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1530)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1501)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1491)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1483)
at routing.cab.impl.TripSheetImpl.getTripSheetByQuery(TripSheetImpl.java:150)
... 26 more

Name and version of the database you are using:
MySQL 1.22
The generated SQL (show_sql=true):
not generated.
the actual qry :
select if(sum(TripSheet.TotalDistance),sum(TripSheet.TotalDistance),0) from routing.cab.domain.TripSheet TripSheet where TripSheet.TripDate>='2004-09-06' and TripSheet.TripDate <='2004-09-21' and TripSheet.Cab=3
Debug level Hibernate log excerpt:none.

dear all..

am i wrong in my post? or is it not possible ?
pls suggest me.. its most ugent here.
or can anyone guide me an alternative sol?

-New Bie(?).

Well, this is not really up to Hibernate. It is your database that decides what to do with an aggregate of 0 rows.

yeah ..i agree that..
and i was right in getting the exact 0(zero) from my DB,
if i give the query as ...

"select if(sum(TotalDistance),sum(TotalDistance),0) from Trip_Sheet
where Cab_Id=3" -in MySql query..and it works fine.

"select nvl(sum(TotalDistance),0) from Trip_Sheet
where Cab_Id=3" -in Oracle query..and it works fine.

my qstin here is how to make this query as HQL?

-Munas.

I have the same question? Has this been answered?

I am tring to do something like


select u from org.hibernate.myTable as u WHERE (NVL(u.col1, 0) + NVL(u.col2, 0)) > 1;

Is there anyway to do this?