Background job failing due to high data in select options used in a select statement.

[arijitbarman]

Dear Abapers,

I have a background job report supposed to run daily which is failing for some days. Debugged and found out that in a particular select statement it is failing when select-options having more than 5500 records in it. I think select-options has some data limitations and need to be split and use it in select accordingly. I am sharing the code for you. Please give me some suggestions.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME.
PARAMETERS: P_DATE LIKE SY-DATUM OBLIGATORY.
SELECT-OPTIONS: SO_MATNR FOR MARA-MATNR NO-DISPLAY.
SELECTION-SCREEN END OF BLOCK B1.

LOOP AT GT_DRAD INTO GS_DRAD.
SO_MATNR-SIGN = 'I'.
SO_MATNR-OPTION = 'EQ'.
SO_MATNR-LOW = GS_DRAD-MATNR.
APPEND SO_MATNR. [Above 5000 data]
CLEAR SO_MATNR.
ENDLOOP.

Select statement which is failing.

SELECT MATNR WERKS KZKRI DISPO
FROM MARC
INTO TABLE GT_MARC
FOR ALL ENTRIES IN GT_ZMMPACOCM
WHERE WERKS EQ GT_ZMMPACOCM-WERKS
AND DISPO EQ GT_ZMMPACOCM-DISPO
AND MATNR IN SO_MATNR.

I think i need to split so_matnr data and use accordingly in the below select statement. Please advise me.

[kiran_k8]

Kindly go through this.

https://archive.sap.com/discussions/thread/286011

Also kindly make a note to search in SCN,.

K.Kiran.

[NTeunckens]

Hello

Please check SAP-KBA 635318 on the limitations of SQL-commands ...

Extract :

"Constructs with SELECT-OPTIONS (or RANGE tables), for example, "WHERE id IN itab", look completely different in the database.

....

The length of the statement in the database must not exceed 28672 characters (with Unicode, 14336 characters)."

Please check this via Tracing ...

Kind regards

Nic T.

[matt]

It seems crazy to me to take a list of materials and build a range object (so_matnr), solely to be able to do IN so_matnr.

I don't know how you're filling GT_DRAD and GT_ZMMPACOCM, but if they're coming from straight database selects, you should combine all your selects into a single select, using INNER JOIN, and completely get rid of SO_MATNR.

[raghug]

If your GT_ZMMPACOCM does not have any data in it, you could be returning all the rows in MARC. See the Keyword help http://help-legacy.sap.com/abapdocu_702/en/abenwhere_logexp_itab.htm.

Specifically the second paragraph, quoting - "If the internal table itab is empty, the entire WHERE condition is ignored and all rows from the database are placed in the result set".

[Former Member]

Hi,

Due to select-option limitation(>5000) program is going to dump. Loop the select-option SO_MATNR and keep the material numbers in a separate internal table and use this internal table to fetch the MARC data based on material numbers. Later you filter the records based on "WERKS" and "DISPO" it will work.

Regards,

Shashi