Invalid UNION statement in generated Subset Scripts (Oracle)

book

Article ID: 129132

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Performing a Subset (Unix DataPump scripts). Certain .par files generate SQL that contain Select queries joined with a UNION. These particular files, when attempting to run an expdp (DataPump Export), error out with an Oracle error:
ORA-01789: query block has incorrect number of result columns

Manual inspection shows that one select looks for a single attribute - ROWID or ID, and the other Select stmt looks for "*" (All columns). The UNION statement will fail as there is a mis-match between the number of attributes on each side of the UNION. (1 versus many).

Cause

Driving table SQL has INNER JOIN statements in it.

Environment

TDM 4.7

Resolution

After modifying the SUBSET query, replacing INNER JOINs with SELECTs: Initial script generation has produced .par files with UNIONS that select ROWID on either side of the UNION. 

Additional Information

Video URL on how to open a Support Case - https://communities.ca.com/videos/5898-demo-how-to-open-a-support-case
You can download the latest version of TDM by following the directions in this document: https://comm.support.ca.com/kb/How-to-Download-Test-Data-Manager/KB000009671.    
To contact support, go to https://www.ca.com/us/services-support/ca-support/contact-support.html