Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Could both these queries be written using UNION ALL in a better way?

AshK-OracleMay 6 2022 — edited May 6 2022

  1. sql_stmt := 'CREATE TABLE ' || inbound_rvsl_tbl
    || ' AS SELECT transaction_number'
    || ' , ''D'' as status'
    || ' FROM (SELECT xtr.transaction_number
    FROM
    xla_transaction_headers xth,
    xla_transaction_reversals_int xtr
    WHERE
    xth.transaction_number = xtr.transaction_number
    AND xth.transaction_reversal_flag = 'Y')';

  2. sql_stmt := 'INSERT INTO '
    || inbound_rvsl_tbl
    || ' SELECT transaction_number'
    || ' , ''I'' as status'
    || ' FROM (SELECT xtr.transaction_number
    FROM
    xla_transaction_headers xth,
    xla_transaction_reversals_int xtr
    WHERE
    xth.transaction_number = xtr.transaction_number
    AND xth.transaction_date > xtr.REVERSAL_DATE)';

My Solution :
sql_stmt := 'CREATE TABLE ' || inbound_rvsl_tbl
|| ' AS SELECT transaction_number'
|| ' , ''D'' as status'
|| ' FROM (SELECT xtr.transaction_number
FROM
xla_transaction_headers xth,
xla_transaction_reversals_int xtr
WHERE
xth.transaction_number = xtr.transaction_number
AND xth.transaction_reversal_flag = ''Y'')
UNION ALL
SELECT transaction_number'
|| ' , ''I'' as status'
|| ' FROM (SELECT xtr.transaction_number
FROM
xla_transaction_headers xth,
xla_transaction_reversals_int xtr
WHERE
xth.transaction_number = xtr.transaction_number
AND xth.transaction_date > xtr.REVERSAL_DATE)';

Comments

mwrf
resolved, just being stupid.
1 - 1

Post Details

Added on May 6 2022
#plsql
3 comments
67 views
1 mention