CODEWITHSUNDEEP
oracle-databaseplsql
john
john

Reputation: 1

PL SQL concatenate 2 resultsets

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

Upvotes: 0

Views: 2613

Answers (6)

KamiMark
KamiMark

Reputation:

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 :)

Upvotes: 0

john
john

Reputation: 1

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.

Upvotes: 0

Pop
Pop

Reputation: 4022

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.

Upvotes: 0

Rob van Wijk
Rob van Wijk

Reputation: 17705

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

Regards, Rob.

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

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.

Upvotes: 1

moleboy
moleboy

Reputation: 874

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.

Upvotes: 0

Related Questions