topic Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001 in Application Development and Automation Discussions

SQL0101N The statement is too long or too complex. SQLSTATE=54001

NooruBohra — Mon, 10 Feb 2014 07:44:59 GMT

Hi Everyone,

I am trying to fetch data from BKPF using AWKEY & XBLNR but system is giving me a dump. Description of DUMP is below

Runtime Errors DBIF_RSQL_SQL_ERROR

Except. CX_SY_OPEN_SQL_DB

Date and Time 10.02.2014 13:00:48

Short text

SQL error in the database when accessing a table.

How to correct the error

Database error text........: "SQL0101N The statement is too long or too complex. SQLSTATE=54001"

Internal call code.........: "[RSQL/OPEN/BKPF]"

Please check the entries in the system log (Transaction SM21).

IF gt_mkpf IS NOT INITIAL.

SELECT bukrs belnr gjahr xblnr awkey FROM bkpf INTO TABLE gt_bkpf_n " This line raises the error

FOR ALL ENTRIES IN gt_mkpf

WHERE xblnr = gt_mkpf-xblnr

AND awkey IN lt_awkey.

ENDIF.

Please Help me how to solve the error.

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

arivazhagan_sivasamy — Mon, 10 Feb 2014 07:58:17 GMT

Hi,

Check the internal table content like below..

IF gt_mkpf[ ] IS NOT INITIAL.

If still you are getting same dump, try to restrict the selection using primary key fields.

Arivazhagan S.

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

NooruBohra — Mon, 10 Feb 2014 08:05:42 GMT

Hi Arivazhagan Sivasamy,

Thanks for reply.

gt_mkpf is an internal table without header line so that won't the problem.
As per the requirement restriction can only be possible by these two fields.

Regards,
Nooruddin Bohra

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

arivazhagan_sivasamy — Mon, 10 Feb 2014 08:08:38 GMT

Then create secondary index for those two fields(xblnr, awkey).

Arivazhagan s

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

Former Member — Mon, 10 Feb 2014 08:21:40 GMT

Hi,

I think you need to check lt_awkey.

I think lt_awkey is ranges. Also I believe the number of entries should not be more than 2000 in lt_awkey.

Regards

Sajid

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

NooruBohra — Mon, 10 Feb 2014 08:46:50 GMT

Hi Shaik Sajid,

lt_awkey contains around 15000 entries. Is it the cause?

Regards,
Nooruddin

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

Former Member — Mon, 10 Feb 2014 08:55:15 GMT

Hi,

If lt_awkey is of type Ranges then yes that's the cause.

Regards

Sajid

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

NooruBohra — Mon, 10 Feb 2014 09:25:33 GMT

Hi Shaik Sajid,

Can you suggest any way to improve so that query don't give me dump.

Regards,

Nooruddin

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

Former Member — Mon, 10 Feb 2014 09:44:17 GMT

Following is one way to avoid this dump:

Get the No of entries in the table lt_awkey and divide by 2000.

describe lt_awkey lines n.

var_n = n / 2000.

do var_n times.
move the first 2000 entries of lt_awkey into lt_awkey_dummy during first iteration and move next 2000 during second iteration into lt_awkey_dummy

IF gt_mkpf IS NOT INITIAL.

SELECT bukrs belnr gjahr xblnr awkey FROM bkpf appending TABLE gt_bkpf_n

FOR ALL ENTRIES IN gt_mkpf

WHERE xblnr = gt_mkpf-xblnr

AND awkey IN lt_awkey_dummy.

ENDIF.
enddo.

sort gt_bkpf_n by bukrs belnr gjahr.
delete duplicate entries gt_bkpf_n comparing bukrs belnr gjahr.

Regards

Sajid

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

NooruBohra — Mon, 10 Feb 2014 09:53:19 GMT

Hi Shaik Sajid,

Thanks for suggestion but don't you think it will increase the runtime and performance will gets decreased.

Regards,

Nooruddin

Re: SQL0101N The statement is too long or too complex. SQLSTATE=54001

Former Member — Mon, 10 Feb 2014 11:46:32 GMT

Hi,

Yes this would increase the run time and has effect on performance.

The Solution which I have provided can be used when there is no other way 🙂

Regards

Sajid