topic Re: Select Single Statement in Application Development and Automation Discussions

Select Single Statement

Former Member — Tue, 30 May 2006 22:21:01 GMT

Hi All,

ranges : r_docnum for edidc-docnum.

constants: c_ready_status(2) type c value '64'.

I have a following select single statement in my code..

the range r_docnum has more than 1000 records. This select is to check whether any IDoc is in ready status..

select single * from edidc

where docnum in r_docnum

and status eq c_ready_status.

This statement is giving a short dump.. pls let me know any way of avoiding the short dump.

Thank you,

Suresh

Re: Select Single Statement

asarat — Tue, 30 May 2006 22:27:18 GMT

use this code

it will work

RANGES : r_docnum FOR edidc-docnum.

CONSTANTS: c_ready_status(2) TYPE c VALUE '64'.

SELECT * FROM edidc

WHERE docnum IN r_docnum

AND status EQ c_ready_status.

EXIT.

ENDSELECT.

Re: Select Single Statement

former_member186741 — Wed, 31 May 2006 00:46:38 GMT

is there any way you can reduce the size of r_docnum? For example are the numbers consecutive? If so, you could just take the high and low values and that will make your generated sql smaller.

select single * from edidc

where docnum between low_docnum and high_docnum

and status eq c_ready_status.

Or if the numbers aren't actually consecutive but contain several groups of consecutive numbers you could put your own logic in to reduce the number of entries in r_docnum by replacing each group of entries with consecutive numbers with a 'BT' entry with the appropriate low and high values.

eg,

sort r_docnum.

CLEAR r_docnum2.

LOOP AT r_docnum.

IF NOT r_docnum2-low IS INITIAL.

currdiff = r_docnum-low - prevnum.

IF currdiff = 1.

r_docnum2-high = r_docnum-low.

ELSE.

IF r_docnum2-high IS INITIAL.

r_docnum2-option = 'EQ'.

ELSE.

r_docnum2-option = 'BT'.

ENDIF.

APPEND r_docnum2.

CLEAR r_docnum2.

ENDIF.

prevnum = r_docnum-low.

IF r_docnum2 IS INITIAL.

r_docnum2-low = r_docnum-low.

ENDIF.

AT last.

IF r_docnum2-high IS INITIAL.

r_docnum2-option = 'EQ'.

ELSE.

r_docnum2-option = 'BT'.

ENDIF.

APPEND r_docnum2.

ENDAT.

ENDLOOP.

r_docnum[] = r_docnum2[].

Or where are you getting the r_docnum values from? If they are coming from a database table maybe could could change your select to use a join rather han storing the values in your range?

Re: Select Single Statement

Former Member — Wed, 31 May 2006 00:48:00 GMT

Try using SELECT UPTO 1 ROWS.

select * from edidc upto 1 rows

where docnum in r_docnum

and status eq c_ready_status.

Regards,

Ravi

Note : Please mark the helpful answers

Re: Select Single Statement

Former Member — Wed, 31 May 2006 01:35:47 GMT

Hi,

if you have populated R_DOCNUM through some logic say--

Loop at itab.

r_docnum-sign = 'I'.

r_docnum-low = itab-docnum.

append r_docnum.

endloop.

then you can try to..

loop at itab.

select single * from edidc

where docnum eq itab-docnum

and status eq c_ready_status.

endloop.

although this is not advisable (too many selects happening) you can avoid short dump with this.

Re: Select Single Statement

Former Member — Wed, 31 May 2006 04:27:56 GMT

Hi Suresh,

Ranges doesn't work if you have too many entries in it and it gives a short dump.

instead of ranges try using a internal table with the following statement

select single * from edidc

for all entries in itab

where status eq c_ready_status.

itab contains all the edidc-docnum.

Hope this helps.

Regards,

Kinshuk

Re: Select Single Statement

former_member186741 — Thu, 01 Jun 2006 00:14:54 GMT

or you could break it up into bite-sized chunks,

ranges r_docnum2 for vbak-vbeln

data packsize like sy-dbcnt default 100.

data firstline like sy-dbcnt.

data lastline like sy-dbcnt.

firstline = 1.

lastline = packsize.

clear sy-dbcnt.

do while sy-dbcnt = 0.

refresh r_docnum2.

loop at r_docnum from firstline to lastline.

append r_docnum to r_docnum2.

endloop.

select single * from edidc

where docnum in r_docnum2

and status eq c_ready_status.

firstline = firstline + packsize.

lastline = lastline + packsize.

enddo.

Re: Select Single Statement

Former Member — Thu, 01 Jun 2006 03:49:00 GMT

I think the dump he is getting is SQL statement too large. This is because of too many entries in r_docnum. What you need to do is to remove the r_docnum from the where clause and put it after it as follows.


DATA: BEGIN OF itab occurs 0.
        INCLUDE STRUCTURE edidc.
DATA: END OF itab.

SELECT * FROM edidc INTO TABLE itab
        WHERE status = c_ready_status.
DELETE itab WHERE NOT docnum IN r_docnum.
READ TABLE itab INDEX 1.
IF sy-subrc = 0.
*-- at least one document is in ready status
ELSE.
ENDIF.

Re: Select Single Statement

former_member186741 — Thu, 01 Jun 2006 04:01:39 GMT

removing r_docnum from the sql will certainly solve the dump problem........ but execution time could become an issue.

I think reducing the size of r_docnum or chunking it so that sql can deal with it is the better approach.