PL SQL concatenate 2 resultsets

Question

I need to get the result of concatenating 2 similar querys' resulsets. For some reason had to split the original query in 2, both with their corresponding order by clause. Should be something like (this is an oversimplification of the original queries)

Query1: Select name, age from person where age=10
Resultset1: 
Person1, 10
Person3, 10

Query2: Select name, age from person where age=20
Resultset1: 
Person2, 20
Person6, 20

The expected result:
Person1, 10
Person3, 10
Person2, 20
Person6, 20

I can not simply use Query1 UNION Query2.

Below the 2 original querys: (#1)

select cp.CP_ID, cpi.CI_DESCRIPCION, cp.CP_CODIGOJERARQUIZADO, cp.CP_ESGASTO as gasto, cp.CP_CONCEPTOPADRE, LEVEL
from TGCCP_ConceptoPagoIng cp
left join tgcci_ConceptoPagoIngIdioma cpi on cpi.CI_IDCONCEPTOPAGOING = cp.CP_ID and cpi.CI_IDIDIOMA = 1
start with ((CP_CONCEPTOPADRE is null) and (**cp.CP_ESGASTO = 1**))
connect by prior cp.CP_ID = cp.CP_CONCEPTOPADRE
order siblings by CP_CODIGOJERARQUIZADO

(#2)

select cp.CP_ID, cpi.CI_DESCRIPCION, cp.CP_CODIGOJERARQUIZADO, cp.CP_ESGASTO as gasto, cp.CP_CONCEPTOPADRE, LEVEL
from TGCCP_ConceptoPagoIng cp
left join tgcci_ConceptoPagoIngIdioma cpi on cpi.CI_IDCONCEPTOPAGOING = cp.CP_ID and cpi.CI_IDIDIOMA = 1
start with ((CP_CONCEPTOPADRE is null) and (**cp.CP_ESGASTO = 2**))
connect by prior cp.CP_ID = cp.CP_CONCEPTOPADRE
order siblings by CP_CODIGOJERARQUIZADO

Answer 1

OK, well, I'm not fully certain why you need it this way, but if Oracle won't allow you to do a UNION, or it screws up the ordering when you do, I would try creating a pipelined table function. An example here

Basically, you'd create a procedure that ran both queries, first one, then the other, putting the results of each into the returned dataset.

Answer 2

I think you want a

select * from ( first query )
UNION ALL
select * from ( second query )

Where first query and second query are the queries from above, so you are turning them into subqueries, thus preserving the order by clauses.

Answer 3

It looks like you are looking for a MULTISET UNION. Which can only be used from version 10 upwards.

Regards, Rob.

Answer 4

You could combine your queries as subqueries and do a single order by on the outer query:

select * from (
<query 1 with its order by> 
UNION ALL
<query 2 with its order by>
)
order by column1, column2;

Alternatively, you can implement in PL/SQL the equivalent of a sort merge join with two cursors, but that's unnecessarily complicated.

Answer 5

Hello, this solution works perfectly:

select * from ( first query ) UNION ALL select * from ( second query )

I appreciate everyone that have taken the time to answer. regards.

Answer 6

For your example:

Select name, age from person where age in (10,20) or Select name, age from person where age = 10 or age = 20

However I'm guessing this is not what you need :)