topic Re: Select statement with skip count & row count in Application Development and Automation Discussions

Select statement with skip count & row count

anand_sagarsethi — Fri, 18 Jun 2010 07:47:50 GMT

Hi,

I want to write a select statement in abap which should be like

select * from e070 into table t_output limit 10 20.

with this I mean to say is that get me 10 rows from the r3 where the ten rows are the 2nd half, like get 20 rows skip first 10 rows and send me the left ten rows.

Thanks

Anand

Re: Select statement with skip count & row count

anand_sagarsethi — Fri, 18 Jun 2010 07:51:58 GMT

I need to know how i can write the statement in abap..?

Re: Select statement with skip count & row count

Former Member — Fri, 18 Jun 2010 08:05:25 GMT

Hello

No possible.

Only select into internal table and manipulations with this table.

Re: Select statement with skip count & row count

Former Member — Fri, 18 Jun 2010 10:37:48 GMT

Hi Anand,

We can't achive with select statement but with internal table we can

try this code

SELECT * FROM E070 INTO TABLE IT_E070 UP TO 100 ROWS.

LOOP AT IT_E070 FROM 10 TO 20.
WRITE : / IT_E070-TRKORR.
ENDLOOP.

Hope this may help u.

Re: Select statement with skip count & row count

anand_sagarsethi — Fri, 18 Jun 2010 11:08:35 GMT

Thanks for the reply: I need this kind of select query because all the data generated in rfc called through web. and at web ui only first 10 are displayed. Most of the people are happy with first 10 records then why i should bring 100 records for those users who dont even see it. Moreover it makes session size big.. leading to performance issues.. Any suggestion.. abt how to solve this...

Thanks & Regards

Anand

Re: Select statement with skip count & row count

anand_sagarsethi — Fri, 18 Jun 2010 11:09:01 GMT

Thanks & Regards

Anand

Re: Select statement with skip count & row count

Former Member — Fri, 18 Jun 2010 11:17:29 GMT

Hi,

If u want only first 10 rows then u can try with this select statement

SELECT * FROM E070 INTO TABLE IT_E070 UP TO 10 ROWS

i guess this will help u

Re: Select statement with skip count & row count

RaymondGiuseppi — Fri, 18 Jun 2010 11:48:21 GMT

You have to select 20 rows, using a UP TO 20 ROWS in the select statement, then delete the first 10 rows of the internal table. Also, as you don't give any ORDER BY clause, the result is theorically unpredictable (optimizer choice, and buffering effect can arise)

SELECT * INTO TABLE t_output FROM e070 UP TO 20 ROWS ORDER BY PRIMARY KEY.
DELETE t_output FROM 1 TO 10.

Regards,

Raymond

Re: Select statement with skip count & row count

Former Member — Fri, 18 Jun 2010 13:23:27 GMT

Use the PACKAGE SIZE option of the SELECT statement. Make your PACKAGE SIZE 10 and ignore the first set.

Rob

Re: Select statement with skip count & row count

Former Member — Sat, 19 Jun 2010 06:39:10 GMT

I need this kind of select query because all the data generated in rfc called through web. and at web ui only first 10 are displayed.

If you have a table with a simple primary key (i.e. only one key field), which is the case for E070 you can simply use something like this:


select * from E070 into table e070_tab up to ROW_LIMIT rows
         where TRKORR > TRKORR_PREV
         order by primary key.

I.e. all you need to do in your RFC is to pass the number of entries you want to get via parameter ROW_LIMIT and fill the value TRKORR_PREV with the highest value you previously read. This way you can nicely paginate through the data. One could generalize this also for multiple key fields, but this is more difficult and would have to account for some possible overlap...

Most of the people are happy with first 10 records then why i should bring 100 records for those users who don't even see it. Moreover it makes session size big.. leading to performance issues..

Though I can understand the general need for using a clean approach for pagination, I'm irritated with your statement given table E070 along with 10 rows. When I add all field lengths for E070 I get 82 bytes and even with some overhead for the used data structure storing this information, I kind of wonder if your table/numbers are just an example or you're dealing with some highly tuned application. For example a web dynpro session usually consumes a couple of megabytes and that's just due to the overhead of the framework without considering any specific application data...

I'd expect though that performance is also accounting for response time (that's what the users are usually interested in) and in that case getting 10 entries and then having to do another RFC call seems also not ideal. I can see that you don't want to retrieve all data if that set is large, but I'd expect that your web app does some buffering to allow faster pagination (balance between memory requirements and response time).

Cheers, harald

Re: Select statement with skip count & row count

anand_sagarsethi — Sat, 19 Jun 2010 12:35:29 GMT

Gr8 reply!!!!! Harald Boeing Appreciated..

this can work..