full outer join or union [message #440243] |
Thu, 21 January 2010 15:30  |
kanaka
Messages: 2 Registered: January 2010
|
Junior Member |
|
|
HI All,
I am working on one requirement.but I am not able to get the right direction to work on.Can you please help me.
I have 2 source tables
table1
emplid effdt name
xxxxx 01/01/2001 abc
xxxxx 01/02/2001 abcd
table2
emplid effdt marital_status
xxxxx 01/01/2001 U
xxxxx 01/04/2001 M
xxxxx 01/06/2001 D
the output would like this- target table
emplid effdt name marital_status
xxxxx 01/01/2001 abc U
xxxxx 01/02/2001 abcd U (most recent)
xxxxx 01/04/2001 abcd M
xxxxx 01/06/2001 abcd D
Can you please give me some ida how to acheive this
Thanks
|
|
|
|
|
|
Re: full outer join or union [message #440285 is a reply to message #440243] |
Fri, 22 January 2010 00:47   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
This might, or might not be one way to do that (depending on how much I've understood the question).
A test case:SQL> create table table1
2 (emplid varchar2(5),
3 effdt date,
4 e_name varchar2(10)
5 );
Table created.
SQL> create table table2
2 (emplid varchar2(5),
3 effdt date,
4 marital_status varchar2(1)
5 );
Table created.
SQL> insert all
2 into table1 values ('xxxxx', to_date('01.01.2001', 'dd.mm.yyyy'), 'abc')
3 into table1 values ('xxxxx', to_date('02.01.2001', 'dd.mm.yyyy'), 'abcd')
4 into table2 values ('xxxxx', to_date('01.01.2001', 'dd.mm.yyyy'), 'U')
5 into table2 values ('xxxxx', to_date('04.01.2001', 'dd.mm.yyyy'), 'M')
6 into table2 values ('xxxxx', to_date('06.01.2001', 'dd.mm.yyyy'), 'D')
7 select * from dual;
5 rows created.
Query:
SQL> select x.emplid,
2 x.effdt,
3 max(a.e_name) e_name,
4 min(b.marital_status) marital_status
5 from (select t1.emplid, t1.effdt
6 from table1 t1
7 union
8 select t2.emplid, t2.effdt
9 from table2 t2
10 ) x,
11 table1 a,
12 table2 b
13 where a.emplid = x.emplid
14 and a.effdt <= x.effdt
15 and b.emplid = x.emplid
16 and b.effdt <= x.effdt
17 group by x.emplid, x.effdt
18 order by 1, 2
19 ;
EMPLI EFFDT E_NAME M
----- ---------- ---------- -
xxxxx 01.01.2001 abc U
xxxxx 02.01.2001 abcd U
xxxxx 04.01.2001 abcd M
xxxxx 06.01.2001 abcd D
SQL>
So, asking about "full outer join OR union": here's a "join AND union" attempt.
Forgot to mention:ramoradbaInsted of putting "xxxx" in your records use valid number(dummy)atleast.
But, 'xxxx' IS a dummy value.
[Updated on: Fri, 22 January 2010 00:49] Report message to a moderator
|
|
|
|