topic Re: Native sql for nth record in Application Development and Automation Discussions

Native sql for nth record

kesavadas_thekkillath — Wed, 26 Aug 2009 10:16:12 GMT

Hi generally in oracle

we write something like this

select top 1 * from emp_mast where row_num = 10.

for getting the 10th row from table.

so in ABAP

how to write the same with native sql.


select * from mara into wa up to 10 rows order by matnr descending.
check sy-subrc = 0.
write mara-matnr.
exit.
endselect.

but the above is not satisfactory

I think so in open sql its not possible....if possible then how ?

Any ideas...

Re: Native sql for nth record

Former Member — Wed, 26 Aug 2009 10:19:35 GMT

Hello,

You can try following:



SELECT SINGLE *
    INTO <itab>
    FROM <table>
    UP TO 10 ROWS
    WHERE ....

There is lot possible with open SQL. Only required thing is you need to explore in SAP help.

Hope this helps!

Thanks,

Augustin.

Re: Native sql for nth record

kesavadas_thekkillath — Wed, 26 Aug 2009 10:22:25 GMT

SINGLE * with up to clause -

not all possible

Re: Native sql for nth record

Former Member — Wed, 26 Aug 2009 11:15:55 GMT

create itab of your wa type

now

select * from mara into table itab up to 10 rows order by matnr descending.

read itab into wa index 10.

check syntex.

Re: Native sql for nth record

kesavadas_thekkillath — Wed, 26 Aug 2009 11:34:28 GMT

no...intenal table should not be there in picture...i need it with a single select as of oracle query

Re: Native sql for nth record

guilherme_frisoni — Wed, 26 Aug 2009 12:37:40 GMT

Hi,

could be this:


select * from mara into wa up to 10 rows.
endselect.

Ok, will loop 10 times, but in the end you will have 10th record in wa structure.

Regards,

Frisoni

Re: Native sql for nth record

Former Member — Wed, 26 Aug 2009 12:42:29 GMT

HI,

In ABAP, INDEX is the only way to access a particular row in the table.

Pass data into internal table from database and the user read statement as follows:

READ TABLE ITAB INDEX 10.

Regds,

Anil

Re: Native sql for nth record

Former Member — Wed, 26 Aug 2009 12:43:10 GMT

Hi,

I dont think its possible with a single select query to directly fetch a particular numbered record from the db table in ABAP as in ORACLE. I did quite a searching and dint find any such provision.

Regards,

Vik

Re: Native sql for nth record

Former Member — Wed, 26 Aug 2009 12:45:21 GMT

Hello Keshu,

By mistake it came actually I wanted to tell all option.

Thanks,

Augustin.

Edited by: Augustarian on Aug 26, 2009 6:21 PM

Re: Native sql for nth record

Former Member — Wed, 26 Aug 2009 12:45:43 GMT

Re: Native sql for nth record

kesavadas_thekkillath — Wed, 26 Aug 2009 13:52:27 GMT

hi vik,

I think so native SQL could give a chance ... but no idea

Re: Native sql for nth record

rainer_hbenthal — Wed, 26 Aug 2009 13:57:04 GMT

There is no nth record in a relational database. Without specyfying a key and do an order by such a query makes no sense cause the result might not be stable. Even if there is no change to the table the result may vary, espacially after a reorg.

Re: Native sql for nth record

kesavadas_thekkillath — Wed, 26 Aug 2009 14:08:47 GMT


There is no nth record in a relational database

i just asked this question because it was asked ..... i dont mention it here beause its against forum rules !!!

Oracle is also a RDBMS.

I admit to your reply about reorg.

if oracle db is used in SAP ... then it should be possible to replicate the same in ABAP using native sql ... not sure just an assumption ...