outer join in record selection formula in CR
Printed From: Crystal Reports Book
Category: General Information
Forum Name: Talk with the Author
Forum Discription: Ask Brian questions about his books and give him your comments. Like the book? Hate the book? Have suggestions? Let me know!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16672
Printed Date: 18 May 2024 at 10:18am
Topic: outer join in record selection formula in CR
Posted By: mani2011
Subject: outer join in record selection formula in CR
Date Posted: 04 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?  |
Replies:
Posted By: hilfy
Date Posted: 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 ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |