Skip to Content

Join Conditions

Hello, i have a question about a infoset. i am trying to create a infoset on 2 infoobjects and 3 custom dsos ok. the characteristics are:

0profit_ctr and 0costcenter and dsos are custom dsos

profit center is an attribute of cost center

i created a left outer join between these 2 chars making cost center as my left outer join because i want my users to run the query by profit centers. the other dsos i did simple join from either profit center or cost center char..

i created a query and put cost center from 0cost_center table and 0profit_ctr from 0profit_center table. i created a variable off profit center char.

i ran that query and put in profit center on variable screen and i know that one profit center exists because i went to cost center table and i was able to see the profit center value which i input in that variable.

is there something am i doing wrong? please share your opinion.

thanks.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Jul 22, 2016 at 04:53 PM

    Hi Sunil, I think I remember running into a similar situation.

    Short answer: try removing any additional objects that you have joined to the profit center table and see if you get results back. I noticed that BW seems to convert left outer joins to inner joins when you try to join additional tables to tables on the right side of a left outer join.

    Long answer:

    Right now I think the core of your InfoSet is like this:

    0COSTCENTER ---LEFT JOIN---> 0PROFIT_CTR

    In this scenario 0COSTCENTER is the left table and 0PROFIT_CTR is the right table. This join will give you all records from the left table (0COSTCENTER) and any matching records from the right table (0PROFIT_CTR).

    You could extend it like this with a custom DSO:

    DSO1 <---INNER JOIN---> 0COSTCENTER ---LEFT JOIN---> 0PROFIT_CTR

    In this example DSO1 and 0COSTCENTER form the left side of the join together and you will only get records from DSO1 and 0COSTCENTER that have a match, and any matching records from 0PROFIT_CTR.

    But if you try to do the following:

    0COSTCENTER ---LEFT JOIN---> 0PROFIT_CTR ---JOIN--->DSO2

    BW seems to treat it like:

    0COSTCENTER ---INNER JOIN---> 0PROFIT_CTR ---INNER JOIN---> DSO2

    So it has converted your left join to an inner join, and now if there are no matching records in 0PROFIT_CTR or DSO2 then you will not get any results.

    This is why I said try removing the join to your custom DSO from 0PROFIT_CTR. Doing so will prevent BW from converting your left join to an inner join. Of course then you need to figure out what to do about DSO2, hopefully you can join it to 0COSTCENTER instead.

    Add a comment
    10|10000 characters needed characters exceeded

    • Then that would seem to indicate there is no matching record in the custom DSO to match ZFIELD1 from 0COSTCENTER. Try outputting the results from the join that works, including the custom ZFIELD1 field. Then manually check the values of ZFIELD1 to see if there are any matching records in the DSO.

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.