Skip to Content
0

How do you compare the values of two strings of different length inside an inner join?

Dec 27, 2016 at 08:26 PM

94

avatar image

I've tried a number of different things trying to comparing the field lop_d_list_hd-doc_item_no to wbhi-tposn. Nothing seems to be working. Neither of the bold options below is working.

doc_item_no: 0000000010 would be item 10. The data type is NUMC and the length is 10.

tposn: 000010 would be item 10. The data type is numc and the length is 6.

Does anyone know of a way to compare the two columns inside the inner join so I don't have to loop through the table to do the compare?

SELECT DISTINCT
A~DOC_NO, ", -- Document Number (TC #)
A~DOC_ITEM_NO, " DOC_ITEM_NO,
A~MASTER_NO, ", -- List Number
A~LOP_CAT, ", -- Option Category
A~LOP_ID, ", -- Option Category Description
B~MATNR, ", -- Material
C~TPOSN
INTO Table @IT_LOP_TEMP FROM LOP_D_LIST_HD AS A
INNER JOIN LOP_D_LIST_IT AS B
ON A~LIST_GUID = B~LIST_GUID
INNER JOIN WBHI AS C ON
C~TKONN = A~DOC_NO
* AND C~TPOSN = RIGHT(A~DOC_ITEM_NO)
*AND A~DOC_ITEM_NO = C~TPOSN
INNER JOIN WBHK AS D ON D~TKONN = C~TKONN
wHERE
A~LOP_CAT = P_LOP_CAT AND
B~MATNR in so_matnr AND
D~KUNNR = i_kunnr AND " -- Customer
D~btbsta in T_BTBSTA_RFC_SELOPT.

10 |10000 characters needed characters left characters exceeded

I see you tried to use the RIGHT function, but it won't work if you don't run ABAP 7.50.

As the 2 fields contain different values (0000000010 versus 000010), you can only use a join by using the new functions RIGHT or SUBSTR of ABAP 7.50. The only other way I know before 7.50 is to use 2 SELECT statements, the second being with FOR ALL ENTRIES where it's possible to indicate a substring, possibly with a join:

  • SELECT ... FROM lop_d_list_hd INTO TABLE itab_lop_d_list_hd ...
  • SELECT ... FROM wbhi FOR ALL ENTRIES IN itab_lop_d_list_hd WHERE c~tposn = itab_lop_d_list_hd-doc_item_no(6)...

But of course it may be counter-performing especially if the first SELECT returns too many lines.

0
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Christopher Solomon
Dec 27, 2016 at 09:15 PM
-1
Share
10 |10000 characters needed characters left characters exceeded