E-QR: Join To Tree Selector Performance Option Causes PS Query To Create Bad SQL
(Doc ID 1637820.1)
Last updated on AUGUST 27, 2024
Applies to:
PeopleSoft Enterprise PT PeopleTools - Version 8.52 and laterInformation in this document applies to any platform.
*** ***
Symptoms
When attempting to create a query based on 'In Tree' criteria for a Tree that uses Performance Options 'Join to Tree Selector.' Incorrect SQL is generated.
The correct Query SQL is generated if the Tree uses Performance Options 'Use Application Defaults' instead.
Steps
-------
The issue can be reproduced at will with the following steps:
1. Login to PIA
2. Navigate to Tree Manager > Tree Manager - Confirm tree performance option is set to Application Default.
3. Open REGION_TREE under setid SHARE
4. Review Performance Options – Set Access to 'Join to Tree Selector'
5. Create Query to list business units (SB_TEST_TREE_SELECTOR)
5.1. Add record BUS_UNIT_TBL_FS
5.2. Add criteria as shown here (detailed in following steps):
a. Criteria 1 Detail
Select the JAPAN node of the REGION_TREE under SHARE setid
b. Criteria 2 Detail
Create “In List” criteria with business units IN001 and IN002
Change the second criteria logical to OR
6. Save query so that it can be re-used at a later time
7. SQL Example to build the Query:
SELECT A.BUSINESS_UNIT, A.DESCR
FROM PS_BUS_UNIT_TBL_FS A, PSTREESELECT10 A3_0
WHERE ( A3_0.SELECTOR_NUM=105 AND A.BUSINESS_UNIT>= A3_0.RANGE_FROM_10 AND A.BUSINESS_UNIT <= A3_0.RANGE_TO_10 AND A3_0.TREE_NODE_NUM BETWEEN 125000000 AND 149999999
OR A.BUSINESS_UNIT IN ('IN001','IN002') )
8. Run query – the result set is incorrect with 76 business units - with each business unit repeated several times - instead of 4 rows generated with Access Method set to 'Application Default.'
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |