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

User_5U12Z

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)';

Frank Kulash

Hi, @User_5U12Z

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. It helps to explain what is unique and what can be NULL in the tables. Also post the exact results you want from that data, and explain why you want those results from that data. For example, "I want to create a table from the result set of a join, but sometimes I'll want two rows inserted into the new table for each row in the join. When ... then I want to insert a row with status = 'D', and when ... then I want to insert a row with status = 'I'. It's important to state your requiremements. Don't just post some code and say "I want to do the same as this"; the code you post may not do what you want. for example, it may have errors like:

 AND xth.transaction_reversal_flag = 'Y')';

Always post your complete Oracle version (e.g. 18.4.0.0.0).

There are several other ways to do what you want (such as using a join instead of UNION), but they may be more complicated and/or less efficient than your idea. I don't see anything obviously wrong with your idea. If it does what you want, use it. However, always think carefully before using dynamic SQL; it's often a symptom of a bad design. Why do you need to create tables dynamically? Are you planning to have several tables all with the same columns? If so, why?

ora_1978

Your solution is correct.

alvinder

Please post some sample data.

Something along the lines.

    SELECT
      xtr.transaction_number , Case when xth.transaction_date > xtr.reversal_date then 'D' 
                     when nvl(xth.transaction_reversal_flag,'N') = 'Y' then 'I' else Null end Status
       
    FROM
      xla_transaction_headers     xth,
      xla_transaction_reversals_int  xtr
    WHERE
      xth.transaction_number = xtr.transaction_number
      AND ( xth.transaction_date > xtr.reversal_date OR xth.transaction_reversal_flag = 'Y')

Provided if xth.transaction_date < = xtr.reversal_date and xth.transaction_reversal_flag= 'N' or null