Query on joins

[former_member226419]

Hi,

I am going through joins in information views and have small doubts.

Sorry for asking such basic questions once again but I am not still satisfied with the results searched on SCN.

Consider below tables.

Test1:

Matnr	text
100	abc
200	def
300	ghi
400	jkl
500	mno

Test10:

Matnr	text_alt
100	abc
100	def
100	ghi
300	jkl
300	mno
300	abc
400	def
400	ghi

Here are my questions:

Attribute view:

a.) When I am doing inner join or referential join ,I get the same result as below. I haven't selected any column from right table but results are same          for both joins. Why? Referential join should act as left outer join as per concept. Please let me know the reason.

Analytical join: 

b.) When  I am using inner join in analytical view I am getting below output. Its deleting duplicates for MATNR which I think it should not.

     Please let me know the reason.

Matnr          Text

100             abc

300             ghi

400             jkl

I think output should be as below. And I can see in left outer or right outer join , its behaving same as above, deleting duplicated based on MATNR.

Please clarify.

c.) Calculation View:

Why we don't have referential join in calculation view? Any specific reason?

Also I have noticed one thing, when I applied filter on right side table in inner join or referential join, it gives me correct result , but when i go through posts on SCN, its written referential join will not work with filters. I am confused , please clarify.

BR

Sumeet

[lbreddemann]

Hi Sumeet,

referential joins are meant to provide an additional option for run time shortening under very specific conditions.

If you think about when it could be possible to not touch the second table for a join and still yield the correct result, then this is only the case for

• inner joins
• where there actually is one and only one matching record in the second table
• when no columns from the second table are requested

For other cardinalities, like 1..0 and 1..n, the database has to go and look up how many records are actually matching. So, there's no benefit here.

For outer joins only the cardinality 1..0/1 would allow not touching the second table, as every record in the starting table would yield exactly one record in the result set.

Whenever any of these prerequisites is not fulfilled, the second table must be touched, even if no columns from it are requested.

Since I don't know how you join the tables and how the cardinalities are set, there's not much more I can tell here.

You may want to go and check the "Validate Join" functionality (right click on the join) to get advise on the join type and the cardinality.

- Lars

[former_member226419]

Hi lars,

Thanks for your reply. Just for clarification below I did in my models.

--> I haven't given any cardinality means left it as blank.

--> I have joined MATNR of test1 with MATNR of test10  and then testing it with different types of joins as discussed below.

Still I have some questions:

In attribute view, I cannot see any difference in referential join as well as inner join. Whether I select any field or not from right table it is giving same result of inner join. Confused...

In analytical view, when I give as referential join, its working as per concept, that is will work as normal left outer join when no field is selected from right side table.

But again small confusion, when I give inner join, below output is coming.

Matnr          Text

100             abc

300             ghi

400             jkl

But I think output should be as below.

I hope you understood my query.

Thanks and Regards

Sumeet

[lbreddemann]

If I am not completely off (and I easily might be, given that I haven't touched a referential join for the last 2 years before your question), the actual ref. join optimization (leaving out the access to the one join partner) is only done in the analytic view.

And that even though you define it in the attribute view.

For the inner join you're right and on my rev. 94 is does exactly the correct thing: for every match it returns one result row.

No idea what runs wrong on your system.

- Lars

[former_member226419]

Thanks for your reply. But then why SAP has given referential join in attribute view. What's the use of the same?

[lbreddemann]

I'd say this is because the join itself is defined in the attribute view (internal name for that is column view type "join").

Look, the main purpose of attribute views is it to be used in conjunction with analytic views.

To me, the referential join is not that particularly exciting, since most of the time, you cannot guarantee that the pre-conditions are actually met.

And if you cannot do that, this join is simply not to be used, as long as you're interested in correct results.

- Lars