How to reuse the part of SQL code in Oracle

Question

I have 4 tables in my database: t1, t2, t3, t4

And I would like to run 4 insert statements:

insert into t1
with a as (select from another_table1 where condition1)
**XXX**;

insert into t2
with a as (select from another_table2 where condition2)
**XXX**;

insert into t3
with a as (select from another_table3 where condition3)
**XXX**;

insert into t4
with a as (select from another_table4 where condition4)
**XXX**;

where XXX is a large piece of SQL code (with multiple unions of selection from a), but this code is the same in all 4 insert statements

Is there a way to reuse XXX in the insert statements and not to write it 4 times? (if I need to change something I don't want to change it in 4 different places of my SQL code)

[EDIT #1]

Text of XXX is the kind of:

select col1, sum(col10) from a group by col1 union all
select col2, sum(col11) from a group by col2 union all
select col3, sum(col12) from a group by col3 union all
...

a in every insert statement is different because of different with statement, but the text of XXX is the same

are there any foreign keys between the another_tablex tables? — Boneist
– Boneist, Commented Feb 4, 2020 at 12:45

Boneist · Accepted Answer · 2020-02-04 12:57:59Z

If there are no foreign keys between the tables you're inserting into, you can do this in a single insert all statement. (An insert all statement may fail due to foreign key constraint violations, as it doesn't guarantee the order of the tables it will insert into.)

First off, you can write your select statements into a single query such as:

WITH t1 AS (SELECT 'a' col1, 'b' col2, 'c' col3, 10 col10, 11 col11, 12 col12 FROM dual UNION ALL
            SELECT 'aa' col1, 'bb' col2, 'cc' col3, 20 col10, 21 col11, 22 col12 FROM dual UNION ALL
            SELECT 'aaa' col1, 'b' col2, 'cc' col3, 30 col10, 31 col11, 42 col12 FROM dual),
     t2 AS (SELECT 'a' col1, 'b' col2, 'c' col3, 100 col10, 111 col11, 122 col12 FROM dual UNION ALL
            SELECT 'a' col1, 'bb' col2, 'cc' col3, 200 col10, 211 col11, 222 col12 FROM dual UNION ALL
            SELECT 'a' col1, 'b' col2, 'cc' col3, 300 col10, 311 col11, 422 col12 FROM dual),
      a AS (SELECT 't1' table_name, col1, col2, col3, col10, col11, col12 FROM t1
            UNION ALL
            SELECT 't2' table_name, col1, col2, col3, col10, col11, col12 FROM t2),
  dummy AS (SELECT LEVEL id
            FROM   dual
            CONNECT BY LEVEL <= 3)
SELECT table_name,
       CASE WHEN d.id = 1 THEN 'col1'
            WHEN d.id = 2 THEN 'col2'
            WHEN d.id = 3 THEN 'col3'
       END main_col,
       CASE WHEN d.id = 1 THEN col1
            WHEN d.id = 2 THEN col2
            WHEN d.id = 3 THEN col3
       END main_col_vals,
       SUM(CASE WHEN d.id = 1 THEN col10
                WHEN d.id = 2 THEN col11
                WHEN d.id = 3 THEN col12
           END) sum_vals
FROM   a
       CROSS JOIN dummy d
GROUP BY table_name,
         CASE WHEN d.id = 1 THEN 'col1'
              WHEN d.id = 2 THEN 'col2'
              WHEN d.id = 3 THEN 'col3'
         END,
         CASE WHEN d.id = 1 THEN col1
              WHEN d.id = 2 THEN col2
              WHEN d.id = 3 THEN col3
         END
ORDER BY table_name,
         main_col,
         main_col_vals;

This uses the old-style method of pivoting to pivot all the values from all the tables in one go, labeling the rows with the appropriate table_name.

Then it's just a matter of adding that into an insert all statement, e.g.:

insert all
  when table_name = 't1' then into
    another_t1 (cola, val) values (main_col_vals, sum_vals)
  when table_name = 't2' then into
    another_t2 (cola, val) values (main_col_vals, sum_vals)
WITH a AS (SELECT 't1' table_name, col1, col2, col3, col10, col11, col12 FROM t1
           UNION ALL
           SELECT 't2' table_name, col1, col2, col3, col10, col11, col12 FROM t2),
 dummy AS (SELECT LEVEL id
           FROM   dual
           CONNECT BY LEVEL <= 3)
SELECT table_name,
       CASE WHEN d.id = 1 THEN 'col1'
            WHEN d.id = 2 THEN 'col2'
            WHEN d.id = 3 THEN 'col3'
       END main_col,
       CASE WHEN d.id = 1 THEN col1
            WHEN d.id = 2 THEN col2
            WHEN d.id = 3 THEN col3
       END main_col_vals,
       SUM(CASE WHEN d.id = 1 THEN col10
                WHEN d.id = 2 THEN col11
                WHEN d.id = 3 THEN col12
           END) sum_vals
FROM   a
       CROSS JOIN dummy d
GROUP BY table_name,
         CASE WHEN d.id = 1 THEN 'col1'
              WHEN d.id = 2 THEN 'col2'
              WHEN d.id = 3 THEN 'col3'
         END,
         CASE WHEN d.id = 1 THEN col1
              WHEN d.id = 2 THEN col2
              WHEN d.id = 3 THEN col3
         END;

Here's the test case.

Thanks a lot! It is exactly what I need. Sorry for the late answer :-)

Littlefoot · Accepted Answer · 2020-02-04 09:43:36Z

0

Using a view?

create or replace view xxx as
  select whatever 
  from blabla;

insert into t1
with a as (select ... from another_table1 where condition1)
select * from a
join xxx on ...

answered Feb 4, 2020 at 9:43

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

4 Comments

Room'on Over a year ago

please, see my EDIT#1

Littlefoot Over a year ago

OK. So use a view, as I said. Info you posted additionally doesn't change that.

Room'on Over a year ago

I can't use create or replace view xxx as select whatever from a; because there no a table and I don't need join xxx on, I need to reuse select * from a in your case

Littlefoot Over a year ago

It seems that XXX is not the same, after all, as it uses different a in every of those statements. If you can't uniform it, then you're mostly out of luck.

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-02-04 09:45:18Z

0

if your consideration is not executing it 4 times then you can create temporary table for it. use it and then drop it.

create global temporary table temp_table_transaction on commit preserve rows
as 
<your query goes here>

but otherwise (if you want to reuse it, you can create views). Views executes SQLs on time.

answered Feb 4, 2020 at 9:45

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

1 Comment

Room'on Over a year ago

please, see my EDIT#1

Collectives™ on Stack Overflow

How to reuse the part of SQL code in Oracle

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related