Internal tables - UNION in ABAP

[a-ramesh]

Hi,

We are on 740 SP12. The requirement is to generate a report to compare between SAP data and Legacy system data.

Searching did not yield any results. Answers kept pointing to SQL SELECT statements.

There could be a mismatch in number of records between the two systems, for a given order.

I have a solution using intermediate tables, but am wondering if there is a better way to do it without intermediate tables using the new ABAP syntax. For example, using FOR, REDUCE etc.

This is what I have that is giving me the correct results. Looking for a cleaner approach.

Any help is much appreciated.

REPORT ztest_itab_union.
TYPES:
  BEGIN OF lty_leg_bol,
    order TYPE vbeln,
    bolnr TYPE vbeln,
  END OF lty_leg_bol,
  lty_t_leg_bol TYPE STANDARD TABLE OF lty_leg_bol WITH EMPTY KEY,
  BEGIN OF lty_sap_bol,
    order TYPE vbeln,
    vbeln TYPE vbeln,
  END OF lty_sap_bol,
  lty_t_sap_bol TYPE STANDARD TABLE OF lty_sap_bol WITH EMPTY KEY,
  BEGIN OF lty_report,
    order TYPE vbeln,
    bolnr TYPE vbeln,
    vbeln TYPE vbeln,
  END OF lty_report,
  lty_t_report TYPE STANDARD TABLE OF lty_report WITH EMPTY KEY.

DATA:
  lt_report  TYPE lty_t_report.

START-OF-SELECTION.
  DATA(lt_leg_bol) = VALUE lty_t_leg_bol(
                       ( order = 'ORD001' bolnr = 'LEG101' )
                       ( order = 'ORD001' bolnr = 'LEG102' )
                       ( order = 'ORD002' bolnr = 'LEG201' )
                       ( order = 'ORD002' bolnr = 'LEG202' )
                       ( order = 'ORD002' bolnr = 'LEG203' ) ).

  DATA(lt_sap_bol) = VALUE lty_t_sap_bol(
                       ( order = 'ORD001' vbeln = 'SAP101' )
                       ( order = 'ORD001' vbeln = 'SAP102' )
                       ( order = 'ORD001' vbeln = 'SAP103' )
                       ( order = 'ORD002' vbeln = 'SAP201' )
                       ( order = 'ORD002' vbeln = 'SAP202' )
                     ).

  LOOP AT lt_leg_bol ASSIGNING FIELD-SYMBOL(<lfs_leg_bol>).

    AT END OF order.
      DATA(lt_leg_temp) = VALUE lty_t_leg_bol(
        FOR wa_leg IN lt_leg_bol WHERE ( order = <lfs_leg_bol>-order ) ( wa_leg ) ).

      DATA(lt_sap_temp) = VALUE lty_t_sap_bol(
        FOR wa_sap IN lt_sap_bol WHERE ( order = <lfs_leg_bol>-order ) ( wa_sap ) ).

      IF lines( lt_leg_temp ) GT lines( lt_sap_temp ).

        DO lines( lt_leg_temp ) - lines( lt_sap_temp ) TIMES.
          APPEND INITIAL LINE TO lt_sap_temp ASSIGNING FIELD-SYMBOL(<lfs_sap_temp>).
          <lfs_sap_temp>-order = <lfs_leg_bol>-order.
        ENDDO.

      ELSE.

        DO lines( lt_sap_temp ) - lines( lt_leg_temp ) TIMES.
          APPEND INITIAL LINE TO lt_leg_temp ASSIGNING FIELD-SYMBOL(<lfs_leg_temp>).
          <lfs_leg_temp>-order = <lfs_leg_bol>-order.
        ENDDO.

      ENDIF.

      DATA(lt_report_temp) = VALUE lty_t_report(
          FOR wa1 IN lt_leg_temp INDEX INTO idx
          ( order = wa1-order
            bolnr = wa1-bolnr
            vbeln = lt_sap_temp[ idx ]-vbeln )
        ).

      APPEND LINES OF lt_report_temp TO lt_report.
      REFRESH lt_report_temp.

    ENDAT.

  ENDLOOP.

  DATA(out) = cl_demo_output=>new( ).

  out->write( lt_report ).

  out->display( ).

and the correct result:

Thank you.

With best regards.

[Sandra_Rossi]

