Author |
Message |
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
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 Logged |
|
joeg1962
Newbie
Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
|
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 Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
Posted: 17 Apr 2013 at 5:09am |
right outer join is disabled
|
IP Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
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 Logged |
|
joeg1962
Newbie
Joined: 01 Mar 2013
Location: United States
Online Status: Offline
Posts: 35
|
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 Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
Posted: 17 Apr 2013 at 5:33am |
same result, only 3 lines
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
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 Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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 Logged |
|
|