topic Re: SQL: Package selection with entry-point/up to rows in Application Development and Automation Discussions

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

Weiskopf — Tue, 06 Apr 2010 15:41:35 GMT

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.

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

ThomasZloch — Tue, 06 Apr 2010 15:48:50 GMT

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

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

Weiskopf — Wed, 07 Apr 2010 08:17:40 GMT

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.

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

ThomasZloch — Wed, 07 Apr 2010 14:39:40 GMT

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 &lt;&gt; 0.
    EXIT.
  ENDIF.
* do something that requires a periodic DB commit
  CALL FUNCTION 'DB_COMMIT'.
ENDDO.
CLOSE CURSOR ...

Thomas