Talk with the Author: outer join in record selection formula in CR

Posted By: mani2011 — 4 Jun 2012 at 8:35pm

Hi,

I am trying to ennhace a crystal report. What I want is to add 2 columns in the report.

tech details:

I have a table "tref" with 2 of its fields as " areftype" and "arefcode".

areftype has values like "broker","owner","retail" ...

arefcode" has corresponding numbers.

My requirement is to add broker and owner columnd to report.

I could add it in the report by self join of tref table, but then I cannot put

tref.areftype (+)='broker'

and

tref_1.areftype(+)='owner'

in the record selection formula.

currently the result I am getting is only for the entries having both broker and owner details. where as for some entries either of them is present. In that case the missing field should appear as "null".

That is when I run a query in SQL developer is gived 60 rows (which is correct) but in CR it is just 6 rows.

CAn anybody suggest how to achieve the proper result as stated above?

Posted By: hilfy — 11 Jun 2012 at 4:24am

I assume you're doing this in your selection criteria, correct? If so, you'll also need to check for null. So, your criteria will look like this (note the parentheses in red).

(IsNull({tref.areftype}) or {tref.areftype} = 'broker')

and

(IsNull({tref_1.areftype}) or {tref_1.areftype = 'owner')

Also, you have to check for Null first, otherwise it won't work.

-Dell