SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Join between ekpo and lips

Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> SD
View previous topic :: View next topic  
Author Message

Joined: 01 Sep 2007
Posts: 1639

PostPosted: Sun Aug 22, 2021 10:08 pm    Post subject: Join between ekpo and lips Reply with quote

Before ABAP 7.50 possible :

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.

zekpo_lips_join-ebelp = 20.
zekpo_lips_join-vgpos = 20.


zekpo_lips_join-ebelp = 99990.
zekpo_lips_join-vgpos = 099990.

3.3 Program example

REPORT  zjoin_ekpo_lips.

DATA: lv_ebeln TYPE ekpo-ebeln.

SELECT-OPTIONS: so_ebeln FOR lv_ebeln.

  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.

    MODIFY zekpo_lips_join FROM TABLE lt_zekpo_lips_join.

  SELECT e~ebeln e~ebelp e~aedat e~matnr e~menge e~netpr
         l~vgbel l~vgpos l~lfimg
  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,

For ABAP >= 7.50, there are SQL string functions LIKE for instance SUBSTRING:

 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).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> SD All times are GMT + 4 Hours
Page 1 of 1

Jump to:  
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.