difference in SELECT statements?

[Former Member]

i have 3 select statements like SELECT SINGLE *

and SELECT UPTO ONE ROW,

AND SELECT DOUBLE *

what is the main differences between these 3 statements?

how the execution flow will be?

correct answer for this question will carry good reward points

Message was edited by:

Koteswara rao

[SantoshKallem]

http://www.sap-img.com/abap/difference-between-select-single-and-select-upto-one-rows.htm

[Former Member]

<b>Select Single</b> - This form of select is used when the WHERE clause of the SELECT contains all the KEY fields for comparision buring the selection of a single record. This returns the exact record.

<b>Select UP TO 1 rows</b> - This form of select is used when the WHERE clause of the SELECT does not contain all the KEY fields for comparision buring the selection of a single record. This returns the first record for condition.

Similar reasoning can be given to the third form

[Former Member]

hi,

chk out this:

http://sap.ittoolbox.com/groups/technical-functional/sap-dev/select-single-up-to-n-rows-234183

keerthi

[Former Member]

hi,

Both are same,but when it comes to performance select single takes more time.

use the SELECT SINGLE statement when I need a single entry from a database table where I have all key field values availabe. After executing the statement I have the details of the entry available (i.e. stored in a variable).

I use the SELECT UP TO ONE ROWS statement if I need to check that at least a single entry having certain values exists in the database table. Since I am not interested in details of the entries I do not have any variable filled with details.

_____________

SELECT SINGLE:

1. Select single is based on PRIMARY KEY

2. It will take the first record directly without searching of all relevant records.(Though you have lot of records for given condition)

SELECT UPTO 1 ROW :

It will check all records for given condition and take the first record .

It means in select single no searching, where as other searching.

Therefore, select single more efficient than UPTO 1 ROW.

_________________--

SELECT SINGLE is an option we use only when we know the full key of the table, not when we know that there will be only one record. So if you are selecting from MARA and your WHERE condition has MATNR in it, then you should use SELECT SINGLE. But if your WHERE condition has BISMT(old material number) and even if you know that it will result in one record only, you should not use SELECT SINGLE. It is not that it will give you an error but if you do an extended check, there it will show it as a warning saying that you didn't use the full key and that there is a possibility that there could be more than one record.

SELECT UP TO 1 ROWS is used when you are not passing the key field, but you know 1)there will be only one record or 2)all records will have the same value for the selected field. Let us say you are selecting from MARC and you are interested in the value of the field ABC indicator. You know, based on your business process, that this indicator will have the same value even though, it is extended to 10 plants. Then you can use SELECT ABCIN FROM MARC UP TO 1 ROWS WHERE MATNR = P_MATNR. ENDSELECT. Here, even though you are not supplying the second key field WERKS, since you know there will only be one value(even though there are multiple records fetched with this clause), you are using SELECT UP TO 1 ROWS. In the other example where you select from MARA using BISMT, there it might fetch you just one record and so you will still use SELECT UP TO 1 ROWS.

SELECT UP TO 1 ROWS introduces a loop to fetch one record from your database, where as SELECT SINGLE doesn't.

or

check this link :

sri

[Former Member]

SELECT SINGLE * retrieve the exact row ( a single access on database ) if full key is specified.

SELECT UPTO ONE ROW, is usually used when we don't have full primary key to read the table, it fetches the first row (in the sort of db table) which matches the partial key. When a partial key is given both SINGE * and UPTO ONE ROW, act in a similar fashion

There is no SELECT construct by name DOUBLE

[Former Member]

Hi Ram,

<b>SELECT SINGLE *</b> : extracts exactly one row from the database and WHERE clause should contain all key fields, can also say, it will extract a record which can be identified uniquely in the database.

<b>SELECT UPTO ONE ROW</b> : Also extracts single row from database, but WHERE clause may or may not contain all the key fields. Can say, it selects exactly one row which meets the selection criteria.

There is no SELECT DOUBLE * as for as I know.

Hope this info may also help you. Pls close the thread if r met with sufficient info.

Regards,

Sujatha.