I have a report that fails in our Oracle on-Premise environment. I turned on debugging and found the query that fails has a WHERE condition that passes in a "FALSE = 1". This fails in Oracle.
I have Jaspersoft Studio and I cannot find this query in the report. Is there a hidden tab or some super secret Jaspersoft hideaway for queries like this one?
SELECT r.resource_key AS resource_key,
r.resource_id AS resource_id,
r.resource_name AS resource_name
FROM dwh_res_resource r
WHERE ? = 1
AND r.is_role = 0
AND r.resource_type_key = 0
AND r.resource_manager_key IN (?)
AND r.resource_key IN (SELECT DISTINCT obsm.resource_key
FROM dwh_res_obs_mapping obsm
INNER JOIN dwh_cmn_obs_hierarchy obsh ON obsm.obs_unit_key = obsh.child_obs_unit_key
WHERE obsh.parent_obs_unit_key = PPM_DWH.dwh_null_number_fct(?,0))
AND (r.is_active = CASE WHEN ? = 1 THEN r.is_active ELSE 1 END)
AND EXISTS (SELECT 1
WHERE user_uid = ?
AND resource_key = r.resource_key)
ORDER BY r.resource_name
2018-01-31 21:46:47,915 DEBUG JRJdbcQueryExecuter,http-apr-8080-exec-7:509 [cambia|r616330] - Parameter #1 (populateResource of type java.lang.Boolean): false
2018-01-31 21:46:47,916 DEBUG JRJdbcQueryExecuter,http-apr-8080-exec-7:576 [cambia|r616330] - Parameter #2 (resourceManagerKey_1 of type java.math.BigDecimal): 5.00432E+6
2018-01-31 21:46:47,917 DEBUG JRJdbcQueryExecuter,http-apr-8080-exec-7:509 [cambia|r616330] - Parameter #3 (resourceOBSUnitKeyRequired of type java.math.BigDecimal): 5166219
2018-01-31 21:46:47,918 DEBUG JRJdbcQueryExecuter,http-apr-8080-exec-7:509 [cambia|r616330] - Parameter #4 (includeInactiveResources of type java.lang.Boolean): false
2018-01-31 21:46:47,919 DEBUG JRJdbcQueryExecuter,http-apr-8080-exec-7:509 [cambia|r616330] - Parameter #5 (LoggedInUsername of type java.lang.String): r616330
If its out of box report, it shouldn't fail. Have a look at the Jasperserver log and you will see the issue, it could be PMO is not installed or applied. It could also be DWH job is failing or some mismatch in attributes in DWH
FYI, The query noted below comes as result of the Resource Input Control associated with the Capacity vs Allocation by OBS Report
Steve Troy ca technologies
Sr Principal Engineering Svcs Architect
Tel: +1 425 201 3528
Mobile: +1 206 718 7466
This report is an out of the box report. Why would it require the PMO Accelerator? This doesn't seem connected.
I'm trying to find the source of this query and where to edit it.
PMO accelerator is must of all out of box Jaspersoft reports to work properly.
The DWH job runs regularly without errors. The PMO Accelerator does fail to load, but this report is Out of the Box and should not require the PMO Accelerator.
The PMO Accelerator is required for Advanced Reporting content. This can be found in the documentation linked below.
PMO Accelerator Advanced Reporting Content - CA PPM - 15.2 - CA Technologies Documentation