Page 1 of 2

this select command meaning?

Posted: Tue Dec 03, 2002 4:48 am
by digitalife

Code: Select all

       select * from smensapnew UP TO 1 ROWS 
                            where customized = 'S'
                             and object_id = parent
                             and menu_level = levl.


               ......

        endselect.
i don't understand "UP TO 1 ROWS',is that mean only get the first record at where condition of database?

Posted: Tue Dec 03, 2002 4:53 am
by ocvantol
First of all: read the help.

But you are almost right. It will return 1 record. Keep in mind it retrieves the first record it finds, so if nessecary use ORDER BY.

You can use this to check existence. It is better than SELECT, EXIT, ENDSELECT.

Posted: Tue Dec 03, 2002 5:03 am
by digitalife
i did not find this addication help in F1.

i am a beginer,thank you very much.

select single * from... hava a same result,isn't it ? :oops:

Posted: Tue Dec 03, 2002 5:05 am
by sagvantofi
yes... and SELECT SINGLE has better performance

I could be wrong, but ....

Posted: Tue Dec 03, 2002 5:51 am
by Dr Sidewalk
Technically I believe that SELECT SINGLE is a little more effecient.
I could be wrong here, but I understand that UP TO 1 ROWS returns 1 row once all the data has been extracted by the database server before it's returned. Hence, if you use ORDER BY field DESCENDING and also use UP TO 1 ROW you will get the last entry rather than the first.

Thought I would mention that ...

Dr Sidewalk :]

Posted: Tue Dec 03, 2002 6:26 am
by Rich
ocvantol wrote:First of all: read the help.

But you are almost right. It will return 1 record. Keep in mind it retrieves the first record it finds, so if nessecary use ORDER BY.

You can use this to check existence. It is better than SELECT, EXIT, ENDSELECT.
Sorry Ocvantol - you're a bit off the mark. As I keep saying, and Dr Sidewalk also mentioned that there is a difference between Select Single and Up To 1 Rows.

Select Single finds the first occurance, UP TO 1 Rows selects the entire data set according to the where clause, performs any ordering, grouping etc and then returns the first row of the result set.

Slightly different.

Rich

Posted: Tue Dec 03, 2002 6:32 am
by Henk
Hi digitalife,
You can find in in F1 HELP under FROM.

Regards
Henk

Posted: Tue Dec 03, 2002 7:37 am
by digitalife
thank guys. :D

You declared very clear,i got it.

thank you.

Posted: Tue Dec 03, 2002 7:39 am
by Guest
Rich wrote:
ocvantol wrote:First of all: read the help.

But you are almost right. It will return 1 record. Keep in mind it retrieves the first record it finds, so if nessecary use ORDER BY.

You can use this to check existence. It is better than SELECT, EXIT, ENDSELECT.
Sorry Ocvantol - you're a bit off the mark. As I keep saying, and Dr Sidewalk also mentioned that there is a difference between Select Single and Up To 1 Rows.

Select Single finds the first occurance, UP TO 1 Rows selects the entire data set according to the where clause, performs any ordering, grouping etc and then returns the first row of the result set.

Slightly different.
I doubt that SELECT ... FROM ... UP DO n ROWS ... will first retrieve ALL entries which fit the WHERE clause and just return N entries.
(At least for 4.x and newer I'm sure the UP TO ... is passed on to the DBMS. You can verify this via an SQL trace.)

Most important difference when using SELECT SINGLE:

DON'T use

IF SY-SUBRC NE 0 or similar expressions (IS INITIAL), because SY-SUBRC will be 8 if there are more records fitting the WHERE clause.

Frank

Posted: Tue Dec 03, 2002 7:43 am
by ocvantol
Fact is (I think) that UP TO 1 ROWS returns 1 record, a more or less random one, unless you use ORDER BY, or if the WHERE clause is so strict that you will find 1 record only.

Seems like SELECT SINGLE is an optimized UP TO 1 ROWS. But don't forget that the general syntax is UP TO n ROWS!

For a more than 95% correct reply, maybe I should have posted my comment in Dutch, but I very much doubt if that would help Digitalife :wink:

Posted: Tue Dec 03, 2002 7:49 am
by kumar
ocvantol wrote:For a more than 95% correct reply, maybe I should have posted my comment in Dutch, but I very much doubt if that would help Digitalife :wink:
Unless he knows http://babelfish.altavista.com

:D

KRK

Posted: Tue Dec 03, 2002 7:53 am
by digitalife
DON'T use

IF SY-SUBRC NE 0 or similar expressions (IS INITIAL), because SY-SUBRC will be 8 if there are more records fitting the WHERE clause.
So interesting,i'll try it tomorrow at work.

Posted: Tue Dec 03, 2002 8:02 am
by digitalife
ocvantol wrote:Fact is (I think) that UP TO 1 ROWS returns 1 record, a more or less random one, unless you use ORDER BY, or if the WHERE clause is so strict that you will find 1 record only.

Seems like SELECT SINGLE is an optimized UP TO 1 ROWS. But don't forget that the general syntax is UP TO n ROWS!

For a more than 95% correct reply, maybe I should have posted my comment in Dutch, but I very much doubt if that would help Digitalife :wink:
I think the UP TO 1 ROWS usually in use UP TO N(N>2) ROWS,in special useful,so it's speed maybe slower than select sing.

we can read recorder from a large database,compare it.

tomorrow,when i am in office,i'll test it. :)

Posted: Tue Dec 03, 2002 8:44 am
by ponnada_sk
for example, with out upto 1 rows your select statement retrive following data.
empno name salary
100 sri kris 1000
200 hello 2000
300 smile 3000

suppose you mention upto 1 rows in your select statement, it will retrive
only first row
ie.
100 sri kris 1000

generally these type select statements used in screen validations only.

regards
sri krishna ponnada

Posted: Tue Dec 03, 2002 8:54 am
by ocvantol
No Sri, you will not get the first row! You will get one row.