Can't use UNION statement in the BIRT

rbai

Hi,

I have a report runs fine in BIRT designer but it doesn't run in Maximo. When I run the report from Maximo, I can see report's title and column heading and no records.

I found the problem is I used UNION statement in the report. If I comment out the UNION statement, the report runs fine in Maximo. For some reason, when BIRT pass the whole SQL statement to Maximo, Maximo doesn't know how to handle it and it didn't pass the statement to Oracle. I checked the Oracle sql log. The statement was not there.

Does anyone know what the problem is?

Here is my SQL statement.

if (BirtComp.notEqual(params["startdate"], null)) {
strTemp1 = " and trunc(changedate) >= " + MXReportSqlFormat.getDateFunction(params["startdate"]);
strTemp2 = " and trunc(asset.installdate) >= " + MXReportSqlFormat.getDateFunction(params["startdate"]);
}
else {
strTemp1 = " and trunc(changedate) >= '01-JAN-1900'";
strTemp2 = " and trunc(asset.installdate) >= '01-JAN-1900'";
}

if (BirtComp.notEqual(params["enddate"], null)){
strTemp1 = strTemp1 + " and trunc(changedate) <= " + MXReportSqlFormat.getDateFunction(params["enddate"]);
strTemp2 = strTemp2 + " and trunc(asset.installdate) <= " + MXReportSqlFormat.getDateFunction(params["enddate"]);
}
else {
strTemp1 = strTemp1 + " and trunc(changedate) <= " + MXReportSqlFormat.getDateFunction(new Date());
strTemp2 = strTemp2 + " and trunc(asset.installdate) <= " + MXReportSqlFormat.getDateFunction(new Date());
}

// Add query to sqlText variable.
sqlText = "(select asset.eq3, asset.assettag, asset.description eqdesc, asset.assetnum,"
+ " asset.statusdate, workorder.wonum, workorder.reportdate,"
+ " workorder.description wodesc, workorder.persongroup, asset.siteid,"
+ " ( select alnvalue"
+ " from assetspec"
+ " where assetspec.assetnum=asset.assetnum"
+ " and assetspec.assetattrid='QUAD' ) as quad,"
+ " dcwasa_assetstatus.changedate"
+ " from workorder, asset,"
+ " (select assetnum, wonum, siteid, changedate from assetstatus a"
+ " where changedate = (select max(changedate)"
+ " from assetstatus"
+ " where a.assetnum = assetnum"
+ " and a.isrunning = 1"
+ " and a.siteid = siteid"
+ strTemp1
+ " )) dcwasa_assetstatus"
+ " where asset.assetnum = dcwasa_assetstatus.assetnum"
+ " and asset.siteid = dcwasa_assetstatus.siteid"
+ " and dcwasa_assetstatus.wonum = workorder.wonum"
+ " and dcwasa_assetstatus.assetnum = workorder.assetnum"
+ " and dcwasa_assetstatus.siteid = workorder.siteid"
+ " and asset.disabled <> 1"
+ " and asset.isrunning = 1"
+ " and asset.failurecode = 'HYDRANTS'"
+ " and (asset.location <> 'DISPOSED' or asset.location is null)"
+ " and (( select alnvalue"
+ " from maximo.assetspec"
+ " where assetspec.assetnum=asset.assetnum"
+ " and assetspec.assetattrid='OWNER' ) ='WASA' or"
+ " ( select alnvalue"
+ " from maximo.assetspec"
+ " where assetspec.assetnum=asset.assetnum"
+ " and assetspec.assetattrid='OWNER' ) is null)"
+ " and asset.siteid = 'DWS_DSS'"
+ " and " + params["where"]
+ ") union"
+ " (select asset.eq3, asset.assettag, asset.description, asset.assetnum, asset.statusdate, null, null, null,"
+ " null, asset.siteid,"
+ " ( select alnvalue"
+ " from maximo.assetspec"
+ " where assetspec.assetnum = asset.assetnum"
+ " and assetspec.siteid = asset.siteid"
+ " and assetspec.assetattrid='QUAD' ) as quad,"
+ " asset.installdate as cdate"
+ " from maximo.asset,"
+ " (select assettag, siteid"
+ " from maximo.asset"
+ " where failurecode = 'HYDRANTS'"
+ " group by assettag, siteid"
+ " having count(*) > 1) temp"
+ " where asset.assettag = temp.assettag"
+ " and asset.siteid=temp.siteid"
+ " and asset.disabled <> 1"
+ " and asset.isrunning = 1"
+ " and (asset.location <> 'DISPOSED' or asset.location is null)"
+ " and (( select alnvalue"
+ " from maximo.assetspec"
+ " where assetspec.assetnum=asset.assetnum"
+ " and assetspec.assetattrid='OWNER' ) ='WASA' or"
+ " ( select alnvalue"
+ " from maximo.assetspec"
+ " where assetspec.assetnum=asset.assetnum"
+ " and assetspec.assetattrid='OWNER' ) is null)"
+ strTemp2
+ " and asset.siteid = 'DWS_DSS'"
+ " and " + params["where"]
+ ") order by quad, eq3"
;

Thanks

Ruth