Support Article
Where clause values setting wrongly for report definition query
SA-36693
Summary
User scheduled the Open RMAEMEARV2 report in production environment and tested through ExecuteAgentActivityManually activity.
As per their analysis in ExecuteLargeReports activity step 15 dynamically generated SQL passing the input parameters wrongly that’s why the Open RMAEMEARv2 report generated with no records.
In that query where clause setting the input parameter values wrongly CustGroup"."PXOBJCLASS" = 'EMEAR' ) WHERE ( "PC0"."THEATER" = *-FW-RMAFW-Data-CustGrouping )
Error Messages
The upgraded version, the index of pyPreparedValues returns the wrong value.
The pyPreapredValue in the SQL comes like this :
"PC0"."LAST_ACTIVITYCODE_UPDATE_DATE" AS "LAST_ACTIVITYCODE_UPDATE_DATE" FROM {CLASS: *-FW-RMAFW-Data-ORRView } "PC0" LEFT OUTER JOIN {CLASS: *-FW-RMAFW-Data-CustGrouping } "CustGroup" ON ( ( "CustGroup"."BILL_TO_LOCATION" = "PC0"."BILL_TO_LOCATION" ) AND "CustGroup"."PXOBJCLASS" = {ReportContentPage.pyPreparedValues(2) }) WHERE ( "PC0"."THEATER" = {ReportContentPage.pyPreparedValues(1) } )
At the same time, in clipboard the pyPreparedValues return the wrong values against the index.
Steps to Reproduce
1) Schedule the Heavy reports.
2) Manually run the activity ExecuteAgentActivityManually.and pass the PZINSKEY for the report.
3) Enable the tracer. ExecuteLargeReports activity get executed and report gets generated with no records.
Root Cause
The order of the pyPreparedValue items is generated and managed by the SQL generation layer and they are used for internal purposes.
The processing order has been changed in the release Pega 7.2.1 and there is no guarantee that this order will remain the same for future releases.
Ideally, user must not rely on the pyPreparedValue order.
Resolution
Here is the explanation:
The order of the pyPreparedValue items is generated and managed by the SQL generation layer and they are used for internal purposes.
The processing order has been changed in the release Pega 7.2.1 and there is no guarantee that this order will remain the same for future releases.
Ideally, user must not rely on the pyPreparedValue order.
In case the user wants to know the order, the Report definition can be run in query only mode.
And the query generated with the question marks replaced with the placeholders can be viewed by using the pxRetrieveReportData activity.
It is expected behavior.
As the user is using a custom activity and using pyPreparedValues to form query manually and to generate report, the logic of the order of preparedValues must also be handled by the user.
As in the custom activity, step 12, - Get pyPreparedValuesOrder, user need to correct his logic, since it seems to be a custom Activity.
Published May 4, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.