E-QR: Missing Rows From PS Query Output When EFFDT Field Has No Values (Doc ID 2737126.1)

Last updated on JUNE 20, 2022

Applies to:

Symptoms

When running PS Queries, there are inconsistencies in the data output retrieved. The PS Query is pulling out wrong results, there are missing rows only for the queries using
translate values based on blank/Null date in the Date(e.g. EFFDT, TREE_EFFDT etc) fields set on Field Criteria.

The same issue cannot be reproduced when using Current Date criteria with translate values based on blank/Null date.

The issue can be reproduced at will with the following steps:
1. Create a new PS Query based on the record SEC_RULE_FVALS
Note: The record SEC_RULE_FVALS is required to be added to the Query Security before creating the query (Navigation PeopleTools > Security > Query Security
> Query Access Manager - add the record to the QUERY_TREE_PT)
2. Select All fields
3. Added translate values (Short) to the field A.SEC_RULE_CRITERIA - Field
Criteria
   3.1. Heading: Short
   3.2. Aggregate: None
   3.3. Translate Value: Short
   3.4. Effective Date for Short/Long: enable radio button for Field and select TREE_EFFDT
4. Then Save the changes applied
5. View SQL:
Query SQL
SELECT A.SEC_RULE, A.FIELDNAME, A.SETID, A.BUSINESS_UNIT, A.TREE_NAME,
A.TREE_NODE, A.SELECT_VALUE, A.TO_VALUE, A.SEQNBR, A.SEC_RULE_CRITERIA,
TO_CHAR(A.TREE_EFFDT,'YYYY-MM-DD'), A.TREE_LEVEL,
TO_CHAR(A.TREE_EFFDT,'YYYY-MM-DD')
  FROM PS_SEC_RULE_FVALS A
  ORDER BY 1
6. Run the Query
7. There are missing rows when the TREE_EFFDT field values are Null:
8. Download the results to Excel - same issue, no rows are displayed if there are Null values in the field TREE_EFFDT
Note:
If there are existing data saved in the record SEC_RULE_FVALS  with NOT NULL TREE_EFFDT values, only those rows will be pulled out by the PS Query and missing all the rows that contains Blank/Null TREE_EFFDT values.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.