topic Re: What is wrong with this SQL statement? in Application Development and Automation Discussions

What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:01:03 GMT

What is wrong with this SQL statement? abap dump generated

checking through ST11...found this..

ERROR => max. statement length (65536) exceeded

there are

114 records in r_blart

44 records in itab

in this selection statement


    SELECT
      bukrs
      belnr
      gjahr
      kunnr
      blart
      buzei
      budat
      bldat
      mansp
      shkzg
      xblnr
      bschl
      dmbtr
      zuonr
      INTO TABLE i_bsid
      FROM bsid
      FOR ALL ENTRIES IN itab
      WHERE bukrs = itab-bukrs
        AND kunnr = itab-kunnr
        AND budat BETWEEN backdate_13 AND p_r_date
        AND blart IN r_blart
        AND zuonr IN szuonr.

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:03:27 GMT

Hi,

Put a bracket in the line:

AND budat ( BETWEEN backdate_13 AND p_r_date )

and check.

Regards,

Anji

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:04:23 GMT

hi Charles,

Try including all the key fileds in where condition of your select statement ..

dump could be here


BETWEEN  ( backdate_13 AND p_r_date )

Regards,

Santosh

Message was edited by:

Santosh Kumar Patha

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:05:37 GMT

Please add all keys IN WHERE CLAUSE which is defines in BSID table.

SELECT

bukrs

belnr

gjahr

kunnr

blart

buzei

budat

bldat

mansp

shkzg

xblnr

bschl

dmbtr

zuonr

INTO TABLE i_bsid

FROM bsid

FOR ALL ENTRIES IN itab

WHERE bukrs = itab-bukrs

AND kunnr = itab-kunnr

AND budat BETWEEN backdate_13 AND p_r_date

AND blart IN r_blart

AND zuonr IN szuonr.

KEYS:BUKRS,KUNNR,UMSKS,UMSKZ,AUGDT,AUGBL,ZUONR,GJAHR,BELNR,BUZEI

**Please reward suitable points***

With Regards

Navin Khedikar

Re: What is wrong with this SQL statement?

sreeramkumar_madisetty — Fri, 16 Mar 2007 11:08:33 GMT

Wrong in the Between statement.

Check it out once and correct that.

Regards,

kumar

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:10:11 GMT

but how come if there are

43 records in itab

..there will be no dump?

the dump is something to do with the length of the SQL statement?

as mentioned in ST11?

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:12:04 GMT

i'm not able to add in key fields... in my case

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:14:34 GMT

this is wrt the buffer size.

SO_SNDBUF ( 65536)

Sets the send buffer size for this socket. This option is used by the platform's networking code as a hint for the size to set the underlying network I/O buffers.

Increasing the buffer size can increase the performance of network I/O for high-volume connection, while decreasing it can help reduce the backlog of incoming data.

regards,

madhu

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 11:35:51 GMT

select the data with all the key fields,

BUKRS

KUNNR

UMSKS

UMSKZ

AUGDT

AUGBL

ZUONR

GJAHR

BELNR

BUZEI

regards,

Sujatha.

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 12:17:46 GMT

Hi Charles

Have you tried this select with fewer values in R_BLART and ITAB? As I understand it (and I could be completely wrong), this problem can occur when the SAP compiler translates the ABAP SELECT statement to native SQL. It creates the SQL select statement in one big, long string, which is then passed to the database. The problem can happen when you use a FOR ALL ENTRIES IN statement or ranges, because these are basically converted into one big WHERE clause. In your case:

WHERE ( bukrs = itab-bukrs[1] AND
        kunnr = itab-kunnr[1] )
   OR ( bukrs = itab-bukrs[2] AND
        kunnr = itab-kunnr[2] )
   OR ...
   OR ( bukrs = itab-bukrs[44] AND
        kunnr = itab-kunnr[44] )

Similarly, the range is also expanded:

   AND blart = r_blart[1]
    OR blart = r_blart[2]
    OR ...
    OR blart = r_blart[114]

So, while the ABAP SELECT statement might be fine, it could cause the SQL string to overflow and give these wonderfully cryptic short-dumps.

