topic Re: Select statement using UP TO 1 rows in Application Development and Automation Discussions

Select statement using UP TO 1 rows

Former Member — Tue, 16 Dec 2008 14:07:45 GMT

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

Re: Select statement using UP TO 1 rows

Former Member — Tue, 16 Dec 2008 14:20:16 GMT

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.

Re: Select statement using UP TO 1 rows

Former Member — Tue, 16 Dec 2008 14:24:11 GMT

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.

Re: Select statement using UP TO 1 rows

Former Member — Tue, 16 Dec 2008 14:25:25 GMT

Check the index used for this SELECT.

Re: Select statement using UP TO 1 rows

daniel_carvalho — Tue, 16 Dec 2008 14:25:32 GMT

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.

Re: Select statement using UP TO 1 rows

rainer_hbenthal — Tue, 16 Dec 2008 14:27:45 GMT

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.

Re: Select statement using UP TO 1 rows

Former Member — Tue, 16 Dec 2008 14:37:25 GMT

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

Re: Select statement using UP TO 1 rows

SuhaSaha — Tue, 16 Dec 2008 14:42:57 GMT

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

Re: Select statement using UP TO 1 rows

matt — Tue, 16 Dec 2008 14:43:34 GMT

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.

Re: Select statement using UP TO 1 rows

Former Member — Tue, 16 Dec 2008 14:49:18 GMT

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.

Re: Select statement using UP TO 1 rows

matt — Tue, 16 Dec 2008 17:47:09 GMT

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

matt

Re: Select statement using UP TO 1 rows

nkr1shna — Tue, 16 Dec 2008 18:10:03 GMT

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

Re: Select statement using UP TO 1 rows

rainer_hbenthal — Tue, 16 Dec 2008 18:14:23 GMT


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

Re: Select statement using UP TO 1 rows

Former Member — Wed, 17 Dec 2008 05:56:43 GMT

Matt - Thank You.

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

Rgds,

Narendra

Re: Select statement using UP TO 1 rows

Former Member — Fri, 19 Dec 2008 11:29:26 GMT

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

Thanks everyone!!

Rgds,

Narendra.