this select command meaning?

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

digitalife
Posts: 137
Joined: Wed Nov 06, 2002 3:50 am
Location: China

this select command meaning?

Post by digitalife » Tue Dec 03, 2002 4:48 am

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?
I Like SAP

ocvantol
Posts: 929
Joined: Tue Oct 22, 2002 3:19 am
Location: The Hague

Post by ocvantol » Tue Dec 03, 2002 4:53 am

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.
Last edited by ocvantol on Tue Dec 03, 2002 5:12 am, edited 1 time in total.

digitalife
Posts: 137
Joined: Wed Nov 06, 2002 3:50 am
Location: China

Post by digitalife » Tue Dec 03, 2002 5:03 am

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:
I Like SAP

sagvantofi
Posts: 47
Joined: Mon Nov 04, 2002 8:33 am
Location: Prague

Post by sagvantofi » Tue Dec 03, 2002 5:05 am

yes... and SELECT SINGLE has better performance

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

I could be wrong, but ....

Post by Dr Sidewalk » Tue Dec 03, 2002 5:51 am

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 :]

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Post by Rich » Tue Dec 03, 2002 6:26 am

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

Henk

Post by Henk » Tue Dec 03, 2002 6:32 am

Hi digitalife,
You can find in in F1 HELP under FROM.

Regards
Henk

digitalife
Posts: 137
Joined: Wed Nov 06, 2002 3:50 am
Location: China

Post by digitalife » Tue Dec 03, 2002 7:37 am

thank guys. :D

You declared very clear,i got it.

thank you.
I Like SAP

Guest

Post by Guest » Tue Dec 03, 2002 7:39 am

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

ocvantol
Posts: 929
Joined: Tue Oct 22, 2002 3:19 am
Location: The Hague

Post by ocvantol » Tue Dec 03, 2002 7:43 am

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:

kumar
Posts: 2012
Joined: Tue Oct 22, 2002 12:51 pm
Location: Basel, Switzerland
Contact:

Post by kumar » Tue Dec 03, 2002 7:49 am

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

digitalife
Posts: 137
Joined: Wed Nov 06, 2002 3:50 am
Location: China

Post by digitalife » Tue Dec 03, 2002 7:53 am

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.
I Like SAP

digitalife
Posts: 137
Joined: Wed Nov 06, 2002 3:50 am
Location: China

Post by digitalife » Tue Dec 03, 2002 8:02 am

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. :)
I Like SAP

ponnada_sk
Posts: 15
Joined: Tue Dec 03, 2002 8:33 am
Location: india
Contact:

Post by ponnada_sk » Tue Dec 03, 2002 8:44 am

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

ocvantol
Posts: 929
Joined: Tue Oct 22, 2002 3:19 am
Location: The Hague

Post by ocvantol » Tue Dec 03, 2002 8:54 am

No Sri, you will not get the first row! You will get one row.

Post Reply