Native sql for nth record

[kesavadas_thekkillath]

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...

[Former Member]

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.

[kesavadas_thekkillath]

SINGLE * with up to clause -

---

not all possible

[Former Member]

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.

[kesavadas_thekkillath]

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

[Former Member]

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

[kesavadas_thekkillath]

hi vik,

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

[rainer_hbenthal]

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.

[kesavadas_thekkillath]

hi

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 ...

[guilherme_frisoni]

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

[Former Member]

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

[Former Member]

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

[Former Member]

.