Right outer join 1

[cathiec]

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

 

[vongrunt]

Two questions:

1. This query uses left joins ((+) denotes nullable side). Any special reason you want to swith to right joins?
2. What is with table "company"? It isn't joined to anything. If it has only one record then OK, otherwise you'll get sub-cartesian product.

 

[cathiec]

my apologies - i am new to oracle and i was led to believe that the position of the (+) syntax meant a right outer join.

i should also have added the line to the query:

"AND CP.CSNO = CO.CSNO" to join the company and the claimsperd_emf table.

i have copied the tables from oracle to sql server so now
what i need to do is run this query in sql server so that it returns the same results that it would if run from oracle.

 

[vongrunt]

Try something like:

SELECT CO.name,
CP.gr_ref, CP.active_period, CP.crcode, CP.paymentdate, CP.claimid, CP.csno,
(	select SUM(CD.grwages+CD.grsubsistance+CD.grtravel+CD.grcourse+CD.grconsultancy) 
	from CLAIMSDETAIL_EMF CD 
	where CD.claimid=CP.claimid	) grsum,
ROUND(CT.grtrainor, 0) grtrainor,
CP.grnttype, CP.claimno
FROM CLAIMSPERD_EMF CP 
INNER JOIN COMPANY CO on CP.csno= CO.csno
LEFT JOIN CLAIMSTRAINOR_EMF CT on CP.claimid=CT.claimid

The only thing I'm not sure is SUM() stuff - please check it.