Birt dataset says " Incorrect " for correct HQL ( Using Subselct )

ganesh1984

Hi All,

I am getting strange issues with Birt. I am trying to fetch some records from the table using a simple query with subselects. Its working fine when i execute this query inside java class (I am able to see all records. its perfect) , but when i try the same thing inside Birt data set i am getting birt exception.

Hibernate Query :

select month(loanapp.statusHistory.startTime) ,
(select count(la.status) from
LoanApplication la where la.status = 'APPROVED' and
month(la.statusHistory.startTime) = month(loanapp.statusHistory.startTime)
group by month(la.statusHistory.startTime)),
count(loanapp.statusHistory.startTime),
avg(loanapp.primaryCustomer.creditReport.summary.scoreValue),
sum(loanapp.amount),avg(loanapp.amount)
from LoanApplication loanapp
group by month(loanapp.statusHistory.startTime)

In the second column i am using sub select. when i test query it says " The query is valid ".

When i try to see Preview Results it throws exception:

Exception:
*********************************************************
A BIRT exception occurred.
Plug-in Provider:Eclipse.org
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:2.3.2.r232_v20090211
Error Code:odaconsumer.CannotPrepareStatement
Error Message:Failed to prepare the following query for the data set type org.jboss.tools.birt.oda.dataSet.
[select month(loanapp.statusHistory.startTime) ,
(select count(la.status) from
LoanApplication la where la.status = 'APPROVED' and
month(la.statusHistory.startTime) = month(loanapp.statusHistory.startTime)
group by month(la.statusHistory.startTime)),
count(loanapp.statusHistory.startTime),
avg(loanapp.primaryCustomer.creditReport.summary.scoreValue),
sum(loanapp.amount),avg(loanapp.amount)
from LoanApplication loanapp
group by month(loanapp.statusHistory.startTime)

]
2

Reason:
A Birt Exception Occured.

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

I have all plugins related to Hibernate ODA. Infact most of my hibernate queries are working properly. I dont know what's wrong with. is it a bug?

Remember its working when i execute it in java class. So there is no syntax problem.

any kind of help is appreciated.

regards,
ganesh

Find more posts tagged with

Comments

ganesh1984

Hi,

it works if i use sub select as last column in the select clause. This is really strange.

i.e

select month(loanapp.statusHistory.startTime) ,
count(loanapp.statusHistory.startTime),
avg(loanapp.primaryCustomer.creditReport.summary.scoreValue),
sum(loanapp.amount),avg(loanapp.amount),
(select count(la.status) from
LoanApplication la where la.status = 'APPROVED' and
month(la.statusHistory.startTime) = month(loanapp.statusHistory.startTime)
group by month(la.statusHistory.startTime))
from LoanApplication loanapp
group by month(loanapp.statusHistory.startTime)

But it doesn't works if i use any other column after sub select clause.
one more point is, Right now in the query i am using only one column in sub select. It doesn't works if i use two columns in sub select clause.

These are working properly in java class.These are really strange. I wonder how we can consider Birt for reporting if it doesn't support these basic requirements like using sub selects in the query whatever might be its appearing order in the query.

There should be some solution which i might not be knowing. can some one please help me to solve this.

regards,
ganesh