No null records with left outer join

[Pete56]

CR 10; SQL Server 2000
Tables - HR = Patiets; OT = Orders
Output desired - list of patients with primary order (rank=1) or patients with no orders
Database setup - HR left outer join OT
Record selection criteria - {hr.pat_stat} = 'Active' and {hr.pat_type} = 'Supplies Only' and (isnull({ot.rank}) or {ot.rank} = 1)
This creates the following SQL in crystal -
SELECT "HR"."LAST_NAME", "HR"."PAT_TYPE", "HR"."PAT_STAT", "OT"."RANK", "HR"."FIRST_NAME"
FROM "CPR_SQL"."dbo"."HR" "HR" LEFT OUTER JOIN "CPR_SQL"."dbo"."OT" "OT" ON "HR"."MRN"="OT"."MRN"
WHERE "HR"."PAT_STAT"='Active' AND "HR"."PAT_TYPE"='Supplies Only' AND ("OT"."RANK" IS NULL OR "OT"."RANK"=1)

Problem - the patients without any orders do not appear.

I copy this into Enterprise Manager and they appear with a rank = null.

Why are they not in my report?

In summary - how do I join two tables where I include all the records in the left table and the records in the right table that meet a condition?

Thanks.

Pete

 

[kskid]

Change the record selection

instead of

(isnull({ot.rank}) or {ot.rank} = 1)


to

(isnull({ot.mrn}) or {ot.rank} = 1)

 

[Pete56]

This still returned all of the records except the one with no order. The new SQL is:
SELECT "HR"."LAST_NAME", "HR"."PAT_TYPE", "HR"."PAT_STAT", "HR"."FIRST_NAME", "OT"."RANK", "OT"."DESCRIP", "OT"."RXTYPE", "OT"."STATUS", "HR"."DELFLAG", "OT"."DELFLAG", "OT"."MRN"
FROM "CPR_SQL"."dbo"."HR" "HR" LEFT OUTER JOIN "CPR_SQL"."dbo"."OT" "OT" ON "HR"."MRN"="OT"."MRN"
WHERE "HR"."PAT_STAT"='Active' AND "HR"."DELFLAG"=0 AND "HR"."PAT_TYPE"='Supplies Only' AND ("OT"."MRN" IS NULL OR "OT"."RANK"=1)
ORDER BY "HR"."PAT_TYPE"

I figured out a work around where I use a formula in the detail section suppress option to suppress the records I don't need once they get to Crystal but this is kind of ugly.

Thanks for the suggestion.

Pete

 

[lbass]

You might want to make sure that you do not have "convert null values to default" checked in file->report options.

-LB

 

[Pete56]

Bingo. I changed this and the problem was resolved. Now I'll go read and fully understand this option.

Thank you so much, this information is HUGE!

Pete