I currently have a report written in Crystal Reports 8.5 and I need to left join some tables.
When I change the tables to left outer join I get these error messages stating: General SQL Server: Check Message from SQL Server and Error Detected by Database DLL. When I revert them back, the report works.

When I run the same report in Crystal 11, and change all the necessary links to left outer, I don't get any errors.

Unfortunately I need this report to work in Crystal Reports 8.5, how can I get this to work?

Crystal 8.5 Working Code:
SELECT
appt.start_datetime, appt.apptstatus_id,
probooking.act_name,
physician.name,
loc.abbr,
visit.pat_acct_num, visit.admit_reason,
visitpayerlist.visit_auth, visitpayerlist.policy_number, visitpayerlist.grp_number,
pat.birthdate, pat.home_phone, pat.work_phone, pat.name_display,
patmrnlist.mrn,
carrier.name
FROM
prod.dbo.appt appt,
prod.dbo.visitapptlist visitapptlist,
prod.dbo.resunit resunit,
prod.dbo.probooking probooking,
prod.dbo.res physician,
prod.dbo.loc loc,
prod.dbo.visit visit,
prod.dbo.visitpayerlist visitpayerlist,
prod.dbo.pat pat,
prod.dbo.patmrnlist patmrnlist,
prod.dbo.payer payer,
prod.dbo.carrier carrier
WHERE
appt.appt_id = visitapptlist.appt_id AND
appt.resunit_id = resunit.resunit_id AND
appt.appt_id = probooking.appt_id AND
appt.attending_phys_id *= physician.res_id AND
resunit.resunit_id = loc.loc_id AND
visitapptlist.visit_id = visit.visit_id AND
visit.visit_id = visitpayerlist.visit_id AND
visit.pat_id = pat.pat_id AND
pat.pat_id *= patmrnlist.pat_id AND
visitpayerlist.payer_id = payer.payer_id AND
payer.carrier_id = carrier.carrier_id AND
appt.start_datetime >= "Nov 26 2008 00:00:00AM" AND
appt.start_datetime <= "Nov 26 2008 11:59:59PM" AND
(appt.apptstatus_id = 3 OR
appt.apptstatus_id = 1) AND
probooking.act_name NOT LIKE '%TREATMENT%'


Crystal 11 Working Code:
SELECT "appt"."start_datetime", "probooking"."act_name", "pat"."name_display", "pat"."birthdate", "visit"."pat_acct_num", "patmrnlist"."mrn", "pat"."home_phone", "pat"."work_phone", "physician"."name", "visit"."admit_reason", "appt"."apptstatus_id", "loc"."abbr", "carrier"."name", "visitpayerlist"."policy_number", "visitpayerlist"."grp_number", "visitpayerlist"."visit_auth"
FROM (((((((((("prod"."dbo"."appt" "appt" INNER JOIN "prod"."dbo"."resunit" "resunit" ON "appt"."resunit_id"="resunit"."resunit_id") INNER JOIN "prod"."dbo"."probooking" "probooking" ON "appt"."appt_id"="probooking"."appt_id") LEFT OUTER JOIN "prod"."dbo"."visitapptlist" "visitapptlist" ON "appt"."appt_id"="visitapptlist"."appt_id") LEFT OUTER JOIN "prod"."dbo"."res" "physician" ON "appt"."attending_phys_id"="physician"."res_id") LEFT OUTER JOIN "prod"."dbo"."visit" "visit" ON "visitapptlist"."visit_id"="visit"."visit_id") INNER JOIN "prod"."dbo"."pat" "pat" ON "visit"."pat_id"="pat"."pat_id") LEFT OUTER JOIN "prod"."dbo"."visitpayerlist" "visitpayerlist" ON "visit"."visit_id"="visitpayerlist"."visit_id") LEFT OUTER JOIN "prod"."dbo"."payer" "payer" ON "visitpayerlist"."payer_id"="payer"."payer_id") LEFT OUTER JOIN "prod"."dbo"."carrier" "carrier" ON "payer"."carrier_id"="carrier"."carrier_id") LEFT OUTER JOIN "prod"."dbo"."patmrnlist" "patmrnlist" ON "pat"."pat_id"="patmrnlist"."pat_id") INNER JOIN "prod"."dbo"."loc" "loc" ON "resunit"."resunit_id"="loc"."loc_id"
WHERE ("appt"."start_datetime">={ts '2008-11-26 00:00:00'} AND "appt"."start_datetime"<{ts '2008-11-27 00:00:00'}) AND ("appt"."apptstatus_id"=1 OR "appt"."apptstatus_id"=3) AND "probooking"."act_name" NOT LIKE '%TREATMENT%'

Open in New Window Select All Tags:
Crystal Reports 8.5 and 11
Zone:
Crystal Reports Software