sql - Merge two almost identical UNIONed queries into one -


i have multiple queries nested union alls; of inner queries same.

for example

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

result:

amnt|name|cfg ----+----+--- 12  |txt1|  z -12 |tst2|  z 

since inner queries not small , go lot of tables i'm trying save processing time , resources combining these 2 inner queries one. take in consideration name (txt1/txt2) on inner query , not in table

for particular example, need duplicate results returned, conditional logic. if put conditional logic cte perform cartesian join against main table every row in main table duplicated number of records in join. in case 2.

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

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -