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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Are your date fields truly date datatypes?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.