ABAP SQL | Client handling in Joins

[michaB_badura]

Hi all,

while reading the HA400 training course script (course version 12) I encountered following passage:

At first I thought: wow, never heard of it, apparently all our joins are badly wrong! But since this passege describes changes as of 7.4, I just tried it in a 7.0 system. I created two tables - head and items, then I filled both of them, but the first in one client, and the second in another client. No matter in which client and whether inner or outer join - I'm getting no data from the other client.

So what does this improvement mean and is it reasonable in systems < 7.4 to add client field to the on-condition?

I could not find any clues in the documentation so I would be very thankful, if one of You experts could put some light on this mysterious improvement.

Best regards

Michał

[michaB_badura]

Hi all,

while reading the HA400 training course script (course version 12) I encountered following passage:

At first I thought: wow, never heard of it, apparently all our joins are badly wrong! But since this passege describes changes as of 7.4, I just tried it in a 7.0 system. I created two tables - head and items, then I filled both of them, but the first in one client, and the second in another client. No matter in which client and whether inner or outer join - I'm getting no data from the other client.

So what does this improvement mean and is it reasonable in systems < 7.4 to add client field to the on-condition?

I could not find any clues in the documentation so I would be very thankful, if one of You experts could put some light on this mysterious improvement.

Best regards

Michał

[Sandra_Rossi]

It has always been how it worked in ABAP, whatever the database system is.

In your title, you say "ABAP SQL", but the training is HANA. So I guess your title is wrong. This feature is probably only for SQL in HANA, not from ABAP SQL.

[michaB_badura]

Thank You very much for Your answer, Sandra.

This passage comes from Unit 3 of HA400. This is Database Independent Code-to-Data. Concretely it is Lesson Enhanced Open SQL. This lesson describes Open / ABAP SQL enhancements from 7.40 on. So nothing HANA specific here.

[Sandra_Rossi]

I quickly looked at all official ABAP changes in 7.40 and couldn't find anything. Maybe AMDP or ABAP CDS? I don't know HA400, maybe you can show more around the concerned text so that other people like me can help?

[michaB_badura]

I can copy paste more passages from my HA400-script, but please note, I'm not sure whether it's not a violation against copy right...

This is the topic of the lesson:

[michaB_badura]

Here is the slide with some of the changes to Open / ABAP SQL that came with 7.40:

[michaB_badura]

And here is the rest of the description to the above slide. Here you will also find the passage I posted in my question:

[michaB_badura]

Sorry for three separate comments - I tried twice to put all in one comment, but somehow was not able to submit it.

[Sandra_Rossi]

It's probably a violation of copyright but I doubt SAP would pursue for that. Most of content is in the ABAP documentation. Anyway, you can delete these comments in a few days.

Is it possible that the SELECT was previously done by adding the client selection for all tables in the WHERE only (a~client = '100' and b~client = '100'), and the equality is now added (ON a~client = b~client). The result doesn't change but maybe the database could better optimize?

[Sandra_Rossi]

Maybe this note explains a little bit how it used to work in the past: note 621640 SELECT with JOIN and automatic client handling

[Sandra_Rossi]

So, if we have this Open SQL code, where tables T1 and T2 are client-dependent:

SELECT *
FROM t1 INNER JOIN t2 
ON t1~keyfield = t2~keyfield
WHERE t1~attr = ''

In versions < 7.40 maybe the Open SQL SELECT converted to SELECT of the database was only including the client selection (example for client 100):

SELECT *
FROM t1 INNER JOIN t2 
ON t1.keyfield = t2.keyfield
WHERE t1.client = '100' and t2.client = '100' and     " <====== only here
      t1~attr = ''

In versions >= 7.40 the join between clients has been added:

SELECT *
FROM t1 INNER JOIN t2 
ON t1.client   = t2.client AND                        " <====== here (new in 7.40)
   t1.keyfield = t2.keyfield
WHERE t1.client = '100' and t2.client = '100' and     " <====== and here 
      t1~attr = ''

The returned rows are the same for both queries BUT the database may sometimes better optimize the query if the join of the SQL converted to database includes explicitly t1.client = t2.client. It's what is explained in the consulting note 621640 - SELECT with JOIN and automatic client handling:

• "When you use a SELECT statement with a JOIN statement for two or more tables, the database access is slower than expected."
• "To assist the database optimizer, you can add a comparison in the 'TAB1~MANDT equals TAB2~MANDT' format to the ON condition."

So, in this note, SAP proposed eventually to test if this solution in Open SQL (in versions before 7.40) improves the performance:

SELECT *
FROM t1 INNER JOIN t2 
ON t1~client   = t2~client AND             " <====== you may add it (not needed after 7.40)
   t1~keyfield = t2~keyfield
WHERE t1~attr = ''

[michaB_badura]

Thank You Sandra for Your answer! Actually I was also thinking about it. But I couldn't find that note, so thank You.

Now I tried to add the MANDT field to the ON condition, since the target systems for which we develop range from 7.0 to the newest ones. But from 7.40 on the use of MANDT field in the ON condition is notified as an error - while (extended) syntax check and in the default variant of Code Inspector.

Dynamic SQL is no choice, so I think we'll end up ignoring this issue - only customers with older systems can possibly be affected.