topic Re: The Open SQL command is too large - issue in Application Development and Automation Discussions

The Open SQL command is too large - issue

Former Member — Wed, 10 Jul 2013 05:34:21 GMT

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

Re: The Open SQL command is too large - issue

venkateswaran_k — Wed, 10 Jul 2013 06:44:03 GMT

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

Re: The Open SQL command is too large - issue

ThomasZloch — Wed, 10 Jul 2013 07:53:25 GMT

-> 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

Re: The Open SQL command is too large - issue

former_member186413 — Wed, 10 Jul 2013 08:23:27 GMT

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.

Re: The Open SQL command is too large - issue

former_member209120 — Wed, 10 Jul 2013 08:28:09 GMT

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.

Re: The Open SQL command is too large - issue

former_member186413 — Wed, 10 Jul 2013 08:30:10 GMT

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.

Re: The Open SQL command is too large - issue

former_member209120 — Wed, 10 Jul 2013 08:34:13 GMT

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.

Re: The Open SQL command is too large - issue

former_member209120 — Wed, 10 Jul 2013 08:35:26 GMT

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.

Re: The Open SQL command is too large - issue

Former Member — Wed, 10 Jul 2013 09:02:32 GMT

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.

Re: The Open SQL command is too large - issue

swen_hettstedt2 — Wed, 10 Jul 2013 09:07:45 GMT

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.

Re: The Open SQL command is too large - issue

Former Member — Wed, 10 Jul 2013 09:08:13 GMT

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.

Re: The Open SQL command is too large - issue

former_member186413 — Thu, 11 Jul 2013 00:08:31 GMT

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

Re: The Open SQL command is too large - issue

Former Member — Thu, 11 Jul 2013 03:34:47 GMT

Hi Wang,

Can you explain little briefly with coding..

Re: The Open SQL command is too large - issue

former_member186413 — Thu, 11 Jul 2013 08:10:26 GMT

DATA: itab TYPE TABLE OF mara WITH KEY matnr.

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

Re: The Open SQL command is too large - issue

Former Member — Thu, 11 Jul 2013 10:12:27 GMT

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.