Skip to Content
author's profile photo Former Member
Former Member

Select statement using UP TO 1 rows

Hi All,

I know this is quite silly and basic but this is quite weird.

As far as my knowledge goes when UP TO 1 ROWS is used when the resultant set from the where condition has multiple records the below statement fetches the 1st record out of the set retreived to the buffer. This is working fine for all the cases except one where in the output is not the first record but the second one which is incidentally last record based on the where condition.

SELECT ebeln bednr loekz menge FROM ekpo

INTO (v_ebeln, v_bednr, v_loekz, l_orig_po_qty)

UP TO 1 ROWS

WHERE bednr = i_itab-bednr.

ENDSELECT.

Apart from changing the code is there any reason that we could justify why the first record is not being picked only in a specific instance??

<<Do not ask for or offer points>>

Rgds,

Narendra.

Edited by: Matt on Dec 16, 2008 3:25 PM - removed text

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2008 at 02:20 PM

    The UP TO 1 Rows statement does not guarantee the first record from the database table - it will usually be the first but not always depending on the index used. You might want to add a ORDER BY to your SELECT to make sure you get the record you want.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 16, 2008 at 02:27 PM

    In a relational database there is no first and no last record. Only if you sort them you have a determined order.

    up to 1 record does not mean gimme the first record, but means gimme just one record.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2008 at 02:25 PM

    Hello Narendra,

    I think it is occurring because the record you think is the first in fact is not. Have you checked the date/time the records were created into EKPO table?

    Kind Regards,

    Daniel.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 16, 2008 at 06:10 PM

    HI Narendra,

    Your select statement is fetching records from table based on date and time record was created into table. Please check whether this criteria makes sense in your case.

    If this is SAP table, most possibily it will log date and time record got created. You can sort based on date and time created.

    Let me know if you have any questions.

    Best Regards,

    Krishna

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 11:29 AM

    Had to modify the statement due to the inconsistent behaviour of the select up to 1 rows statement.

    Thanks everyone!!

    Rgds,

    Narendra.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.