Skip to Content
0
Former Member
Apr 27, 2007 at 03:39 PM

InfoSet Joins - Left Outer Join problems

76 Views

We are running BW 3.x. I am having an issue with joins for an InfoSet.

I have 4 ODS that I am including in the InfoSet. I'll give pseudo names for them in my example

ODS SalesOrder

Sales Order

Sales Order Line Item

ODS ProdOrder

Production Order

Sales Order

Sales Order Line Item

Prod. Line Status

ODS SalesOrderLine

Sales Order

Sales Order Line Item

NetValue

ODS Billings

Sales Order

Sales Order Line Item

Billing Date

I am creating my links as such:

SalesOrder.Sales Order -> ProdOrder.Sales Order

SalesOrder.Sales Order Line Item -> ProdOrder.Sales Order Line Item

SalesOrder.Sales Order -> SalesOrderLine.Sales Order

SalesOrder.Sales Order Line Item -> SalesOrderLine.Sales Order Line Item

SalesOrder.Sales Order -> Billings.Sales Order

SalesOrder.Sales Order Line Item -> Billings.Sales Order Line Item

My problem occurs when the sales order line item has not been invoiced and billed. There is not a line in the Billings to pick up. When that happens, my report will skip the Sales Order Line Item that it did not find a match for in Billings. This also occurs when it doesn't find a match in ProdOrder.

I will have a Sales Order Line Item in the SalesOrder ODS, which will be skipped if it doesn't find a match in all of the other 3 ODS.

What I tried to do is create a Left Outer Join on the Billings ODS. I right clicked the ODS and picked Left Outer Join. The Billings ODS turned white. However, it still did not pick up the record. I had assumed my join was backwards.

I then reset all my joins to regular Inner Joins. I then clicked the Sales Order ODS and selected Left Outer Join. It then make all the joins to the other 3 ODS Left Outer Joins. When I tried to activate, it told me "If an object is marked with 'left outer join', you can only link it to one other object."

And yes, I did run RSISET to adjust the InfoSets after each change.

Anyone have an idea if I am applying the Left Outer Join incorrectly?