select single and select upto

[Former Member]

what is the difference of select single and select upto statements

[Former Member]

hi anjali,

SELECT SINGLE will always select one record from database....but in the where clause there should be always KEY fields..then only the SELECT SINGLE is usefull...

where as SELECT UPTO 1 row will also return a single record fron database...no need of KEY field required in where clause...

hope this will help u out...

Regards,

Prashant

[Former Member]

check this thread

[Former Member]

Hi anjali,

have a look in search forum u will get that

regards

Santosh

[former_member187255]

Check this...

<a href="http://sap-img.com/abap/difference-between-select-single-and-select-upto-one-rows.htm">http://sap-img.com/abap/difference-between-select-single-and-select-upto-one-rows.htm</a>

[Former Member]

Hi,

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.

Regards,

Ferry Lianto

[Former Member]

chk in this FAQ

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/840ad679-0601-0010-cd8e-9989fd65...

[Former Member]

select single - displays a single record matching your condition in the where statement

select upto - you can make any number of records to be selected

[Former Member]

Hi,

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

and in SELECT SINGLE query it is not mandatory to mention primary key fields in where condition , where as in SELECT UPTO 1 ROW query it is mandatory to use primary key fields in where clause...

Hope u got my point...

Regards,

kishor.

[Former Member]

hi kishore,

this is in response to your reply in this forum. You said that SELECT SINGLE query it is not mandatory to mention primary key field but in SELECT ..UPTO 1 ROWS query it is mandatory to use primary keyfield in the WHERE condition.

But actually SELECT SINGLE needs a primary key field in the table otherwise it ends up with a sequential search.

In SELECT..UPTO 1 ROWS there is no need to have a primary key. it doesnot check for the primary key for its search.

i hope you got the point .

if you are not clear

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

[Former Member]

Hi,

have a look in wiki :

"What is the difference between SELECT SINGLE and SELECT ... UP TO 1 ROWS?

SELECT SINGLE returns the first matching row for the given condition and it may not be unique, if there are more matching rows for the given condition.

SELECT ... UP TO 1 ROWS retrieves all the matching records and applies aggregation and ordering and returns the first record.

Inorder to check for the existence of a record then it is better to use SELECT SINGLE than using SELECT ... UP TO 1 ROWS since it uses low memory and has better performance."

Reward points if useful..

Regards

Nilesh

[Former Member]

Check this wiki.

https://www.sdn.sap.com/irj/sdn/wiki

Reward points if useful..

Regards

Nilesh

[Former Member]

In select single * the primary key is must in where clause

while this is not the case with upto 1 row

[Former Member]

HI,

SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

Mainly: to read data from

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Mainly: to check if entries exist.

[Former Member]

Select Single will hit the database only and primary key field is must in the statement where as in the select upto no need of mentioning the primary key fields.

[Former Member]

Hi Anjali

Select single - just selects the first record in your table

Select upto 'n' rows - it also selects the first row of your table and applies aggregation function on it to retreive the required first row of the table

REWARD IT PLEASE....!!!

null

[Former Member]

hi anjali,

SELECT SINGLE will always select one record from database....but in the where clause there should be always KEY fields..then only the SELECT SINGLE is usefull...

where as SELECT UPTO 1 row will also return a single record fron database...no need of KEY field required in where clause...

hope this will help u out...

Regards,

Prashant

[Former Member]

Select single selects the particular record based on the condition u have given.

Whereas Select upto is to select the number of records u want to get from the table.

Select upto 4 rows means it will select 4 rows from the table based on the condition u have given.

[Former Member]

hi anjali,

If u use select single then the table must have a primary key and also it matches with every record..

But select upto n rows is not like that..it fetches fastly..

Regards,

Zeemaaa....

[Former Member]

> what is the difference of select single and select

> upto statements

The difference between select single and select upto 1 row is usage of buffer area.

select single will search all the match records and bring it to buffer, form there we get it. Processing time is large here. If we use select upto 1 row, it will fetch the particular single record and move it to the buffer .Processing time is less compared to select single statement.

[Former Member]

hi,

1) both will fetch the single record only

2) but for select single no need of end select

3) primary key should need for select single not required for upto 1 row

madhukar.

[Former Member]

Hi,

Select single: reflects the first row in the database that it finds that fulfills the ‘where’

Clause if this results in multiple records then only first one will be returned and therefore may not be unique.

Select up to n row: (N stands for a inter number)

Select all the rows from the database table but write only the specified number of rows specified by N into the internal table.

It gives up to 1 row only 1 row is written into the internal table.

Thanks,

Satya kumar

[Former Member]

SELECT SINGLE selects only single record from the databasetable depending on your where clause.

where as select upto n rows selects all the records from the database but displays only n records.

so its better to use select single.

reward points if useful.

sagarika

[Former Member]

Select single < > means it will fetch only one record from the table which satisfies that WHERE condition.

Select * from <> up to 10 rows means it will fetch first 10 records form the defined table it will not check the where condition .

[Former Member]

Hi Anjali,

i will give you a brief idea regarding this.

suppose you have 10 records in a table of which 4 records have some fields matched.

now if you use SELECT SINGLE * query for that table using that field name as a WHERE condition then it will search up the records that fulfils that condition and takes up the first record which is the first record in the database table which may not be unique one.

but in SELECT..UPTO 1 ROWS query it also checks with WHERE condition and gives the first record in the search results which may be the desired one.

**************************************************************************************

now the other point includes that SELECT SINGLE needs a primary key field to read the database table records otherwise it may end up in sequential search.

In SELECT..UPTO 1 ROWS there is no need to have a primary key for its search.

i hope now you got some idea.

for complete result visit

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

regards,

Santosh Kumar