SQL: Package selection with entry-point/up to rows

[Weiskopf]

Hello together,

I would like to ask for a "mathematical" question concerning select clause with entry-point to build package wise selection.

If I have one key, I just can set the where-clause by keyfield > keyfield as next entry point, e.g.:

select * from mara into table lt_mara

up to 1000 rows

where matnr > lv_last_matnr.

lv_last_matnr = {last selected row].

But if I have more keys, so I need all relevant components in the where-clause.

(There is a requirement with more than 10 key elements)

Is there a general rule, where can be used to formulate the corresponding where-clause?

Thanks & best regards,

Kurt.

[Weiskopf]

Hello together,

I would like to ask for a "mathematical" question concerning select clause with entry-point to build package wise selection.

If I have one key, I just can set the where-clause by keyfield > keyfield as next entry point, e.g.:

select * from mara into table lt_mara

up to 1000 rows

where matnr > lv_last_matnr.

lv_last_matnr = {last selected row].

But if I have more keys, so I need all relevant components in the where-clause.

(There is a requirement with more than 10 key elements)

Is there a general rule, where can be used to formulate the corresponding where-clause?

Thanks & best regards,

Kurt.

[ThomasZloch]

Did you look into using the PACKAGE SIZE option of the SELECT statement? This does automatically on database level what you're trying to simulate here on the application server.

Thomas

[Weiskopf]

Hello Thomas,

thank you for your answer.

Unfortunately, I cannot use this type of selection, because I'll have commit process that will close the prior opened cursor (equals select ... endselect).

I'm looking for a general rule cause the program will be very generic; any table will be selected with different keys.

Thanks & best regards,

Kurt.

[ThomasZloch]

I see, but I think it would be quite difficult to come up with a generic solution.

Regarding the commit work, there is a workaround using OPEN CURSOR ... WITH HOLD that still allows you to use PACKAGE SIZE:

OPEN CURSOR WITH HOLD ... FOR SELECT ...
DO.
  FETCH NEXT CURSOR ... PACKAGE SIZE ...
  IF SY-SUBRC <> 0.
    EXIT.
  ENDIF.
* do something that requires a periodic DB commit
  CALL FUNCTION 'DB_COMMIT'.
ENDDO.
CLOSE CURSOR ...

Thomas