topic One Select query from two itabs in Application Development and Automation Discussions

One Select query from two itabs

ricky_shaw — Tue, 16 Jun 2020 05:00:36 GMT

Hi,

I have two internal tables : itab1 holding BKPF data & itab2 holding RSEG data.

BELNR is the common field on both of them.

Now i need to get data from BSEG using one select query based on BELNR'S from itab1 & itab2.

How do i do that?

Re: One Select query from two itabs

VenkatRamesh_V — Tue, 16 Jun 2020 05:06:36 GMT

try,

Both internal table contains same structure means use append lines of it_rseg to it_bkpf or

Select * from bseg into table it_bseg for all entries in it_bkpf

Select * from bseg appending table it_bseg for all entries in it_rseg.

Regards,

RAmesh

Re: One Select query from two itabs

former_member1716 — Tue, 16 Jun 2020 05:47:29 GMT

ricky.shaw,

You could try with the below code:

SORT: IT_BKPF, IT_RSEG.
* Also Try Deleting Adjacent Duplicates from the above two tables after sorting by comparing the required fields

IF it_bkpf[] IS NOT INITIAL.
  SELECT *
  FROM bseg
  INTO TABLE @DATA(it_bseg)
  FOR ALL ENTRIES IN @it_bkpf
  WHERE belnr = @it_bkpf-belnr and
        BUKRS = @it_bkpf-bukrs and  * Hope the field is in it_bkpf
        GJAHR = @it_bkbf-bukrs.     * Hope the field is in it_bkpf
  IF sy-subrc EQ 0.
    SORT it_bseg BY belnr.
  ENDIF.
ENDIF.

IF it_rseg[] IS NOT INITIAL.
  SELECT *
  FROM bseg
  APPENDING TABLE @it_bseg
  FOR ALL ENTRIES IN @it_rseg
  WHERE belnr = @it_rseg-belnr and
        BUKRS = @it_rseg-bukrs and  * Hope the field is in it_rseg
        GJAHR = @it_rseg-bukrs.     * Hope the field is in it_rseg
  IF sy-subrc EQ 0.
    SORT it_bseg BY belnr.
  ENDIF.
ENDIF.<br>

Regards!

Re: One Select query from two itabs

MateuszAdamus — Tue, 16 Jun 2020 06:35:29 GMT

Just don't forget to check if IT_BKPF and IT_RSEG have records. Otherwise this will return whole BSEG table.

As a rule of thumb, always check for records in table that is used in FOR ALL ENTRIES.

IF it_bkpf[] IS NOT INITIAL.
  ...
ENDIF.

IF it_rseg[] IS NOT INITIAL.
  ..
ENDIF.

Kind regards,
Mateusz

Re: One Select query from two itabs

former_member1716 — Tue, 16 Jun 2020 06:51:27 GMT

mateuszadamus,Code Corrected! Thanks!

Re: One Select query from two itabs

former_member16553 — Tue, 16 Jun 2020 06:55:54 GMT

Hi,

You can go for union as well if result structure is same.

Regards,

Girdhari

Re: One Select query from two itabs

former_member1716 — Tue, 16 Jun 2020 07:03:26 GMT

girdhari.mondal,Yes, but that has a dependency on the GUI version and database in use.

Re: One Select query from two itabs

ziolkowskib — Tue, 16 Jun 2020 07:04:15 GMT

Hi satishkumarbalasubramanian,
Please note that BELNR is not the full key of BSEG table and BELNR is not unique number thus please also include field GJAHR and BUKRS in your code sample - otherwise we will be answering questions like "why the code is returning incorrect documents".
Regards,
Bartosz

Re: One Select query from two itabs

ziolkowskib — Tue, 16 Jun 2020 07:10:01 GMT

Hi ricky.shaw,
If you wanted to have it in one SELECT statement I would suggest creating a temporary table (i.e. LT_BSEG_PARTIAL_KEY) consisting of key fields (BELNR, GJAHR, BUKRS) and populate it based on entries you earlier fetched from BKPF and RSEG. I would also suggest sorting that table and deleting adjacent duplicates from the table. And as mentioned by mateuszadamus please always check if your table is not empty before using it with FOR ALL ENTRIES statement.
Regards,
Bartosz

Re: One Select query from two itabs

former_member1716 — Tue, 16 Jun 2020 07:15:15 GMT

ziolkowskib,Yes you are right, Added the code but it is mentioned in the question only BELNR is the common field.

Re: One Select query from two itabs

Sandra_Rossi — Tue, 16 Jun 2020 09:54:59 GMT

Do you run S/4HANA or an old version of SAP ERP? BSEG is a transparent table in S/4HANA but a clustered table in older versions, that will do two completely different answers. (and eventually tell us your ABAP version so that to get an even more precise answer)

Re: One Select query from two itabs

Sandra_Rossi — Tue, 16 Jun 2020 10:47:35 GMT

As mentioned by Bartosz, the access to BSEG cannot be done based on BELNR only, it must be done based on the three primary key columns BELNR, GJAHR, BUKRS, otherwise you may retrieve several non-related lines from BSEG.