CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle10g
ziggy
ziggy

Reputation: 15876

Oracle SQL - Alternative to the UNION ALL statement

Given the follownig query that uses a union,

Select col1,col2,col3,col4,col5,cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)) temp, col6
from tableA
union all
Select a,b,c,d,e,cast(multiset(temp.col1 from table(cast(tableB.children as clob_nt)) temp, col7
from tableB

i would expect to get the following output (i.e. rows from both tables)

tableA.x tableA.x tableA.x tableA.x tableA.x tableA.clob(x) tableA.x
tableA.x tableA.x tableA.x tableA.x tableA.x tableA.clob(x) tableA.x
tableB.x tableA.x tableA.x tableB.x tableB.x tableB.clob(x) tableB.x
tableB.x tableA.x tableA.x tableB.x tableB.x tableB.clob(x) tableB.x
tableB.x tableA.x tableA.x tableB.x tableB.x tableB.clob(x) tableB.x

What other options are available that will allow me to run the query without using 'union' or 'union all' and get the same results

Upvotes: 0

Views: 5378

Answers (2)

deroby
deroby

Reputation: 6002

You could store the results of every query in a (temporary) result table and then in the end fetch all the results at once.

The only reason I can think of to do this is to split the work into smaller chunks either to conserve resources (not sure it will make that much of a difference though) or to benchmark the different queries separately.

Below how this would look more or less in Oracle. (I'm more of a MSSQL man)

CREATE GLOBAL TEMPORARY TABLE MyWorkingTable
   ON COMMIT PRESERVE ROWS 
   AS Select col1,col2,col3,col4,col5,cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)) temp, col6
      from tableA
     WHERE 1 = 2

INSERT MyWorkingTable (col1,col2,col3,col4,col5, temp, col6)
Select col1,col2,col3,col4,col5,cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)) temp, col6
      from tableA;


INSERT MyWorkingTable (col1,col2,col3,col4,col5, temp, col6)
Select col1,col2,col3,col4,col5,cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)) temp, col6
      from tableB;

INSERT MyWorkingTable (col1,col2,col3,col4,col5, temp, col6)
Select col1,col2,col3,col4,col5,cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)) temp, col6
      from tableC;


-- etc

SELECT * FROM MyWorkingTable;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You actually can do this in Oracle, but the coding is a bit messy. The idea is to do a full outer join on a non-matching field, and then use coalesce() to bring the results together:

select coalesce(a.col1, b.a) as col1,
       coalesce(a.col2, b.b) as col2,
       coalesce(a.col3, b.c) as col3,
       coalesce(a.col4, b.d) as col4,
       coalesce(a.col5, b.e) as col5,
       coalesce(cast(multiset(temp.col1 from table(cast(xslt.children as clob_nt)),
                cast(multiset(temp.col1 from table(cast(b.children as clob_nt))
               ) as temp,
       coalesce(a.col6, b.col7) as col6
from tableA a full outer join
     tableB b
     on 0 = 1;

However, I'm not sure if the preceding will work on the temp column. One reason is that the cast() doesn't seem to be fully formulated:

Upvotes: 1

Related Questions