Right outer join

Question

Post reply

Right outer join

cathie corcoran

SSC Enthusiast

Points: 145
More actions
August 10, 2004 at 6:03 am

#108484

i have a query written for oracle that i want to convert so that it can be used in sql server. my problem is that i want to use right outer joins but i cant seem to get the joins right. here is the query that was written for oracle:

SELECT CO.NAME, CP.GR_REF, CP.ACTIVE_PERIOD, CP.CRCODE,CP.PAYMENTDATE, CP.CLAIMID,
CP.CSNO, SUM(CD.GRWAGES+CD.GRSUBSISTANCE+CD.GRTRAVEL+CD.GRCOURSE+CD.GRCONSULTANCY )GRSUM,
ROUND(CT.GRTRAINOR) GRTRAINOR,
CP.GRNTTYPE, CP.CLAIMNO
FROM CLAIMSPERD_EMF CP, CLAIMSDETAIL_EMF CD, COMPANY CO, CLAIMSTRAINOR_EMF CT
WHERE CP.CLAIMID = CD.CLAIMID(+)
AND CP.CLAIMID = CT.CLAIMID(+)

i would really appreciate any suggestions

thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

A quick and easy way is:

WHERE CP.CLAIMID =* CD.CLAIMID

AND CP.CLAIMID =* CT.CLAIMID

------------------------------
The Users are always right - when I'm not wrong!

cathie corcoran SSC Enthusiast Points: 145 More actions · Answer 2

this worked well! i had to put the * on the lEft side of the = sign though cos i found out the (+) where iT was in the query used by oracle means a left join.

thanks

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

I forgot that. I have not worked on oracle for about 18 months and it shows.

------------------------------
The Users are always right - when I'm not wrong!