Print Page | Close Window

not all records display

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19433
Printed Date: 26 Apr 2024 at 9:38pm


Topic: not all records display
Posted By: Macavity
Subject: not all records display
Date Posted: 17 Apr 2013 at 4:41am
Hi,

I have 2 tables linked with left outer join.

table 1 has 3 records per item:
order1
order2
order3

table 2 has 4 records per item:
order1, invoice1
order2, invoice1
order3, invoice1
order3, invoice2

the detail records should display 4 lines, but it only displays 3 lines (not order3, invoice2). However the sum of invoices per item is correct.
How to display all 4 lines ?

reversing the table links is not an option, that would mess up groups and sorts

thanks



Replies:
Posted By: joeg1962
Date Posted: 17 Apr 2013 at 4:59am
With that data, why are you doing an outer join? And, I think if any 'outer join' that it should be a right outer join.

Posted By: Macavity
Date Posted: 17 Apr 2013 at 5:09am
right outer join is disabled

Posted By: Macavity
Date Posted: 17 Apr 2013 at 5:10am
table 1 might have records without a link with table 2, they still have to be displayed

Posted By: joeg1962
Date Posted: 17 Apr 2013 at 5:13am
Left Outer Join:
The result set from a Left Outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup table.

Hence my thinking that this is not the correct Join. What happens with the standard 'Inner Join'?

Posted By: Macavity
Date Posted: 17 Apr 2013 at 5:33am
same result, only 3 lines

Posted By: DBlank
Date Posted: 18 Apr 2013 at 4:25am
are you using a select statement? Sometimes the way the select statement is written it can turn an outer join into an inner join by excluding the non-matched records.

Posted By: DBlank
Date Posted: 18 Apr 2013 at 4:27am
are you conditionally suppressing anything? Suppressed rows are still included in sums ("... sum of invoices per item is correct").

Posted By: Macavity
Date Posted: 19 Apr 2013 at 4:00am
no select statement, but there is a suppress : drilldowngrouplevel<2. I removed it, but still only 3 lines appear

Posted By: DBlank
Date Posted: 19 Apr 2013 at 4:05am
did you use any record from table 2 (invoice data)?
If not the join is not enforced unlkess you set it to enforced in the join set up.
drag the invoice field into the report and see if your data alters

Posted By: Macavity
Date Posted: 19 Apr 2013 at 5:03am
yes, the invoice amount is used. I know all the records are read, the sum is correct.
I tried a simple report with table 1 and table 2 and that works fine, all 4 lines display.

There are 3 more tables connected with table 1, I'm now removing them one by one to see what the results are


Print Page | Close Window