As I say, I could be entirely wrong about this, and if my wrongness offends any true techies out there, then I only hope they can forgive me. Or point out the error of my ways.

Cheers

Lyal

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 14:50:13 GMT

yes, i suspect that too... something to do with the SQL length

i wonder if there is any way to solve this?

i'm doing performance tuning for a report which does a inner join for

kna1, kpb1, bkpf and bsid...

the report later does another inner join for

kna1, kpb1, bkpf and bsad...with the same criteria and combines both itab into a big itab..

these 2 inner joins can take up for several hours for huge data... (selection for 13months)

so, i'm trying to break this up into more efficient statements...

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 15:06:09 GMT

Hi Charles,

There is a limitation for number of entries in the ranges when used in a select statement ( without for all entries addition). I think it accepts only up to 1000 .

Since you are using ranges along with the for all entries, it may mean the same.

Is there any possibility to have the r_blart and szuonr values also in the ITAB ?

    SELECT
      bukrs
      belnr
      gjahr
      kunnr
      blart
      buzei
      budat
      bldat
      mansp
      shkzg
      xblnr
      bschl
      dmbtr
      zuonr
      INTO TABLE i_bsid
      FROM bsid
      FOR ALL ENTRIES IN itab
      WHERE bukrs = itab-bukrs
        AND kunnr = itab-kunnr
        AND budat BETWEEN backdate_13 AND p_r_date
        AND blart = itab-blart
        AND zuonr = itab-szuonr.

<b></b>

Regards,

Ravi

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 15:19:41 GMT

Ravi's answer is largely correct.

Each SAP system has a DB parameter set by the DBAs that determines how large an SQL statement can be... to explain further...

SAP uses Open SQL (proprietary language)... this is not an SQL language directly supported/used by DB companies like Oracle, etc.

And so... the SAP system must convert your Open SQL to the SQL that is "native" to your underlying DB system. During this process, it literally re-writes your Open SQL in Oracle SQL (let's say).

Example: Open SQL with ranges involved actual generates numerous conditions in the WHERE clause that the passed onto the DB system...

RANGES: S_OTHER_FIELD TYPE Zxxxx.

Assume that S_OTHER_FIELDS has 4 EQ values in it.

Open SQL looks like this.

SELECT * FROM SOME_TABLE WHERE FIELD1 IN S_OTHER_FIELD.

The generated Oracle SQL looks like this.

SELECT * FROM SOME_TABLE

WHERE FIELD1 = '0001'

OR FIELD1 = '0002'

OR FIELD1 = '0003'

OR FIELD1 = '0004'.

Note that the outputted SQL text is much larger.

This re-written SQL has a textual limit. In the case cited here, the DBAs have set the limit at 64 KB.

You have two choices:

1) convince your DBAs to increase this limit (and good luck with that)

2) Or alter your business logic to gather the results in chunks

Pretty amazing stuff... huh?!?!

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 15:25:27 GMT

dump:

The reason for the exception is:

The SQL statement generated from the SAP Open SQL Statement violates a

restriction imposed by the database system used in R/3.

Possible errors:

-The maximum size of an SQL statement has been exceeded.

-The statement contains too many input variables.

-The space needed for the input data exceeds the available memory.

-...

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 15:29:55 GMT

Yep, sounds to me like we were all correct, but that there is no instant, elegant solution to the problem, other than limiting the data to be selected.

But enough of that! It's Friday afternoon - anyone for a beer?

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 15:53:41 GMT

well well....

hahahha

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 15:56:47 GMT

Charles,

Correct. You will need to pursue one of my options above.

Please reward points accordingly and close the thread.

Re: What is wrong with this SQL statement?

Former Member — Fri, 16 Mar 2007 16:45:06 GMT

Hi Ravi,

Do u mean to include blart and zuonr into selection...

then filter them out manually in a loop?

hmm... well.. there could be a way... but may affect performance...

but again... its funny... how come when i test in test environment..

the for all entries table has more than 6000 records and s_blart has 126 records..

there's no problem with this...

so meaning DBA didnt set that in test environment? or its just SAP "bug"...