You should use the algorithm "sort-merge join", i.e. first sort both tables by the fields of the primary key, then read the line sequentially, one or the other, or the two of them at each loop, depending on the values of the key fields of the last read line of each table.

Why do you focus on latest ABAP language, if the issue is about the algorithm.

[a-ramesh]

Hi Sandra,

Thank you for your reply. Honestly, I am not aware of the "sort-merge join" algorithm. I will look into it. Checking on google shows some examples. Have to figure out how to translate that to ABAP.

I am attaching the actual initial code that I wrote, which is working as expected for the scenarios that we have. Wanted to see if it can be done more efficiently with fewer lines of code using the new expressions introduced for internal tables.

REPORT ztest_itab_union.
TYPES:
  BEGIN OF lty_leg_bol,
    order TYPE vbeln,
    bolnr TYPE vbeln,
  END OF lty_leg_bol,
  lty_t_leg_bol TYPE SORTED TABLE OF lty_leg_bol WITH UNIQUE KEY order bolnr,
  BEGIN OF lty_sap_bol,
    order TYPE vbeln,
    vbeln TYPE vbeln,
  END OF lty_sap_bol,
  lty_t_sap_bol TYPE SORTED TABLE OF lty_sap_bol WITH UNIQUE KEY order vbeln,
  BEGIN OF lty_report,
    order TYPE vbeln,
    bolnr TYPE vbeln,
    vbeln TYPE vbeln,
  END OF lty_report,
  lty_t_report TYPE STANDARD TABLE OF lty_report WITH EMPTY KEY.

DATA:
  l_sap_tabix TYPE sy-tabix,
  lt_report   TYPE lty_t_report,
  lwa_report  LIKE LINE OF lt_report.

START-OF-SELECTION.
  DATA(lt_leg_bol) = VALUE lty_t_leg_bol(
                       ( order = 'ORD000' bolnr = ' ' )
                       ( order = 'ORD001' bolnr = 'LEG101' )
                       ( order = 'ORD001' bolnr = 'LEG102' )
                       ( order = 'ORD002' bolnr = 'LEG201' )
                       ( order = 'ORD002' bolnr = 'LEG202' )
                       ( order = 'ORD002' bolnr = 'LEG203' ) ).


  DATA(lt_sap_bol) = VALUE lty_t_sap_bol(
                       ( order = 'ORD000' vbeln = 'SAP001' )
                       ( order = 'ORD000' vbeln = 'SAP002' )
                       ( order = 'ORD001' vbeln = 'SAP101' )
                       ( order = 'ORD001' vbeln = 'SAP102' )
                       ( order = 'ORD001' vbeln = 'SAP103' )
                       ( order = 'ORD002' vbeln = 'SAP201' )
                       ( order = 'ORD002' vbeln = 'SAP202' )
                     ).

  LOOP AT lt_leg_bol ASSIGNING FIELD-SYMBOL(<lfs_leg_bol>).
    AT NEW order.
      READ TABLE lt_sap_bol TRANSPORTING NO FIELDS
                 WITH KEY order = <lfs_leg_bol>-order.
      IF sy-subrc EQ 0.
        l_sap_tabix = sy-tabix - 1.
      ENDIF.
    ENDAT.

    CLEAR lwa_report.
    MOVE-CORRESPONDING <lfs_leg_bol> TO lwa_report.

    ADD 1 TO l_sap_tabix.
    READ TABLE lt_sap_bol ASSIGNING FIELD-SYMBOL(<lfs_sap_bol>)
                          INDEX l_sap_tabix.
    IF sy-subrc EQ 0.
      IF <lfs_sap_bol>-order EQ <lfs_leg_bol>-order.
        lwa_report-vbeln = <lfs_sap_bol>-vbeln.
      ELSE.
        SUBTRACT 1 FROM l_sap_tabix.
      ENDIF.
    ELSE.
      SUBTRACT 1 FROM l_sap_tabix.
    ENDIF.

    APPEND lwa_report TO lt_report.

    AT END OF order.
* Get any Remaining SAP records for this Legacy order
      ADD 1 TO l_sap_tabix.
      LOOP AT lt_sap_bol ASSIGNING <lfs_sap_bol> FROM l_sap_tabix.
        IF <lfs_sap_bol>-order NE <lfs_leg_bol>-order.
          EXIT.
        ENDIF.
        CLEAR lwa_report.
        MOVE-CORRESPONDING <lfs_sap_bol> TO lwa_report.
        APPEND lwa_report TO lt_report.
      ENDLOOP.
    ENDAT.

  ENDLOOP.

  DATA(out) = cl_demo_output=>new( ).

  out->write( lt_report ).

  out->display( ).

