Left Outer Join - Issue with conversion formula 2

[Roscoe307]

I have a left outer join from tableA to tableB on a field named {CODE}. TableA has one extra value under {CODE} which is '5'. '5' means that the {CODE} is unknown. TableB has the same codes EXCEPT for the '5'. Now when {CODE} = '5' in tableA the field {CODE_DESC} in tableB is empty. ({CODE_DESC} is just a text description of the code and is not available in tableA.) When {CODE}='5' I would like to display a 'U' in the report and then for the corresponding{CODE_DESC} display 'UNKNOWN'. I think I have a formula that works to convert the blank {CODE_DESC} to 'UNKNOWN' but I can't get the {CODE} formula to work. I've tried the following formula in the report but this only displays the "U" when the {tableA.CODE} ="5" and does not display any other codes.

IF {tableA.CODE} = "5" then "U" else if
(isnull({tableB.CODE_DESC})) then "U"
else {tableA.CODE}

Any ideas?

I'm using CRXI and an oracle database.

Thanks!

 

[Madawc]

Do you have a selection on TableB? If so, that would negate your left-outer. You'd need to include the possibility of a null on that test.

Failing that, try splitting the formula in three parts. Display them next to the data on a test report, to see which bit is working.

You could also test {tableA.CODE} for being spaces. Possibly also for being null.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Your description formula should be:

IF isnull({tableB.CODE_DESC}) or
trim({tableB.CODE_DESC})= "" then
"U" else
{tableB.CODE_DESC}

You must always check for a null before referencing the same field in the formula.

-LB