Joining two tables using a full join statement

[cbyrne]

Hello i am trying to join two tables in cognos/report studio and below is the sql code i have entered but i keep getting an error missing keyword.  Can anybody spot a mistake in the below code.

SELECT a.evt_code, b.ect_labor
FROM r5events a
FULL JOIN r5eventcosts b
WHERE r5events a.a.evt_code = r5eventcosts b.b.ect_labor


Many Thanks

[blom0344]

SELECT a.evt_code, b.ect_labor
FROM r5events a
FULL JOIN r5eventcosts b
WHERE r5events a.a.evt_code = r5eventcosts b.b.ect_labor

SELECT a.evt_code, b.ect_labor
FROM r5events a
FULL JOIN r5eventcosts b
on a.evt_code = b.ect_labor

syntax-wise . However only fetching the join object is pretty useless (a union would then be better)



SELECT coalesce(a.evt_code, b.ect_labor), a.[somefield],b.[otherfield]
FROM r5events a
FULL JOIN r5eventcosts b
on a.evt_code = b.ect_labor

would make more sense..

[cbyrne]

Yes i agree this would make more sense to use a union.  I have tried this code and it now works thank you very much for you response.

Many Thanks

Chris