My Oracle Support Banner

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

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


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