Posted: Sun Aug 22, 2021 10:08 pm Post subject: Join between ekpo and lips
Before ABAP 7.50 possible :
1. FOR ALL ENTRIES
2. Execute "native" SQL directly on the database connected to your ABAP software. This can be done with EXEC SQL or ADBC (class CL_SQL_STATEMENT and so on), or AMDP if your database is HANA.
3.1 Create intermediate database table zekpo_lips_join
with 3 fields
mandt TYPE t001-mandt,
ebelp TYPE ekpo-ebelp,
vgpos TYPE lips-vgpos.
3.2. Fills the table with values
zekpo_lips_join-mandt = sy-mandt.
zekpo_lips_join-ebelp = 10.
zekpo_lips_join-vgpos = 10.
END-OF-SELECTION.
TYPES: BEGIN OF ty_joindata,
ebeln TYPE ekpo-ebeln,
ebelp TYPE ekpo-ebelp,
aedat TYPE ekpo-aedat,
matnr TYPE ekpo-matnr,
menge TYPE ekpo-menge,
netpr TYPE ekpo-netpr,
vgbel TYPE lips-vgbel,
vgpos TYPE lips-vgpos,
lfimg TYPE lips-lfimg,
END OF ty_joindata.
DATA: ls_joindata TYPE ty_joindata,
lt_joindata TYPE TABLE OF ty_joindata.
CONSTANTS: lc_count TYPE i VALUE 99999.
DATA: lt_zekpo_lips_join TYPE TABLE OF zekpo_lips_join,
ls_zekpo_lips_join TYPE zekpo_lips_join,
lv_count TYPE i.
SELECT COUNT(*) FROM zekpo_lips_join INTO lv_count.
IF lv_count <> lc_count + 1.
* example of filling zekpo_lips_join table
ls_zekpo_lips_join-mandt = sy-mandt.
APPEND ls_zekpo_lips_join TO lt_zekpo_lips_join.
DO lc_count TIMES.
ls_zekpo_lips_join-ebelp = sy-index.
ls_zekpo_lips_join-vgpos = sy-index.
APPEND ls_zekpo_lips_join TO lt_zekpo_lips_join.
ENDDO.
MODIFY zekpo_lips_join FROM TABLE lt_zekpo_lips_join.
COMMIT WORK AND WAIT.
ENDIF.
SELECT e~ebeln e~ebelp e~aedat e~matnr e~menge e~netpr
l~vgbel l~vgpos l~lfimg
INTO CORRESPONDING FIELDS OF TABLE lt_joindata
FROM ekpo AS e
INNER JOIN zekpo_lips_join AS j ON e~ebelp = j~ebelp
INNER JOIN lips AS l ON l~matnr = e~matnr
AND l~vgbel = e~ebeln
AND l~vgpos = j~vgpos
WHERE e~ebeln IN so_ebeln.
IF sy-subrc IS INITIAL.
LOOP AT lt_joindata INTO ls_joindata.
WRITE: / ls_joindata-ebeln,
ls_joindata-ebelp,
ls_joindata-vgbel,
ls_joindata-vgpos,
ls_joindata-matnr.
ENDLOOP.
ENDIF.
For ABAP >= 7.50, there are SQL string functions LIKE for instance SUBSTRING:
Code:
SELECT a~ebeln, a~ebelp, c~vbeln, c~posnr
FROM ekpo AS a
INNER JOIN lips AS c
ON c~vgbel = a~ebeln
AND substring( c~vgpos, 2, 5 ) = a~ebelp
INTO TABLE @DATA(gt_dummy).
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You cannot download files in this forum
All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG. SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG. Every effort is made to ensure content integrity. Use information on this site at your own risk.
