oracle start_date and end_date

Hi,

what effect has using (+)  after start_date and end_date when we extract a list of employees with
their relative information where family joining start_date and end date falls in the following condition:

trunc(sysdate) between start_date(+) and end_date(+)

thanks
shmzAsked:
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:
That is the old Oracle syntax for an "outer join".

The effect depends on the tables joined.  It will either be a left outer join or a right outer join.

An outer join allows rows to be returned from one table even if matching rows aren't in the other table.
0

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
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
... and the field with (+) is the one which might not exist. Basically the other table (employee?)  will always show its rows, but the one with the dates only if both dates are of today. Kinda strange condition, though.
0
shmzAuthor Commented:
Hi,

I need to understand this, I am looking at a pre-existing query written by someone else and need to figure all details:

if I have following records:

employeeid | contact_id | contact_start_date | contact_end_date | employee start_date | employee_end_date

1111|111111|2015-05-23||2010-10-10|
1111|111222|2014-05-20||2010-10-10|
1111|111333|2013-05-09|2014-05-19|2010-10-10|

2222|221111|2014-09-14|2015-05-05|2010-10-10|

and I use the following where clause :
where
trunc(sysdate) between employee_start_date(+) and employee_end_date(+)
and
trunc(sysdate) between contact_start_date(+) and contact_end_date(+)

Q1: this will return all records above right?

Q2: but if I want to return only those records that their contact is current (not expired) then what is the condition?

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
You are only selecting that table?
0
shmzAuthor Commented:
Qlemo, not sure about your question?
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Are we talking about a single table here? Because the original question tells different. And a outer join of a single table is rather useless.
0
slightwv (䄆 Netminder) Commented:
>>Q1: this will return all records above right?
>>Q2: but if I want to return only those records that their contact is current (not expired) then what is the condition?

We have no way to know the answers to these questions.  We do not know your tables, data or requirements.

If you want a copy/paste answer, please post a small test case with tables, sample data and expected results.

Here is a small example of the joins.  Hopefully you have a development database to run it.  If not, I suggest sqlfiddle.

drop table tab1 purge;
create table tab1(mgr number, emp number);

insert into tab1 values(1,1);
insert into tab1 values(1,2);
insert into tab1 values(2,3);
insert into tab1 values(3,4);
commit;

drop table tab2 purge;
create table tab2(mgr number);

insert into tab2 values(2);
commit;


--without the outer join:
select t1.mgr,emp
from tab1 t1, tab2 t2
where t1.mgr=t2.mgr
/

--with the outer join:
select t1.mgr,emp
from tab1 t1, tab2 t2
where t1.mgr=t2.mgr(+)
/

--same outer join using ANSI syntax:
select t1.mgr,emp
from tab1 t1 left outer join tab2 t2 on t1.mgr=t2.mgr
/

Open in new window

0
shmzAuthor Commented:
thank you
0
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.

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.