Multiple inline query [message #656162] |
Mon, 26 September 2016 06:39 |
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 #656165 is a reply to message #656164] |
Mon, 26 September 2016 07:09 |
|
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 #656167 is a reply to message #656162] |
Mon, 26 September 2016 07:54 |
|
Michel Cadot
Messages: 68665 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 #656201 is a reply to message #656169] |
Wed, 28 September 2016 06:13 |
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
|
|
|
|