Skip to Content
author's profile photo Former Member
Former Member

InfoSet Joins - Left Outer Join problems

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?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Apr 27, 2007 at 08:20 PM

    I solved my own issue.

    I had my primary table on the wrong side. It must go on the right side.

    Right click the ODS you wish to have a Left Outer Join relationship, selecting the Left Outer Join option. It will then turn white. It must be on the left side of the mappings screen.

    Problem solved.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.