Hi All

I am using Hibernate version2.

I have a complex native SQL query which works fine when I execute it in Oracle from Toad or Oracle client.

Now I tried to embed this query in Hibernate's createSQLQuery () but I reckon I am missing some pieces over here. Do guide me on the same.


My Oracle SQL query is something like this
(To avoid confusion I am not posting the complete query just the important parts)

select a.name, sum(a.total), sum(a.open), avg(b.avg_open)
from
(
select AGENT.name,
count(REQUEST.REQUEST_ID) TOTAL,
sum(decode(REQUEST.status,'Open',1,0)) OPEN,
from <<<<AGENT, REQUEST where clause >>>
) a,
(
select AGENT.name,
round(avg(decode(ACTIVITY.status,'Open',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE)),0)
AVG_OPEN
from <<<<AGENT, REQUEST,ACTIVITY where clause >>>
) b
where a.name = b.name
group by grouping sets(a.name,())


Can some one help me in getting this query inside the createSQLQuery ()
syntax.


Regards,

Hi

Here is what I am using

select a.name as {BR.NAME}, sum(a.total) as {BR.TOTAL}, sum(a.open) as
{BR.SUMOPEN} , avg(b.avg_open) as {BR.AVGOPEN}
from
(
select AGENT.name,
count(REQUEST.REQUEST_ID) TOTAL,
sum(decode(REQUEST.status,'Open',1,0)) OPEN,
from <<<<AGENT, REQUEST where clause >>>
) a,
(
select AGENT.name,
round(avg(decode(ACTIVITY.status,'Open',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE)),0)
AVG_OPEN
from <<<<AGENT, REQUEST,ACTIVITY where clause >>>
) b
where a.name = b.name
group by grouping sets(a.name,())

***********

results = session.createSQLQuery(sqlQuery,new String[] {"BR"},
new Class [] {Request.class,Agent.class,Activity.class}).list();

Can you please post the error message you get?

Hi Mike

The error is caused when I am executing the query

****************
results = session.createSQLQuery(
sqlQuery, new String[] {"BR"},
new Class[] {Request.class,Agent.class,Activity.class}
).list();
****************

Here is the stack trace:

**************
java.lang.ArrayIndexOutOfBoundsException
at net.sf.hibernate.loader.SQLLoader.<init>(SQLLoader.java:64)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3861)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)

**************

So its not that the query is failing. I think the array being set in my session.createSQLQuery is erroneous

Regards

Hi

I got around the problem by giving all the aliases which I am using

so this works now
results = session.createSQLQuery(
sqlQuery, new String[] {"BR","a","b"},
new Class[] {Request.class,Agent.class,Activity.class}
).list();


Here is the query which I am using
select a.name as {BR.NAME}, sum(a.total) as {BR.TOTAL}, sum(a.open) as
{BR.SUMOPEN} , avg(b.avg_open) as {BR.AVGOPEN}
from
(
select AGENT.name,
count(REQUEST.REQUEST_ID) TOTAL,
sum(decode(REQUEST.status,'Open',1,0)) OPEN,
from <<<<AGENT, REQUEST where clause >>>
) a,
(
select AGENT.name,
round(avg(decode(ACTIVITY.status,'Open',ACTIVITY.ACTIVITY_DATE - REQUEST.REQUEST_DATE)),0)
AVG_OPEN
from <<<<AGENT, REQUEST,ACTIVITY where clause >>>
) b
where a.name = b.name
group by grouping sets(a.name,())

but now I am getting this error
***************
No column name found for property [NAME]
***************

How can i set the properties for values which are calculated such as TOTAL & SUMOPEN above. I wont be having any mappings for these columns in hbm.xml files.

Regards

Hi

I searched this forum for some way of handling the aggregate functions like count , sum as stated in the query above.

But it seems most solution like addScalar & return alias are specific to Hibernate version3.0

Can some one give me an option which can work in Hibernate version2.0.

Also do let me know if creating a custom class (just for the select query values) will help in this scenario


Regards

Create a mapping similar to following. SQL can be as complex as you need. Remember that the id must be unique for each row returned.


Add reference to this mapping to configuration.

Create a POJO that corresponds to the mapping.

You should be able to execute using something like this:


This should work -- I used this extensively with H2 prior to moving to H3.

Curtis ...