Inner Join with string operation

[Former Member]

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.

[Former Member]

Hi,

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

Regards,

Priyanka

[amol_samte]

Hi Krithika,

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

Regards,

Amol

[former_member194152]

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.

[former_member214709]

Dear,

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

[RaymondGiuseppi]

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

[former_member214709]

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.

[RaymondGiuseppi]

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..

[Former Member]

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.

    <your code with the internal table itab>

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.