Query works in sql developer but not in BIRT

I'm using Maximo 7.5 and BIRT v3.7.1. I'm developing a report for the INCIDENT application. When I run below query I get the result in SQL Developer but the same doesn't work in BIRT. 
 
select description from ((SELECT SYS_CONNECT_BY_PATH(classificationid, '\') path,classstructureid,description FROM classstructure START WITH classificationid = 'CIROOT' CONNECT BY PRIOR classstructureid = parent) t1 inner join(select c.classstructureid,to_char(reportdate,'MM') months,count(c.classstructureid) cnt,(sum((coalesce((actualfinish-reportdate)*24,0))))/coalesce(count(c.classstructureid),0) MTR from incident i,ci c where i.status in ('CLOSED','RESOLVED') and i.internalpriority in (1,2) and (i.failurecode='DATABASE' or i.failurecode='SERVICE') and extract(year from i.reportdate)= '2014' and c.cinum = i.cinum group by c.classstructureid,to_char(reportdate,'MM'))t2 on t1.classstructureid=t2.classstructureid)
 
Any help is much appreciated.

Find more posts tagged with

Comments

wwilliams

Have you tried displaying your sqlText in the header of the report?

jverly01

If you add debugging options on the report header
 
mxReportScriptContext.setDefaultLogLevel("DEBUG"); 
mxReportScriptContext.setDefaultLogFile("c:/temp/report_error.log");
 
What errors do you see when you try to preview the report?

wwilliams

Did you remark out? + " where " + params["where"].
I just am wondering if this is the last line in your open method. If so your sql will have an additional where. where 1=1 after your group by...
<pre class="_prettyXprint">
group by c.classstructureid,to_char(reportdate,'MM'))t2 on t1.classstructureid=t2.classstructureid)
WHERE 1=1
</pre>

jverly01

Good catch.