Search code examples
abapsap-erpopensql

Using the table BSEG in a selection without INNER JOIN


I am trying to do a selection of the field KOART from the table BSEG, by using the below code:

CONSTANTS: lc_d TYPE c VALUE 'D'.
    SELECT k~bukrs,
         k~belnr,
         k~budat,
         k~gjahr,
         k~buzei,
         k~kunnr,
         i~banks,
         i~bankl,
         i~bankn
    FROM bsid AS k
    INNER JOIN knbk AS i ON k~kunnr EQ i~kunnr
*    INNER JOIN bseg
    INTO TABLE @DATA(lt_data_knbk)
    WHERE k~bukrs IN @so_bukrs
    AND k~budat IN @so_budat
    AND k~blart IN @so_blart
*    AND bseg~koart = @lc_d.

Normally I would have put the Inner join syntax for the table, but being that bseg is a cluster table, it does not accept the inner join option, and thus I cannot use the field koart.

May anyone know, how can I put the field KOART of the BSEG in the selection above?

Thank you very much in advance for your kind help!


Solution

  • As explained by József Szikszai, an inner join is not actually necessary in this particular use-case, because the table BSID contains only customer documents anyway.

    But if it were necessary here, then you would have two options:

    The first would be to switch to SAP HANA as database backend, because then all the cluster tables will be converted to regular tables. You won't get around this in the long-term due to SAP announcing that they will be charging extra for supporting other databases from 2027 and drop support for non-HANA in 2030 completely. But due to the cost and planning effort involved in a HANA conversion, this is likely not a serious option if you are facing this problem today.

    When you need a temporary solution until your inevitable HANA conversion, then there is a second option. You can solve this problem using a second select with a FOR ALL ENTRIES condition.

    SELECT bukrs,
         belnr,
         budat,
         gjahr,
         buzei,
         kunnr,
      FROM bsid     
      INTO TABLE @DATA(lt_data_bsid)
      WHERE bukrs IN @so_bukrs
      AND budat IN @so_budat
      AND blart IN @so_blart.
    
    IF lt_data_bsid IS NOT INITIAL.
      SELECT bukrs,
             belnr,
             gjahr,
             buzei
        FROM bseg
        INTO TABLE @DATA(lt_bseg)
        FOR ALL ENTRIES IN @lt_data_bsid
        WHERE bukrs = @lt_data_bsid-bukrs
          AND belnr = @lt_data_bsid-belnr
          AND gjahr = @lt_data_bsid-gjahr
          AND buzei = @lt_data_bsid-buzei
          AND koart = @lc_d.
             
    ENDIF.
    

    Note that this approach has a number of problems:

    • You now have the data in two internal tables instead of one, so you might have to perform a couple READ TABLE's or LOOP AT... WHERE's to puzzle the data together.
    • Two SELECTs instead of one mean two network roundtrips to the database server instead of one. You also miss out on most internal optimizations your database might be able to do for JOINs. Which increases the runtime of your program.
    • The first SELECT might return a ton of data you don't need, so you might run into memory problems. You can solve that by using a database cursor (OPEN CURSOR) and FETCH the results in packets. But that will degrade performance even further.
    • Never forget the IF itab1 IS NOT INITIAL check when using FOR ALL ENTRIES. Intuitively, one would assume to receive no database rows at all if the lookup table is empty. But what actually will happen is that the WHERE conditions which mention the FOR ALL ENTRIES table get ignored completely and you receive all the table rows with koart = 'D'.

    But if you have to do a JOIN with a cluster-table, then there is unfortunately no other option.