CODEWITHSUNDEEP
sqloracle-databaseperformanceunion
Bruno Reis
Bruno Reis

Reputation: 1

Merge two almost identical UNIONed queries into one

I have multiple queries nested together by UNION ALLs; some of the inner queries are almost the same.

For example

select sum(x.amount) as amnt, 'txt1' as name, x.cfg as cfg from tbl1
union all
select -sum(x.amount) as amnt, 'txt2' as name, x.cfg as cfg from tbl1

result:

AMNT|NAME|CFG
----+----+---
12  |txt1|  Z
-12 |tst2|  Z

Since the inner queries are not that small and go to a lot of tables themselves I'm trying to save processing time and resources by combining these two inner queries into one. Take in consideration that the NAME (txt1/txt2) is on the inner query and not in a table

Upvotes: 0

Views: 86

Answers (1)

Ben
Ben

Reputation: 52893

For this particular example, you need to duplicate the results returned, with some conditional logic. If you put the conditional logic into a CTE then perform a Cartesian join against your main table then every row in the main table will be duplicated by the number of records in the join. In this case that would be 2.

with multiplier (m, name) as (
 select 1, 'txt1' from dual
  union all
 select -1, 'txt2' from dual
        )
select multiplier.m * sum(t.amount), multiplier.name, t.cfg
  from tbl1 t
 cross join multiplier

Upvotes: 1

Related Questions