SELECT with substring in ON clause?

Question

I have the following select statement in ABAP:

SELECT munic~mandt VREFER BIS AB ZZELECDATE ZZCERTDATE CONSYEAR ZDIMO ZZONE_M ZZONE_T USAGE_M USAGE_T M2MC M2MT M2RET EXEMPTMCMT EXEMPRET CHARGEMCMT
INTO corresponding fields of table GT_INSTMUNIC_F
FROM ZCI00_INSTMUNIC AS MUNIC
INNER JOIN EVER AS EV on
  MUNIC~POD = EV~VREFER(9).
"where EV~BSTATUS = '14' or EV~BSTATUS = '32'.

My problem with the above statement is that does not recognize the substring/offset operation on the 'ON' clause. If i remove the '(9) then it recognizes the field, otherwise it gives error:

Field ev~refer is unknown. It is neither in one of the specified tables nor defined by a "DATA" statement. I have also tried doing something similar in the 'Where' clause, receiving a similar error:

LOOP AT gt_instmunic.

 clear wa_gt_instmunic_f.

 wa_gt_instmunic_f-mandt = gt_instmunic-mandt.
 wa_gt_instmunic_f-bis = gt_instmunic-bis.
 wa_gt_instmunic_f-ab = gt_instmunic-ab.
 wa_gt_instmunic_f-zzelecdate = gt_instmunic-zzelecdate.
 wa_gt_instmunic_f-ZZCERTDATE = gt_instmunic-ZZCERTDATE.
 wa_gt_instmunic_f-CONSYEAR = gt_instmunic-CONSYEAR.
 wa_gt_instmunic_f-ZDIMO = gt_instmunic-ZDIMO.
 wa_gt_instmunic_f-ZZONE_M = gt_instmunic-ZZONE_M.
 wa_gt_instmunic_f-ZZONE_T = gt_instmunic-ZZONE_T.
 wa_gt_instmunic_f-USAGE_M = gt_instmunic-USAGE_M.
 wa_gt_instmunic_f-USAGE_T = gt_instmunic-USAGE_T.

 temp_pod = gt_instmunic-pod.

  SELECT vrefer
  FROM ever
    INTO wa_gt_instmunic_f-vrefer
    WHERE ( vrefer(9) LIKE temp_pod  ).            " PROBLEM WITH SUBSTRING
    "AND ( BSTATUS = '14' OR BSTATUS = '32' ).
  ENDSELECT.

  WRITE: / sy-dbcnt.
  WRITE: / 'wa is: ', wa_gt_instmunic_f.
  WRITE: / 'wa-ever is: ', wa_gt_instmunic_f-vrefer.
  APPEND wa_gt_instmunic_f TO gt_instmunic_f.
  WRITE: / wa_gt_instmunic_f-vrefer.
ENDLOOP.

itab_size = lines( gt_instmunic_f ).
WRITE: / 'Internal table populated with', itab_size, ' lines'.

The basic task i want to implement is to modify a specific field on one table, pulling values from another. They have a common field ( pod = vrefer(9) ). Thanks in advance for your time.

vwegert suggestion (to merge both tables manually) sounds better than any SQL dirty trick. Besides, are you sure about your requirements or your approach to them? I fear that use a partial field to link data is dangerous... what if you have two contracts with the same 9 initial positions? How do you plan to decide which one is the right one? — VXLozano

Gert Beukema Gert Beukema · Accepted Answer · 2018-03-05T19:36:36

If you are on a late enough NetWeaver version, it works on 7.51, you can use the OpenSQL function LEFT or SUBSTRING. Your query would look something like:

SELECT munic~mandt VREFER BIS AB ZZELECDATE ZZCERTDATE CONSYEAR ZDIMO ZZONE_M ZZONE_T USAGE_M USAGE_T M2MC M2MT M2RET EXEMPTMCMT EXEMPRET CHARGEMCMT
  FROM ZCI00_INSTMUNIC AS MUNIC
  INNER JOIN ever AS ev 
          ON MUNIC~POD EQ LEFT( EV~VREFER, 9 )
  INTO corresponding fields of table GT_INSTMUNIC_F.

Note that the INTO clause needs to move to the end of the command as well.

SELECT with substring in ON clause?

3 Answers