Cannot use LEFT OUTER JOIN, must use old ( + ) type of join syntax.

Hi there

Here is the query:

select count(*)
from ipgsubsdisc a left outer join costedevent b on a.account_num=b.account_num and a.bill_seq=b.event_seq and a.mpn=b.event_source
where b.account_num is null

Please advise.

freekaAsked:
Who is Participating?
 
freekaConnect With a Mentor Author Commented:
Here it is:

select count(*) from ip a, costedevent b
where a.account_num  = b.account_num (+)
and
a.bill_seq  = b.event_seq (+)
and
a.mpn  = b.event_source (+)
and
b.account_num is null

Almost but not quite by some.
0
 
derekkrommCommented:
select count(*) from ip a, costedevent b
where a.account_num (+) = b.account_num and
a.bill_seq (+) = b.event_seq and
a.mpn (+) = b.event_source and
b.account_num is null
0
 
LowfatspreadCommented:
for sql server 7 & sql server 2000
use *= for the left outer join conditions

which database are you using?

select count(*)
from ipgsubsdisc a ,
costedevent b
where b.account_num is null
and  a.account_num*=b.account_num
and a.bill_seq*=b.event_seq
and a.mpn*=b.event_source

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
freekaAuthor Commented:
I added a.bill_date = '20070202' to both my query and the queries above suggested as answer and both the above bring back a result of zero whereas the original brings back 3000+ records ?!

Please advise.
0
 
freekaAuthor Commented:
not to confuse anyone but bill_date is a VARCHAR2 datatype not a date so essentially all i did was add a string value to the constraint.
0
 
sathyagiriCommented:
What format is the date stored in the varchar2 column?
0
 
freekaAuthor Commented:
Nevermind, I solved it. And it was none of the answers above so go figure EE admins.
0
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.