topic Re: SELECT SQL in a LOOP statement in Application Development and Automation Discussions

SELECT SQL in a LOOP statement

Former Member — Wed, 21 Jan 2015 05:43:21 GMT

Hi all,

As known, it's not recommended to write SELECT when doing loop for an internal table.

But this time my problem is that I have to use an SAP standard FM to get data from DB in a LOOP.

The internal table is large and it causes some memory shortage and time issues when I run it.

And it's not easy to do data fetching by myself so that I am trying to figure out another way.

Is there something like always make DB connection available, avoiding 'open'(like PREPARE) and 'close' a lot of times, like:

OPEN DB connection.

LOOP AT it.

SELECT xxx FROM xxx

ENDLOOP.

CLOSE DB connection.

Is it possible to do that?

Re: SELECT SQL in a LOOP statement

Former Member — Wed, 21 Jan 2015 06:31:40 GMT

Which FM are you using for fetching data from database?

Re: SELECT SQL in a LOOP statement

vinodkumar_thangavel — Wed, 21 Jan 2015 07:09:57 GMT

This message was moderated.

Re: SELECT SQL in a LOOP statement

Former Member — Wed, 21 Jan 2015 08:58:59 GMT

hi ming,

you can use database cursor concept where you can mention size.

Re: SELECT SQL in a LOOP statement

Former Member — Wed, 21 Jan 2015 10:13:52 GMT

can you briefly explain what it is, thanks.

Re: SELECT SQL in a LOOP statement

Former Member — Wed, 21 Jan 2015 11:54:58 GMT

Hi,

You can write SELECT query outside LOOP using FOR ALL ENTRIES w.r.t internal table "it" and then read this structure in within the LOOP.

SELECT XXX

INTO TABLE it_one

FOR ALL ENTRIES XXX
WHERE XXX

LOOP AT it.

READ TABLE it_one INTO wa_one WITH KEY XXX = it-XXX.

ENDLOOP.

Regards,

Re: SELECT SQL in a LOOP statement

DominikKraemer — Wed, 21 Jan 2015 12:07:22 GMT

Hello,

you could work with DB cursor to process only 100 entries at one time:

OPEN CURSOR dbcur FOR

SELECT *

FROM spfli

ORDER BY carrid.

DO.

FETCH NEXT CURSOR dbcur INTO TABLE spfli_tab PACKAGE SIZE 100.

IF sy-subrc <> 0.

EXIT.

ENDIF

* Your logic here for 100 entries each

ENDDO.

CLOSE CURSOR: dbcur.

Re: SELECT SQL in a LOOP statement

Former Member — Wed, 21 Jan 2015 12:20:41 GMT

Hi,

You can eliminate the data using 'FOR ALL ENTRIES ' statement OR you can use that statement for performance tunning:

%_HINTS ORACLE

'..........'.

refer the following links,

http://www.sapfans.com/forums/viewtopic.php?p=883185&sid=37b22c7f1a6421ccb8966c9da421a540

Re: SELECT SQL in a LOOP statement

RaymondGiuseppi — Wed, 21 Jan 2015 12:23:13 GMT

Which FM do you call in the LOOP?

Regards,

Raymond

Re: SELECT SQL in a LOOP statement

former_member228514 — Wed, 21 Jan 2015 12:46:08 GMT

Try to use package option. Take some interval of records while fetching data