Problem using left outer join with null values in CR v9 1

[lsantos]

My report links a master table with 4 value tables

Master table:
key, valueA, valueB, valueC, valueD
1,A,B,C,D
2,,,,
3,,,,
4,A,B,C,D

Value Table(s):
key,description
A,descriptionA
B,descriptionB
C,descriptionC
D,descriptionD

Keys and values will vary but for the purpose of this explanation it is enough...

My problem is that report only shows records 1 and 4 because records 2 and 3 have null values in valueA, B C and D.

I do have the tables linked as left outer join so I would expect all records to be retrieved.

I also copied the report SQL statement and run it using Crystal SQL designer and got all 4 records.

Is there any trick I need to be aware of when using left outer join and nulls?

Any input will be very much appreciated.

Thanx
Luis

 

[synapsevampire]

Are you joining the tables?

What are you joining on?

I'll assume that it's the valueA-D to the ley in the values table. If so, you'lll need the values table added in 3 more times, and you might need to add to the record selection formula something like:

(
isnull(Master.valueA)
or
Master.valueA = valuetable1.key
)
and
(
isnull(Master.valueB)
or
Master.valueB = valuetable1alias1.key
)
and
(
isnull(Master.valueC)
or
Master.valueC = valuetable1alias2.key
)

etc.

-k

 

[lsantos]

synapsevampire,

Thanks for your input.

Yes, I am linking the valueA-D to the key in the values table.

I do understand that I need to add the "isnull" in the select statement (although I thought that the left outer join would take care of it).

However I don't understand why I need to add the values table 3 more times. Can you please explain?

Bellow is the select statement I have (already including the "isnull" statements).

I did try it but null values are still being excluded from report)

{aualdocs.doc_typ} = 61 and
(if {?Year} = 0 then true
else {aualdocs.doc_yer} = {?Year}) and
(if {?Number} = 0 then true
else {aualdocs.doc_num} = {?Number}) and
(if {?Part} = 0 then true
else {aualdocs.doc_prt} = {?Part}) and

{audmrefs_1.fil_typ} = "61" and
{audmrefs_1.ref_typ} = "dda_cd2" and
{audmrefs.fil_typ} = "61" and
{audmrefs.ref_typ} = "dda_cd1" and
{audmrefn_1.fil_typ} = "61" and
{audmrefn_1.ref_typ} = "ddn_cd1" and
{audmrefn.fil_typ} = "61" and
{audmrefn.ref_typ} = "ddn_cd2" and

(if isnull({aualdocs.ddn_cd1}) then true
else {aualdocs.ddn_cd1} = {audmrefn_1.ref_val}) and

(if isnull({aualdocs.ddn_cd2}) then true
else {aualdocs.ddn_cd2} = {audmrefn.ref_val}) and

(if isnull({aualdocs.ddf_cd1}) then true
else {aualdocs.ddf_cd1} = {audmrefs.ref_val}) and

(if isnull({aualdocs.ddf_cd2}) then true
else {aualdocs.ddf_cd2} = {audmrefs_1.ref_val})

Note: my master table is aualdocs and the value tables are 2x2 audmrefn and audmrefs