Invalid UNION statement in generated Subset Scripts (Oracle)
Article ID: 129132
CA Test Data Manager (Data Finder / Grid Tools)
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).
Driving table SQL has INNER JOIN statements in it.
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.