Select statement using UP TO 1 rows

[Former Member]

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

[Former Member]

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.

[Former Member]

Hey Ken,

Thanks ... I am puzzled as this is behaving in this fashion only for a specific record and not for the rest.

So you say that UP TO 1 rows doesn't guarantee the 1st record rite??

Narendra.

[Former Member]

Check the index used for this SELECT.

[daniel_carvalho]

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.

[rainer_hbenthal]

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.

[Former Member]

Ok... So the essence is if we use UP TO 1 rows it doesnt guarantee the first record unless we use a sort by or order by additions.

So I believe a change of this statement is needed to fetch the desired results.

Select single would be beneficial I guess in this case, which would obviously fetch the first record?

Rgds,

Narendra

[SuhaSaha]

Hello Narendra,

SELECT SINGLE does not fetch the First Record )

It fetches the Unique Record. SELECT SINGLE should be used only if you have the full primary key.

Hope this is clear.

BR,

Suhas

[matt]

No, select single just returns a record that matches the where clause. As Rainer already said - there's no concept of first/last record in a relational database.

[Former Member]

Hi All,

Thanks for your time.. I think these reasons would justify the change that is required at the code level.

One last query - as you say select single would fetch the record that matches the unique key, if I have 2 records that match the where condition then is there a rule that the result would be the first record only?? or as per the relational database concept there aint such thing??

Rgds,

Narendra.

[matt]

as per relational database - there ain't no such thing.

matt

[rainer_hbenthal]

>

> Select single would be beneficial I guess in this case, which would obviously fetch the first record?
> Narendra

What part of "in a relational database there is no first/last record" you didnt understand?

[Former Member]

Matt - Thank You.

Rainer - Im not into databases so I do not understand these concepts..getting to know things out here.

Rgds,

Narendra

[nkr1shna]

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

[Former Member]

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

Thanks everyone!!

Rgds,

Narendra.