Assign value in left outer join when right table value does not exist 1

[cathyg18]

I have a query with a left outer join, and a formula that calculates based on comparing one field in each table. I am having difficulty in making the value 0 when the field does not exist in the right table.

This is the formula I'm using right now.

IF {LEFTTABLE.VALUE} <> {RIGHTTABLE.VALUE}
and {LEFTTABLE.VALUE} <> 0
THEN {ANOTHERTABLE.VALUE} ELSE
if not isnull ({LEFTTABLE.VALUE}) then {LEFTTABLE.VALUE}
else
0

It works fine for everything except when there is no matching record in RIGHTTABLE. Instead of putting a 0 in the calculated field, it puts nothing, which then screws up other calculations that are based on this one.

Is there hope?

Thanks,
Cathy

 

[pgtek]

hi
what the value of
{ANOTHERTABLE.VALUE}
since its returning that value

cheers

pgtek

 

[cathyg18]

I'm not sure I quite understand what you are asking me here. Could you expand a bit?

 

[GMcNamara]

If no value exists for the right table, then that field will be null. You need to check if that field is null before you compare it to the left table/field because Crystal doesn't compare nulls with a known value predictably. Therefore



if not isNull({RIGHTTABLE.VALUE}) and
{LEFTTABLE.VALUE} <> RIGHTTABLE.VALUE} and
{LEFTTABLE.VALUE} <> 0 then
{ANOTHERTABLE.VALUE}
else if not isnull ({LEFTTABLE.VALUE}) then
{LEFTTABLE.VALUE}
else 0;

 

[pgtek]

Ok
you say if not isNull({RIGHTTABLE.VALUE}) and
{LEFTTABLE.VALUE} <> RIGHTTABLE.VALUE} and
{LEFTTABLE.VALUE} <> 0 then
{ANOTHERTABLE.VALUE} (what do you want this fied to return a blank, a zero or a 1???????????????)

 

[cathyg18]

Works like a charm!!!

Thank you so much. I have been beating my head against this wall for several days now.

Cathy