Home » SQL & PL/SQL » SQL & PL/SQL » Multiple inline query (Oracle 11g)
Multiple inline query [message #656162] Mon, 26 September 2016 06:39 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hi All,

Input:
with a as 
(select 1 a1, 1 a2 from dual
union 
select 2 a1, 2 a2 from dual
union 
select 3 a1, 3 a2 from dual),
 b as 
(select 1 b1, 1 b2 from dual
union 
select 2 b1, 2 b2 from dual
union 
select 4 b1, 4 b2 from dual)
select * from a,b

Output like:

col1 col2 
1      1
2      2
3      3
4      4

Can anyone please help me out ?

Thanks in advance.
Re: Multiple inline query [message #656163 is a reply to message #656162] Mon, 26 September 2016 06:51 Go to previous messageGo to next message
ALEXWE
Messages: 11
Registered: August 2016
Location: Germany
Junior Member
docs.oracle.com
"The following statement combines the results of two queries with the UNION operator, which eliminates duplicate selected rows."

with a as 
(select 1 a1, 1 a2 from dual
union 
select 2 a1, 2 a2 from dual
union 
select 3 a1, 3 a2 from dual),
 b as 
(select 1 b1, 1 b2 from dual
union 
select 2 b1, 2 b2 from dual
union 
select 4 b1, 4 b2 from dual)
select * from a union
select * from b;
Re: Multiple inline query [message #656164 is a reply to message #656163] Mon, 26 September 2016 07:02 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Apart from "union", is there any other we can do for the same output ?
Re: Multiple inline query [message #656165 is a reply to message #656164] Mon, 26 September 2016 07:09 Go to previous messageGo to next message
ALEXWE
Messages: 11
Registered: August 2016
Location: Germany
Junior Member
Why don`t use union?

with a as 
(select 1 a1, 1 a2 from dual
union 
select 2 a1, 2 a2 from dual
union 
select 3 a1, 3 a2 from dual),
 b as 
(select 1 b1, 1 b2 from dual
union 
select 2 b1, 2 b2 from dual
union 
select 4 b1, 4 b2 from dual)
select nvl(a1,b1) col1,nvl(a2,b2) col2
from a 
full outer join b
on b.b1 = a.a1
and b.b2 = a.a2
order by col1,col2;

      COL1       COL2
---------- ----------
         1          1 
         2          2 
         3          3 
         4          4 

Re: Multiple inline query [message #656166 is a reply to message #656162] Mon, 26 September 2016 07:46 Go to previous messageGo to next message
joy_division
Messages: 4899
Registered: February 2005
Location: East Coast USA
Senior Member
Being that the output is not produced by the input, I do not understand the question.
Re: Multiple inline query [message #656167 is a reply to message #656162] Mon, 26 September 2016 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Output like:
No output is:
SQL> with a as
  2  (select 1 a1, 1 a2 from dual
  3  union
  4  select 2 a1, 2 a2 from dual
  5  union
  6  select 3 a1, 3 a2 from dual),
  7   b as
  8  (select 1 b1, 1 b2 from dual
  9  union
 10  select 2 b1, 2 b2 from dual
 11  union
 12  select 4 b1, 4 b2 from dual)
 13  select * from a,b
 14  /
        A1         A2         B1         B2
---------- ---------- ---------- ----------
         1          1          1          1
         1          1          2          2
         1          1          4          4
         2          2          1          1
         2          2          2          2
         2          2          4          4
         3          3          1          1
         3          3          2          2
         3          3          4          4

9 rows selected.

What is the question?

[Updated on: Mon, 26 September 2016 07:55]

Report message to a moderator

Re: Multiple inline query [message #656168 is a reply to message #656167] Mon, 26 September 2016 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And feedback to your previous topics.

Re: Multiple inline query [message #656169 is a reply to message #656168] Mon, 26 September 2016 09:12 Go to previous messageGo to next message
Bill B
Messages: 1799
Registered: December 2004
Senior Member
You never told us what you want to do. Give more detail.
Re: Multiple inline query [message #656201 is a reply to message #656169] Wed, 28 September 2016 06:13 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks ALEXWE. I'm using outer join.

@Michel/Bill/joy: Based on input query I'm trying to generate output as I mentioned in my post.
please accept my apologies for incomplete details.


Thanks
Re: Multiple inline query [message #656217 is a reply to message #656201] Wed, 28 September 2016 09:49 Go to previous message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Generating output does not mean having a correct query.

Previous Topic: Distinct Comma Separated Values
Next Topic: HTTPS Webservices Issue though PL/SQL
Goto Forum:
  


Current Time: Mon Oct 15 20:12:33 CDT 2018