Forum Stats

  • 3,827,706 Users
  • 2,260,808 Discussions
  • 7,897,355 Comments

Discussions

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

User_5U12Z
User_5U12Z Posts: 8 Employee
edited May 6, 2022 9:33AM in SQL & PL/SQL

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

Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    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
    ora_1978 Member Posts: 522 Bronze Badge

    Your solution is correct.

  • alvinder
    alvinder Member Posts: 435 Silver Badge

    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