• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

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.

0
freeka
Asked:
freeka
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
freekaAuthor 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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now