Linking in 8.5 vs 11

[cabrera01]

Icurrently have a report written in Crystal Reports 8.5 off of a SQL Server 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%'

 

[lbass]

Not sure, but one thing I noticed was that in the 8.5 report, you are showing only two left outer joins, which suggests that you have not changed the joins to the right of those joins to left outers as well--and all joins to the right of a left outer must also be left outers.

-LB

 

[cabrera01]

In the Crystal 8.5 report, if I were to change any of the other tables to the right to left outer, I get those SQL error messages. In reality, I change all of those in crystal 11 to left outer joins and it works. But for some reason it won't do it in crystal 8.5

 

[lbass]

You MUST change the joins to the right of a left join to left outer. You ARE trying to do this in the linking expert, right? Even in the XI report there are some inner joins to the right of a left-joined table that should be changed. How you do the joins can dramatically change your results, so you have be clear on why you are implementing the left joins in the first place.

-LB

 

[cabrera01]

Yes I am using the linking expert. I just double click on the current link between the 2 tables and change it from equal join to left outer join.

Changing the links is my last chance effort to get the results I need. I need a total list of patients, and that includes those that do not have an insurance carrier. I tried leaving the carrier table as an equal join and writing this formula:

if isnull({carrier.name}) then "NONE" else
if {carrier.name} = " " then "NONE" else
{carrier.name}

But it still doesn't return the additional records I need. I put this same formula in the Crystal XI with the left outer join and I get the additonal records. Thats why I was wondering if there was something I was missing between the 2 versions.

 

[lbass]

Are you sure you made all the necessary changes to the joins? You should check the SQL query to ensure it matches the XI query (although I think there was an error in the joins in that one, also--visit->pat->patmrnlist should all be left joins, too).

-LB