InfoSet Left Outer Join

[aidan_mulcahy]

Hi,

I am having an issue with a left outer join while joining 3 objects (R/3 and CRM) in BW. The three objects are:

t1 - 0CUST_SALES

t2 - 0BP_REL

t3 - 0ATR_DS01

The report should output all t1 customers displaying ALL the data from t2 and t3. If there is no link from t2 to t3 then the t2 data should still be displayed and blank entries in the t3 columns.

The model I am using is:

t1.customer := t2.bp1

t2.bp2 := t3.bp

where ':=' is left outer join.

What is actually in the info set is all t1 customers plus data from t2 and t3. But,

if there is no link from t2 to t3 then there is no data in the InfoSet.

I have checked using RSISET so it I don't think it has anything to do with the query desing.

Any ideas?

BW v3.1 sp 19

CRM 4.0 sp 9

R/3 4.6c

Thanks,

Aidan.

[Former Member]

Hi

I presume that you are creating an infoset? I don't think it is possible to create this in SAP but I’m not an expert on InfoSets. It's a problem on the database level where you can’t outer join a table to another table that is already outer joined to 3. table. A normal (but slow) SQL (oracle syntax) workaround would be the following (you can impliment it in ABAP using native SQL):

Select *

From ( Select *

From t2, t3

Where t2.bp2 = t3.bp(+) ) as t23

, t1

where t1.customer = t23.bp1(+)

I.e. create a implicit view t23 that holds t3 outer joined to t2 i.e. t23 will have all the lines from t2. The implicit view t23 is then outer joined to t1.

It will be slow because t2 may have lot of records that you really don't need i.e. the number of records returned by t23 will be large.

With Kind Regards

Kristian

[aidan_mulcahy]

Thanks Kristian,

maybe I should have explained? I'm very new to working with BW so not sure what to do with your solution. Where should I put this code?

I have been thinking that I should create a new ODS object (copy of 0BP_REL) which allows multiple entries for BP2 and use routines to populate the new entries from 0BP_REL and 0ATR_DS01. Then I could link it to 0CUST_SALES in the InfoSet.

This sounds reasonable to me - do you think it might work? Or is your solution better and easier?

Thanks,

Aidan.

[Former Member]

Hi Aidan

Yes it sound reasonable. It is basically the same as the SQL I suggested is doing. I.e. create a "place" where the content of t2 is enhanced with data from t3 and then outer join it to t1. In your suggestion the "place" is a new ODS and in mine it's an implicit view.

You should not try to go for my solution if you are new to BW, ABAP and native SQL your new ODS will do the trick.

With Kind Regards

Kristian

[aidan_mulcahy]

I'll give the ODS a go.

Thanks for your help.

[aidan_mulcahy]

appologies....

I didn't have both as left outer joins. Actual links are:

t1.customer := t2.bp1

t2.bp2 = t3.bp

where ':=' is left outer join

and '=' is inner join.

To me, having both as outer joins should work but when I try the system tells me that not all joins can be outer. There must be at least one inner.

Here is some sample data which hopefully will explain what I need:

t1

BP 1,2,...10

t2

BP1/BP2 3/305

BP1/BP2 4/306

BP1/BP2 7/910

BP1/BP2 7/309

BP1/BP2 9/311

BP1/BP2 9/123

BP1/BP2 9/124

t3

BP 123, 124

output should be:

1 t1 data

2 t1 data

3 t1 data t2 data

4 t1 data t2 data

5 t1 data

6 t1 data

7 t1 data t2 data

8 t1 data

9 t1 data t2 data t3 data

10 t1 data