Support Article
Report definition not adding condition to where clause
SA-55684
Summary
Report definition omits a condition in the WHERE clause of the generated query.
The Report has the .pxLinkedRefFrom IS EQUAL pyWorkPage.pzInsKey a filter condition. The code for this filter condition is not added to the WHERE clause of the generated SQL query. As a result, the query retrieved 8 million records from the database (over 1GB of data). The query takes ~1,000 seconds to execute.
SELECT DISTINCT "PC0"."PXCREATEDATETIME" AS "pxCreateDateTime" , "PC0"."PYMEMO" AS "pyMemo" , "PC0"."PXCREATEOPERATOR" AS "pxCreateOperator" , "PC0"."PYCATEGORY" AS "pyCategory" , "PC0"."SICORRATTACHMENTSTATUS" AS "SICorrAttachmentStatus" , GIN.pr_read_from_stream('.ETSRespOFACstatus', "PC0".pzInsKey, "PC0".pzPVStream) AS "ETSRespOFACstatus" , "PC0"."PXLINKEDREFTO" AS "pxLinkedRefTo" , "PC0"."PXLINKEDCLASSTO" AS "pxLinkedClassTo" , "PC0"."PZINSKEY" AS "pzInsKey" FROM GIN.pc_link_attachment "PC0" WHERE "PC0"."PXOBJCLASS" = ? ORDER BY 1 DESC
This occurs for cases that are created though File listener and real time users.
Error Messages
Not Applicable
Steps to Reproduce
Create a case from the file feed.
Root Cause
An issue in the custom application code or rules.
In the report definition, the 'Use null if empty' option was disabled on the filter condition. As a result, the value of the pyWorkPage.pzInsKey was empty and the system omitted the filter condition from the WHERE clause.
Resolution
Perform the following local-change:
Check the 'Use null if empty' checkbox on the filter condition.
After selecting this option, if the filter value pyWorkPage.pzInsKey is empty, the system adds an 'IS NULL' condition to the below WHERE clause:
WHERE ( "PC0"."PXLINKEDREFFROM" IS NULL ) AND "PC0"."PXOBJCLASS" = ?
This prevents the query from retrieving the entire table.
Published August 19, 2019 - 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.