E-QR: Join To Tree Selector Performance Option Causes PS Query To Create Bad SQL
Last updated on DECEMBER 01, 2017
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.52 and later
Information in this document applies to any platform.
*** Checked for relevance on 17-Apr-2016 ***
*** Checked for relevance on 01-Dec2017 ***
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.
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.'
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms