getting not match date range result

i have two tables.

table a

sobid        begda_a                endda_a      owner

1063     1999-06-19         2010-11-30  No substan

table b

pernr          begda_b                     endda_b            plans  
1063      1976-06-18            1999-06-18      999999999    
1063      1999-06-19            2008-11-10     20078
1063      2008-11-11            2010-11-30     20078
1063      2010-12-01           999-12-31       999999999

i want ouput as below

999999999  1063 No substan  2010-12-01           999-12-31

i am trying to do

from  table a
right outer join table b
on a.sobid =b.perner
and endda_a not between begda_b  and endda_b
and b.endda='999-12-31'

not getting desire result
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I believe you need a left outer join not a right outer join.

Also, you have a BUNCH of typos in your sample.

Here's the test case I set up that produces your desired results:

drop table tablea purge;
create table tablea (sobid number, begda_a varchar2(10), endda_a varchar2(10), owner varchar2(20));

insert into tablea values('1063','1999-06-19','2010-11-30','No substan');

drop table tableb purge;
create table tableb (pernr number, begda_b varchar2(10), endda_b varchar2(10), plans varchar2(20));

insert into tableb values('1063','1976-06-18','1999-06-18','999999999');
insert into tableb values('1063','1999-06-19','2008-11-10','20078');
insert into tableb values('1063','2008-11-11','2010-11-30','20078');
insert into tableb values('1063','2010-12-01','999-12-31','999999999');

--999999999  1063 No substan  2010-12-01           999-12-31
--i am trying to do

select * from 
tablea a
left outer join tableb b
on a.sobid =b.pernr
and endda_a not between begda_b  and endda_b
and b.endda_b='999-12-31'

Open in new window

Are your date fields truly date datatypes?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.