Problem with Query: "SQL Error SQL CODE: 4"

Question

I was trying to execute this SQL statement within my report and I got this error:

Is there any way to improve this query so it fetch data faster?

 SELECT    bs~bukrs,bs~gjahr, bs~h_monat , bt~mwskz, bs~belnr,
                bs~h_bldat, bs~h_budat,bt~hwbas, bt~hwste,
                bt~fwbas,bt~fwste


       FROM bset AS bt
       INNER JOIN bseg AS bs ON
       bs~bukrs = bt~bukrs AND
       bs~belnr = bt~belnr AND
       bs~gjahr = bt~gjahr

       INNER JOIN t007a AS t ON
       bs~mwskz = t~mwskz

       INNER JOIN a003 AS a ON
        t~mwskz = a~mwskz

       INNER JOIN t683s AS ts ON
       a~kschl = ts~kschl

       INNER JOIN t030k AS tk ON
       ts~kvsl1 = tk~ktosl AND
       bs~hkont = tk~konts

    WHERE bs~bukrs IN @so_bukrs
    AND h_monat EQ @p_monat
    AND bs~gjahr EQ @p_gjahr

    GROUP BY  bs~bukrs,bs~gjahr,  bt~hwbas, bt~fwbas,
              bs~h_bldat, bs~h_budat, bt~mwskz, bs~belnr,
              bt~shkzg,bt~hwste, bt~fwste, bt~mwskz,bs~h_monat

    ORDER BY bs~belnr
     INTO CORRESPONDING FIELDS OF TABLE @it_alv.

Answer 1

This error says that your database internally ran out of memory while executing your query. This can happen when you JOIN and GROUP with too many tables. It happens because it can require that all those tables plus various intermediate result sets being required to be in memory simultaneously.

The solution would be to move some logic from the database to the application server. JOINS can be simulated by selecting from only one table, doing a LOOP AT over that table, and then SELECTing the corresponding data for each row separately. GROUP BY can be implemented by looping again through those result sets and building the sum table on your own. Both of these techniques will usually be a lot slower than doing these things on the database. But at least they are going to work at all.

However, looking closer at your SQL statement, there doesn't really seem to be a good reason why you are joining the tables T007A, A003, T683S and T030K. Their values don't seem to be used anywhere. They might be needed if you want to use them to exclude any rows which do not have a corresponding row in these tables. But with most of those tables, that should not be happening. So unless you have a good reason for joining these tables, I would try to remove them.

Answer 2

Many-to-many join

Your first 2 tables are item/position tables and you aren't using positions IDs (BUZEI) anywhere in your join condition. This creates a many-to-many join resulting in exponential result bloat.

I don't know FI data very well, so my suggestion is to focus on those 2 tables. Figure out if they have the same position IDs and how they overlap, then you'll have a better idea if you can use a join on BSEG~BUZEI = BSET~BUZEI.

Possible solution

My assumption is BSEG has all positions (let's say 1, 2, 3) and BSET only has taxed ones (1, 2, but not 3), but I might be wrong. If they do not overlap and position IDs are unique across 2 tables (i.e. BSEG has 1,2,3 and BSET has 4,5) the solution below doesn't work.

If it's the former and my assumption is correct you could perform BSEG left join BSET on <your conditions> + BSEG~BUZEI left join BSET~BUZEI (note the left join). Keep in mind left join means sometimes BSET values will be null so you'd have to focus all your filters on BSEG or account for null values in BSET conditions.