topic Re: Inner Join with string operation in Application Development and Automation Discussions

Inner Join with string operation

Former Member — Thu, 18 Oct 2012 03:56:18 GMT

Hi,

Is the below scenario possible?

Select a~field 1

a~field 2

from ( ( table1 as a inner join table 2 as b on

a~field3 = b~field 3) inner join table 3 as c

on c~field 4 = a~field4+2(4) )

into table internal table1

where a~field 5 = 'XYZ'.

When syntax is checked, SAP return with the error a~field4 is unknown. Is this because string operation is not allowed in case of Joins or is my syntax wrong?

Thanks for the help.

Re: Inner Join with string operation

Former Member — Thu, 18 Oct 2012 04:12:29 GMT

Hi,

on c~field 4 = a~field4+2(4) is not allowed on inner join.

Regards,

Priyanka

Re: Inner Join with string operation

amol_samte — Thu, 18 Oct 2012 04:19:57 GMT

Hi Krithika,

U can perform the same operation after getting all data into internal table.

Regards,

Amol

Re: Inner Join with string operation

former_member194152 — Thu, 18 Oct 2012 04:57:59 GMT

in inner join condition you cant but in where condition you can put offset specification in right hand side condition where you wrote 'XYZ', but some time it will give warning.

Standard practice is do not put offset specification in select query, do all these kind of operation in internal table.

Re: Inner Join with string operation

former_member214709 — Thu, 18 Oct 2012 05:18:44 GMT

Dear,

Try to use For All Entries as it is considered better to use FOR ALL ENTRIES than opting fo r JOIN statement

Re: Inner Join with string operation

RaymondGiuseppi — Thu, 18 Oct 2012 06:18:06 GMT

Can you explain your opinion, as from my experience JOIN get almost always better performance than FOR ALL ENTRIES as soon as there are many records in the internal tables or not de the full primary key provided, also look at some blog like excellent Rob's JOINS vs. FOR ALL ENTRIES - Which Performs Better?

Regards,
Raymond

Re: Inner Join with string operation

former_member214709 — Thu, 18 Oct 2012 08:16:15 GMT

I think you did not go through the blog properly, in the end the result the author quoted was:

There are other considerations that come into play as well:

INNER JOINs only look at the intersection of the results that meet the WHERE clause.
FOR ALL ENTRIES eliminates duplicates from the results.
I find JOINs to be more time consuming to code. (I can never find the “~” key.)
When using FOR ALL ENTRIES you generally end up with at least two internal tables. This may or may not be a good thing.
The example I have shown uses the full primary key. Some preliminary testing I have done comparing JOINs with FOR ALL ENTRIES show that FOR ALL ENTRIES can give better performance in that case.

Re: Inner Join with string operation

RaymondGiuseppi — Thu, 18 Oct 2012 08:30:37 GMT

I read this document carefully (a long time ago), but the first part of the answer was refering to my (humble) experience, then I added a reference to experience of another member of the community, no problem

Actually I mostly use FOR ALL ENTRIES when the set is of relative small size, or when I don't have other option (I dislike those cluster/pool tables, legacy from another time )

Also I became, for two years now, addict on subqueries which can habve better performance than join if data is not required but only selection criteria, also they can help balancing the load between database and application server.

Regards,

Raymond

PS: Also I mapped the '~' on my keyboard to a free key..

Re: Inner Join with string operation

Former Member — Mon, 03 Oct 2016 07:06:45 GMT

Hello,

I got it with new open SQL Syntax avalaible from enhancement 740.

For example:

Table ZHR_EXAMPLE_TABLE2 has the column ID_EMPLEADO with 6 characters instead of 8 like PERNR in table ZHR_EXAMPLE_TABLE1.

SELECT

T0~COLUMN1, T0~COLUMN2, T0~COLUMN3, T0~COLUM4, T1~COLUMN1 AS COLUMRESULT, T1~PERNR

FROM ZHR_EXAMPLE_TABLE1 AS T0

INNER JOIN ZHR_EXAMPLE_TABLE2 AS T1 ON SUBSTRING( T1~PERNR,3,6 ) = T0~ID_EMPLEADO

WHERE T0~IDPARTE = @ZIDPARTE AND T1~BEGDA LE T0~FECHA AND T1~ENDDA GE T0~FECHA

INTO CORRESPONDING FIELDS OF TABLE @ITAB.

IF SY-SUBRC EQ 0.

ENDIF.

Instead of "INTO CORRESPONDING FIELDS OF TABLE @ITAB" you can use "INTO TABLE @DATA(result)". With result no declarated before, the result of the select will be in the that table.

I hope it can be useful for someone.

Kind regards,

Julian.