Support Article
Report Definition generates inefficient SQL for Optimized props
SA-19749
Summary
After using Wizard to update Properties via "Optimize for Reporting" ,Report Definition creates SQL that is unique; if the optimized property name is too long.
This results in a unique property name in the SQL, for example:
SELECT MYPROP_1 AS "P14539008523763L"
This causes the DB engine to parse sql as a new statement each time the report is executed.
This problem also affects reporting in Autonomic Event Services (AES) and Predictive Diagnostic Cloud (PDC) - since each alert has a unique sql statement so the combined effect is not caught.
Error Messages
Not Applicable.
Steps to Reproduce
1) Create a work object with embedded pages with multiple levels of nesting, and then use "Optimize for Reporting" on a deeply embedded property where the length of "embeddedPage.level2.level3.myProperty" exceeds 30 characters.
2) Create a Report Definition which attempts to display this column (using same syntax embeddedPage.level2.level3.myProperty
Root Cause
An inefficiency in Pegasystems’ code would produce a unique column alias for each execution of the Report Definition. This has been corrected via hotfix.
Resolution
Apply HFix-25989.
Published February 18, 2016 - 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.