CODEWITHSUNDEEP
sqloracle-database
Mayur Sharma
Mayur Sharma

Reputation: 65

Concatenate result of two select statements

My query is something like: 

concatenate( 
 (select col1 from table1) , ( select col1.substr(1,<tillENd>) ) 
)

This works fine if both sub-selects return one row and one column. In my case, both will give multiple rows but one column.

I want to concatenate them row-wise. How can I do this?

Update: complete queries are something like the following.

Query 1:

select col3 
from tabl2 
where col2 in (select substr(A,1,instr(A,'_',-1)-1) 
               from ( select substr(col1,1,instr(col1,'/')-1) as A 
                      from ( select col1 from tabl1 ) 

               ) 
           )

seond select query: 

select substr(col1,instr(col1,'/')) as A1 
from ( select col1 
from tabl1 )

Upvotes: 1

Views: 22187

Answers (3)

APC
APC

Reputation: 146199

Now that you have provided some sample queries it is easier for us to offer a solution.

Regarding your additional requirement, I assume you don't want to match the void strings, so the easiest thing is to filter them out in the sub-query. 

with data as ( select substr(col1,1,instr(col1,'/')-1) as A
               , substr(col1,instr(col1,'/')) as A1 
            from tabl1
           where instr(col1,'/') > 0 )
select tabl2.col3
       , data.A1
from data
      join tabl2
     on tabl2.col2 = substr(data.A,1,instr(data.A,'_',-1)-1);

Upvotes: 1

vptn
vptn

Reputation: 116

Try LISTAGG function. Also see COLLECT function.

Then concatenate results.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

select ...
from   ...
union all
select ...
from   ...

Or if you use UNION instead of UNION ALL the complete result set is subject to a DISTINCT operation.

Upvotes: 9

Related Questions