Query works in sql developer but not in BIRT

maxuser · September 1, 2014

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.

wwilliams · September 2, 2014

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

jverly01 · September 2, 2014

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 · September 3, 2014

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 · September 3, 2014

Good catch.

Query works in sql developer but not in BIRT

Comments

Categories