E-QR: Query With UNION And Drilling URL Returns Duplicate Rows (Doc ID 2344924.1)

Last updated on JULY 27, 2022

Applies to:

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.

Changes

Cause

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