With best regards,

-Ramesh

[Jelena]

+1 to Sandra - this is just the matter of understanding what you need to do exactly (aka the algorithm). Using a specific syntax is a secondary task.

There are many examples available in ABAP Editor. I'm pretty sure they cover the most common scenarios, such as combining data in the internal tables. You'll find the examples in the menu in SE38. Also check ABAPDOCU transaction.

[a-ramesh]

Hi Jelena,

Thank you for your reply. I apologize that I was not clear in my question. Here is the background.

I did have a solution for my result with the following logic to get my LEFT OUTER JOIN.

  LOOP AT lt_leg_bol ASSIGNING FIELD-SYMBOL(<lfs_leg_bol>).

    CLEAR lwa_report.

    MOVE-CORRESPONDING <lfs_leg_bol> TO lwa_report.

    READ TABLE lt_sap_bol ASSIGNING FIELD-SYMBOL(<lfs_sap_bol>)
                          WITH KEY order = <lfs_leg_bol>-order
                                   found = abap_false.
    IF sy-subrc EQ 0.
      <lfs_sap_bol>-found = abap_true.
      lwa_report-vbeln = <lfs_sap_bol>-vbeln.
    ENDIF.

    APPEND lwa_report TO lt_report.

    AT END OF order.
* Get any Remaining SAP records for this Legacy order
      LOOP AT lt_sap_bol ASSIGNING <lfs_sap_bol>
                         WHERE order EQ <lfs_leg_bol>-order
                         AND   found EQ abap_false.
        <lfs_sap_bol>-found = abap_true.
        CLEAR lwa_report.
        MOVE-CORRESPONDING <lfs_sap_bol> TO lwa_report.
        APPEND lwa_report TO lt_report.
      ENDLOOP.
    ENDAT.

  ENDLOOP.

Since LT_SAP_BOL is a SORTED table, I did not like the read on it without a key field.

My second attempt was the code that I posted in Sandra's reply.

Looking for ways to improve performance, I started looking at new syntax in Horst Keller's blogs. One statement that caught my eye was the execution of internal table expressions on the kernel, making it faster.

Hence my quest to see if I can use the new syntax to achieve my results with better performance.

I was hoping that there is a way to do something like the following code, but, I am not well versed with the new syntax yet, thus, reaching out to the community to help me learn.

* Wishful thinking
lt_report = REDUCE ( FOR lt_leg_bol.......
                     FOR lt_sap_bol....... ).

Not sure if I can achieve this, but trying to explore.

Looking for an algorithm is a No and Yes.

No, because I already have one.

Yes, because am looking to see if I can use the new faster and concise syntax.

Thank you for your time.

With best regards,

-Ramesh

PS: Still working on the "sort-merge join" based on Sandra's recommendation and getting close.

This is what I have, but, missing the last record in the result ( ORD002/LEG203/<Blank> ).

  l_leg_tabix = l_sap_tabix = 1.

  WHILE ( l_leg_tabix LE lines( lt_leg_bol ) AND
          l_sap_tabix LE lines( lt_sap_bol ) ).

    READ TABLE lt_leg_bol INTO lwa_leg_bol INDEX l_leg_tabix.

    READ TABLE lt_sap_bol INTO lwa_sap_bol INDEX l_sap_tabix.

    IF lwa_leg_bol-order EQ lwa_sap_bol-order.

      CLEAR lwa_report.
      MOVE-CORRESPONDING lwa_leg_bol TO lwa_report.
      MOVE-CORRESPONDING lwa_sap_bol TO lwa_report.
      APPEND lwa_report TO lt_report.

      ADD 1 TO: l_leg_tabix, l_sap_tabix.

    ELSEIF lwa_leg_bol-order LT lwa_sap_bol-order.

      CLEAR lwa_report.
      MOVE-CORRESPONDING lwa_leg_bol TO lwa_report.
      APPEND lwa_report TO lt_report.

      ADD 1 TO l_leg_tabix.

    ELSE.

      CLEAR lwa_report.
      MOVE-CORRESPONDING lwa_sap_bol TO lwa_report.
      APPEND lwa_report TO lt_report.
      ADD 1 TO l_sap_tabix.

    ENDIF.

  ENDWHILE.