E-QR: Query With UNION And Drilling URL Returns Duplicate Rows

(Doc ID 2344924.1)

Last updated on JANUARY 03, 2018

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.55 and later
Information in this document applies to any platform.

Symptoms

Query with UNION and drilling URL returns all the rows. It uses UNION ALL and returns duplicate rows.

Steps
-----------
In order to replicate the issue follow the below steps:

1. Login to PIA
2. Navigate to Reporting Tools > Query > Query Manager
3. Create a new Query
   3.1. Add PS_PERSON, SELECT EMPLID COLUMN
   3.2. Limit results to 1 EMPLID
   3.3. Add expression, type = Drilling URL. Build using Component URL link
   3.4. Create UNION with exact same query
   3.5. Save query
   3.6. Click Run tab – two rows are returned
4. From Reporting Tools > Query > Query Manager > View SQL tab copy the SQL of the query
Query SQL example
 ----------------------------
SELECT A.EMPLID, 'psp/EMPLOYEE//c/TRACK_STUDENT_CAREERS.ACAD_PLAN.GBL'
  FROM PS_PERSON A
  WHERE ( A.EMPLID = '0002')
UNION
SELECT B.EMPLID, 'psp/EMPLOYEE//c/TRACK_STUDENT_CAREERS.ACAD_PLAN.GBL'
  FROM PS_PERSON B
  WHERE ( B.EMPLID = '0002')
5. Run the query directly to DB.
6. Only one row will be returned.


Cause

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