topic Limit select statement in Application Development and Automation Discussions

Limit select statement

Former Member — Sun, 03 Feb 2008 15:45:25 GMT

Hi everybody,

Is it possible to limit the number of results of a SELECT statement like in MySQL?

For example – my select statement will result 100 records and I need only the records from 31 to 60 (I need to extract the second group of 30 records, then I will need the third group of next 30 records and so on…)

In MySQL it would be:

SELECT * FROM <my table> LIMIT <limit_start>, <limit_end>

Thanks,

Roy

Re: Limit select statement

former_member156446 — Sun, 03 Feb 2008 16:06:39 GMT

Hi Roy

that's not possible in the select statement..

you have select upto 100 rows << which pick first 100 records which match your where clause.

you have package concept as well.. check the help on select..

Re: Limit select statement

Former Member — Sun, 03 Feb 2008 16:11:27 GMT

Hi,

check this

Keywords

http://help.sap.com/saphelp_47x200/helpdata/en/40/b54ea14933458e96c736ec03295bfa/frameset.htm

Procedure

1. When you create an SQL statement, insert square brackets in all the places you want a variable entry to be possible.

2. If you want to formulate a prompt for the dialog window, enter this prompt within the square brackets.

If you want to set parameters for queries for the columns Article No. and Unit Price in the Article table, you can set the following parameters among the selection criteria:

SELECT "Article No.", "Unit Price" FROM "Article"

WHERE "Article No." > [article number (from)]

AND "Article No." < [article number (to)]

AND "Unit Price" <= [upper price limit]

By setting parameters you can also replace entire sections of an SQL statement.

You can formulate a SELECT statement and add a WHERE condition using a parameter:

SELECT * FROM USERS [Your WHERE condition]

If you specify a comparison value for the data type CHAR, VARCHAR, DATE or TIME, you must enclose the comparison value in single quotation marks.

http://help.sap.com/saphelp_47x200/helpdata/en/ee/1c5bebeba711d4aa2800a0c9430730/frameset.htm

Regards

Re: Limit select statement

Former Member — Sun, 03 Feb 2008 16:17:41 GMT

Thanks K.P.N,

This does not help me since I don't have such column that I can set a condition of ">" or "<" on it.

The table includes only three columns that are from string type.

Do you have other suggestions?

Thanks for the try,

Roy

Re: Limit select statement

Former Member — Sun, 03 Feb 2008 16:35:01 GMT

Hi,

using sy-index or a sy-tabix is not a bad idea

can u try that one?

Regards

Re: Limit select statement

Former Member — Sun, 03 Feb 2008 16:49:20 GMT

Hi,

The question is if it will give the required results based on the fact that I already have some condition in the Where clause.

I mean that I need to execute the limit condition on the results I get based on the Where clause

Re: Limit select statement

former_member156446 — Sun, 03 Feb 2008 16:59:54 GMT

Hi Roy

Package size in SELECT statements

Package size can be used to retreive a spcific number of records at a time. This can be used if you for example only want tofinish processing a limited amount of data at a time due to lack of memory.

The example below read 50 records at a time from VBAK into an internal table, and selects the corresponding entries from vbap into an internal table. Then the two internal tables can be processed, and the next 50 records from VBAk can be read. remember to reinitialize tha tables before the next read.

Note the usage of SELECT - ENDSELECT !


REPORT z_test .

TYPES:
BEGIN OF t_vbak, vbeln LIKE vbak-vbeln, erdat LIKE vbak-erdat, END OF t_vbak, BEGIN OF t_vbap, posnr LIKE vbap-posnr, matnr LIKE vbap-matnr, meins LIKE vbap-meins, END OF t_vbap, BEGIN OF t_report, vbeln LIKE vbak-vbeln, erdat LIKE vbak-erdat, posnr LIKE vbap-posnr, matnr LIKE vbap-matnr, meins LIKE vbap-meins,
END OF t_report.

DATA:
li_vbak TYPE t_vbak OCCURS 0, l_vbak TYPE t_vbak, li_vbap TYPE t_vbap OCCURS 0, l_vbap TYPE t_vbap, li_report TYPE t_report OCCURS 0, l_report TYPE t_report.

START-OF-SELECTION.
SELECT vbeln erdat FROM vbak INTO TABLE li_vbak PACKAGE SIZE 50. SELECT posnr matnr meins FROM vbap INTO TABLE li_vbap FOR ALL ENTRIES IN li_vbak WHERE vbeln = li_vbak-vbeln. IF sy-subrc = 0.

    * Now you have the two internal tables li_vbak and liÆ_vbap filled with data.
    * Do something with the data – remember to reinitialize internal tables

ENDIF. ENDSELECT.

Re: Limit select statement

Former Member — Mon, 04 Feb 2008 02:15:06 GMT

First Select your Data From Table into your Internal Table ITAB .

Lets say u get 100 record .

Then

LOOP AT ITAB WHERE sy-tabix > 30 and sy-tabix < 60 .

Place code as per your reqmt here ( loop--endloop ) .

Yopu can populate another table here .

ENDLOOP .

Hope it helps .

Praveen

Re: Limit select statement

Former Member — Tue, 05 Feb 2008 15:32:20 GMT

Thanks Praveen,

I tried it and now I am getting the following compilation error:

No component exists with name "SY-TABIX".

This is the relevant part of my code:

DATA: BEGIN OF ITAB OCCURS 0,

SPRSL LIKE TSTCT-SPRSL,

TCODE LIKE TSTCT-TCODE,

TTEXT LIKE TSTCT-TTEXT,

END OF ITAB.

SELECT * FROM TSTCT INTO table ITAB WHERE (where_clause) ORDER BY TCODE ASCENDING.

LOOP AT ITAB WHERE sy-tabix > START_ENTRY and sy-tabix < END_ENTRY.

ENDLOOP.

Re: Limit select statement

Former Member — Tue, 05 Feb 2008 15:36:18 GMT

Use:

LOOP AT ITAB FROM START_ENTRY TO END_ENTRY.
ENDLOOP.

For further information, Press F1 on LOOP.

Rob

Edited by: Rob Burbank on Feb 5, 2008 10:37 AM

Re: Limit select statement

Former Member — Wed, 06 Feb 2008 01:42:50 GMT

Hello Roy,

That was my mistake .

Please try like below .

LOOP AT ITAB FROM < INDEX eg 30 > TO < INDEX eg 60 > .

ENDLOOP .

This should work.

Thanks . Praveen