Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: not all records display Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Macavity
Groupie
Groupie


Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
Quote Macavity Replybullet Topic: not all records display
    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

IP IP Logged
joeg1962
Newbie
Newbie


Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
Quote joeg1962 Replybullet 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.

IP IP Logged
Macavity
Groupie
Groupie


Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
Quote Macavity Replybullet Posted: 17 Apr 2013 at 5:09am
right outer join is disabled
IP IP Logged
Macavity
Groupie
Groupie


Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
Quote Macavity Replybullet Posted: 17 Apr 2013 at 5:10am
table 1 might have records without a link with table 2, they still have to be displayed
IP IP Logged
joeg1962
Newbie
Newbie


Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
Quote joeg1962 Replybullet 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'?
IP IP Logged
Macavity
Groupie
Groupie


Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
Quote Macavity Replybullet Posted: 17 Apr 2013 at 5:33am
same result, only 3 lines
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.

Edited by DBlank - 18 Apr 2013 at 4:28am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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").
IP IP Logged
Macavity
Groupie
Groupie


Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
Quote Macavity Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.