How can I join 2 tables with converted UUID field?

user_17563865 0
Viewed 477

In SAP EWM the material ID is stored in /SAPAPO/ tables using the data element /SAPAPO/MATID which is a CHAR 22. In the other hand, /SCWM/ tables use the data element /SCWM/DE_MATID which is a RAW 16. All the standard code I've seen, uses the class CL_SYSTEM_UUID and for instance the method IF_SYSTEM_UUID_STATIC~CONVERT_UUID_C22 to map a C22 material ID to a X16.

This is preventing me to join tables directly without first selecting, then converting the material ID and finally selecting.

Is there a way to execute a SELECT joining two tables with the different type of ID?

The system is running a HANA database and ABAP 7.50.

The 2 tables I want to join are: /SAPAPO/MATKEY and /SCWM/PVPAKC

I would like to execute a select similar to this:

SELECT FROM /scwm/pvpakc AS pack_spec
  INNER JOIN /sapapo/matkey AS material ON material~matid = pack_spec~matid
  FIELDS pack_spec~pvguid  as ps_guid,
         material~matnr    as material_num
  INTO TABLE @DATA(lt_pack_spec_material).

Of course the above join is not possible since the MATID between tables needs to be converted

hana abap sql
3 Answers
user_9114190

If you're on an embedded EWM, you can use table MARA. It has both SCM and APO MATID's in it.

MARA-SCM_MATID_GUID16 (SCM)

MARA-SCM_MATID_GUID22 (APO)

user_34718080

You can create a separate mapping database table and use it for block-wise conversion C22 <-> X16 as is described in these posts ("database approach"):

  1. SAP HANA GUID conversion
  2. GUID and UUID in SAP HANA (referring to the first link with extra detail)

Another way is to append a customer field for MATID_X16 to /SAPAPO/MATKEY and to ensure it receives the correct value at product creation ("append approach").

I did some tests (mass selects on /SCWM/AQUA joined to /SAPAPO/MATKEY) comparing both approaches in which the append approach was consistently up to 10 times faster. Still I did't use any SQL scripting, for a start. There is certainly potential for performance improvements in the database approach compared to my tests.

user_76664560
Related