E-QR: Join To Tree Selector Performance Option Causes PS Query To Create Bad SQL (Doc ID 1637820.1)

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

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