E-QR: PS Query Adds Automatically A Column "TO_CHAR(sysdate,'YYYY-MM-DD')" When Using A Translated Value of Short Or Long Description (Doc ID 1925462.1)

Last updated on JANUARY 03, 2023

Applies to:

Symptoms

When attempting to create a Query with XLAT fields and add the short/long description the PS Query adds an extra expression "TO_CHAR(SYSDATE,'YYYY-MM-DD') " to the Query SQL

Steps
-------
The issue can be reproduced at will with the following steps:

1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Build the query, for example on record WCS_ECM_NOD_STS which has XLAT fields
4. Check fields BUDGET_ID, TREE_NODE, DESCR, WCS_NODE_STATUS to report on

The WCS_NODE_STATUS field has translated values
5. Apply on field WCS_NODE_STATUS to display the Short/Long XLAT-value
6. Add Criteria TREE_LEVEL_NUM <> 1
7. Go to View SQL tabular and view the created SQL
8. Confirm that the  column "to_char(SYSDATE,'YYYY-MM-DD')" is on the SELECT clause

SELECT A.BUDGET_ID, A.TREE_NODE, A.DESCR, A.WCS_NODE_STATUS, TO_CHAR(SYSDATE,'YYYY-MM-DD')
 FROM PS_WCS_ECM_NOD_STS A
 WHERE ( A.TREE_LEVEL_NUM <> 1 )

9. Run the query successfully

When you change on field WCS_NOD_STATUS to display None from XLAT the extra column is not shown.

Cause

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