For All Entries, GTT and OPEN SQL

Hi!

Well, searched on SAP blogs and forums for something like this, I only found fragments here and there so I decided to create an article about one of the uses of Global Temporary Tables.

As you know, the addition WHERE… FOR ALL ENTRIES is not allowed in some situations as for example:

• The addition FOR ALL ENTRIES cannot be combined with SQL expressions.
• The addition FOR ALL ENTRIES should not be used with the addition GROUP BY.

From <https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abenwhere_logexp_itab.htm>

But let’s assume you need to use some SQL expressions or a GROUP BY, whatever, and you also need to do it using FOR ALL ENTRIES (FAE) in some internal table. What to do now?!

Well, as we know FOR ALL ENTRIES is a JOIN but using an internal table instead a database table, so, if we have the same records of our internal table in a database table we could use a JOIN instead the FAE, right!?

“With ABAP 7.50, you can also define GTTs in the ABAP Dictionary by setting the Table Category to Global Temporary Table.”

From <https://blogs.sap.com/2015/11/18/abap-news-for-release-750-insert-from-subquery-and-gtts/>

If we create a GTT with the same structure of our internal table, we can do our FAE using a JOIN since the JOIN is accepted.

In my example I needed to use a FAE on table MDUA (it’s view actually) using the fields MATNR, RESWK and WERKS.

Well, I started by creating a GTT called Z06503802_MDUA with those fields:

In the program I use INSERT command to include all values of my internal table into my GTT and then I use these new table in my SELECT to get all fields and calculations at once and using a “pseudo FOR ALL ENTRIES” / JOIN with all records that exists now in a temporary database table instead an internal table:

      TYPES:
        BEGIN OF ty_s_mdua_fae,
          matnr TYPE matnr,
          reswk TYPE werks_d,
          werks TYPE werks_d,
        END OF ty_s_mdua_fae,

        ty_t_mdua_fae TYPE SORTED TABLE OF ty_s_mdua_fae
                      WITH NON-UNIQUE KEY matnr reswk werks.

      DATA(tl_mdua_fae) = VALUE ty_t_mdua_fae(
                            FOR <fs_z06503801> IN t_z06503801
                      ( matnr = <fs_z06503801>-matnr_new
                        reswk = <fs_z06503801>-werksup_new
                        werks = <fs_z06503801>-werks_new ) ).

      IF tl_mdua_fae[] IS NOT INITIAL.

        DELETE ADJACENT DUPLICATES FROM tl_mdua_fae COMPARING matnr reswk werks.

        INSERT z06503802_mdua FROM TABLE @tl_mdua_fae.

        SELECT DISTINCT
                m~matnr, m~reswk, m~banfn, m~bnfpo,
                m~menge, m~bsmng, m~meins, m~lfdat,
                coalesce( y~werks, m~reswk ) AS werks,
                coalesce( y~spwoc, @week ) AS spwoc,
                coalesce( y~statu, @c_producao ) AS statu,
                coalesce( y~recpc, @space ) AS recpc,
                coalesce( y~dtrpc, @l_dtrpc ) AS dtrpc,

                CASE WHEN y~recpc IS NULL THEN @abap_true
                     ELSE @abap_false
                END AS flag,

                menge - bsmng AS saldo

                FROM z06503802_mdua AS z
                JOIN mdua AS m         ON m~matnr EQ z~matnr
                                      AND m~reswk EQ z~reswk
                                      AND m~werks EQ z~werks
                LEFT JOIN zsd0254 AS y ON y~banfn EQ m~banfn
                                      AND y~bnfpo EQ m~bnfpo
                                      AND y~matnr EQ m~matnr
               WHERE m~pstyp EQ @c_transf_estoque
                 AND m~knttp EQ @space
               GROUP BY m~matnr, m~reswk, m~banfn, m~bnfpo,
                        m~menge, m~bsmng, m~meins, m~lfdat, y~werks,
                        y~spwoc, y~statu, y~recpc, y~dtrpc
              HAVING m~menge > m~bsmng
              INTO TABLE @t_mdua.

        DELETE FROM z06503802_mdua.

      ENDIF.

What I’m doing with this SELECT is to assume some default values by using coalesce, an SQL function that returns the values of the first argument, which does not have the null value which means that when I’m not find the corresponding row at the table ZSD0254 coalesce will return the second value.

The instruction CASE is doing the same thing, returning “X” when I’m not finding the row in the ZSD0254 table.

The HAVING clause is filtering the records, I just want to process the lines where Quantity (menge) is bigger than the Quantity ordered.

With this new feature we can even totally forget about FAE and if you want it you can use GTT all the time in all database manipulations that requires a FAE in some internal table.

I don’t know about the performance costs, I didn’t any measure but I think there will be some gain.

Please let me know If this article was useful for you.

Thank you,
Ivan Milazzotti