The Open SQL command is too large - issue

[Former Member]

Hi all,

SELECT matnr

          mtart

          matkl

          meins

     INTO TABLE it_mara

     FROM mara

     WHERE matnr IN s_matnr

       AND mtart IN s_mtart

       AND matkl IN s_mtart.

   LOOP AT it_mara INTO wa_mara.

     s_matnr1-low = wa_mara-matnr.

     s_matnr1-option = 'EQ'.

     s_matnr1-sign = 'I'.

     APPEND s_matnr1.

     clear: wa_mara, s_matnr1.

   ENDLOOP.

   SELECT

          matnr

          werks

          lgort

          SUM( labst ) AS labst

          SUM( insme ) AS insme

          SUM( retme ) AS retme

     INTO corresponding fields of table it_mard

     FROM mard

     WHERE matnr IN s_matnr1

       and werks in s_werks

       and lgort in s_lgort

       AND labst NE space

     GROUP BY matnr werks lgort.

After this statement getting the error message. - The Open SQL command is too large

When I give SINGLE MATERIAL in selection screen then logic executes successfully.

But when I give PLANT then there are so many records then then I am getting

Can anyone suggest how to solve the issue

[venkateswaran_k]

Hi

Frist,

SELECT matnr

          mtart

          matkl

          meins

     INTO TABLE it_mara

     FROM mara

     WHERE matnr IN s_matnr

       AND mtart IN s_mtart

       AND matkl IN s_mtart.   <-  why it is two times ?

Secondly,

After Appending to S_matnr1  --  remove the duplicates. 

Thirdly,

instead of APPEND why don't you try with COLLECT  for s_matrn1

Regards,

Venkat

[ThomasZloch]

-> try using a single select statement joining both tables MARA and MARD, or at least a FOR ALL ENTRIES instead of the range with too many single values.

Thomas

[former_member186413]

Hi, Kris

You should write your code as the following.

SELECT matnr

          mtart

          matkl

          meins

     INTO TABLE it_mara

     FROM mara

     WHERE matnr IN s_matnr

       AND mtart IN s_mtart

       AND matkl IN s_mtart.

* First Step Retric data from database via Array Frech

   SELECT

          matnr

          werks

          lgort

          SUM( labst ) AS labst

          SUM( insme ) AS insme

          SUM( retme ) AS retme

     INTO corresponding fields of table it_mard

     FROM mard

     FOR ALL ENTRIES IN it_mara

     WHERE

       werks in s_werks

       and lgort in s_lgort

       AND labst NE space

     GROUP BY matnr werks lgort.

[former_member186413]

Another option that you are able to take is write the code as follow.

   SELECT

          matnr

          werks

          lgort

          SUM( labst ) AS labst

          SUM( insme ) AS insme

          SUM( retme ) AS retme

     INTO corresponding fields of table it_mard

     FROM mard

     WHERE

        matnr IN ( SELECT matnr FROM mara WHERE  matnr IN s_matnr

                                                                                 AND mtart IN s_mtart

                                                                                  AND matkl IN s_mtart. )

       werks in s_werks

       and lgort in s_lgort

       AND labst NE space

     GROUP BY matnr werks lgort.

[former_member209120]

Hi Hai,

Some small correction...

SELECT

          matnr

          werks

          lgort

          SUM( labst ) AS labst

          SUM( insme ) AS insme

          SUM( retme ) AS retme

     INTO corresponding fields of table it_mard

     FROM mard

     WHERE

        matnr IN ( SELECT matnr FROM mara WHERE  matnr IN s_matnr

                                                                                 AND mtart IN s_mtart

                                                                                  AND matkl IN s_mtart. matkl in s_matkl )

       werks in s_werks

       and lgort in s_lgort

       AND labst NE space

     GROUP BY matnr werks lgort.

[former_member209120]

Hi Hai,

Pl. correct this

SELECT matnr

          mtart

          matkl

          meins

     INTO TABLE it_mara

     FROM mara

     WHERE matnr IN s_matnr

       AND mtart IN s_mtart

       AND matkl IN s_mtart.

[Former Member]

Hi Wang,

It works great.

You have given two kind of options.

1 Select inside select - works fine.

2. Using For all entries in - when i try to use this I am getting a error message

     "The addition FOR ALL ENTRIES" excludes all aggregate functions with the

     exception of "COUNT(*)" as the single element of the SELECT clause.

[former_member186413]

For seond option, the internal table may need to have Matnr as the table key.

[Former Member]

Hi Wang,

Can you explain little briefly with coding..

[former_member186413]

DATA: itab TYPE TABLE OF mara WITH KEY matnr.

SELECT.... INTO TABLE itab......

[Former Member]

Hi Wang,

I made it but still found the same issue..

Here I need mara for other select statements also.

So if we have groupby with for all entries then it could great helpfull.

[former_member209120]

Hi Krish,

Try like this...

SELECT matnr

          mtart

          matkl

          meins

     INTO TABLE it_mara

     FROM mara

     WHERE matnr IN s_matnr

       AND mtart IN s_mtart

       AND matkl IN s_mtart   s_matkl.      " (Material Group)

   LOOP AT it_mara INTO wa_mara.

     s_matnr1-low = wa_mara-matnr.

     s_matnr1-option = 'EQ'.

     s_matnr1-sign = 'I'.

     APPEND s_matnr1.

     clear: wa_mara, s_matnr1.

   ENDLOOP.

   SELECT

          matnr

          werks

          lgort

          SUM( labst ) AS labst

          SUM( insme ) AS insme

          SUM( retme ) AS retme

     INTO corresponding fields of table it_mard

     FROM mard

     for all entries in it_mara

     WHERE matnr IN s_matnr1
                    matnr = it_mara-matnr
             and werks in s_werks

            and lgort in s_lgort

         AND labst NE space

     GROUP BY matnr werks lgort.

[Former Member]

Hi Ramesh,

when i am using FOR ALL ENTRIES I am getting a error message like

     "The addition FOR ALL ENTRIES" excludes all aggregate functions with the

     exception of "COUNT(*)" as the single element of the SELECT clause.

[swen_hettstedt2]

Hy Krish,

the database management has only 64 kbyte to interpret the sql statement.

Is the sql statement too long throw the system a dump.

Try FOR ALL ENTRIES - Statement.