topic Re: select single and select upto in Application Development and Automation Discussions

select single and select upto

Former Member — Thu, 03 May 2007 13:36:08 GMT

what is the difference of select single and select upto statements

Re: select single and select upto

Former Member — Thu, 03 May 2007 13:39:57 GMT

check this thread

Re: select single and select upto

Former Member — Thu, 03 May 2007 13:40:17 GMT

Hi anjali,

have a look in search forum u will get that

regards

Santosh

Re: select single and select upto

former_member187255 — Thu, 03 May 2007 13:41:06 GMT

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>

Re: select single and select upto

Former Member — Thu, 03 May 2007 13:42:08 GMT

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

Re: select single and select upto

Former Member — Thu, 03 May 2007 13:42:34 GMT

chk in this FAQ

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

Re: select single and select upto

Former Member — Thu, 03 May 2007 15:46:34 GMT

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

Re: select single and select upto

Former Member — Fri, 04 May 2007 12:54:24 GMT

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.

Re: select single and select upto

Former Member — Fri, 04 May 2007 14:12:24 GMT

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

Re: select single and select upto

Former Member — Fri, 04 May 2007 14:13:34 GMT

Check this wiki.

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

Reward points if useful..

Regards

Nilesh

Re: select single and select upto

Former Member — Fri, 04 May 2007 14:17:38 GMT

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

while this is not the case with upto 1 row

Re: select single and select upto

Former Member — Sat, 05 May 2007 06:51:38 GMT

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.

Re: select single and select upto

Former Member — Mon, 07 May 2007 09:06:57 GMT

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.

Re: select single and select upto

Former Member — Thu, 10 May 2007 13:40:07 GMT

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

Re: select single and select upto

Former Member — Sun, 13 May 2007 07:12:10 GMT

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

Re: select single and select upto

Former Member — Tue, 15 May 2007 02:54:20 GMT

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.

Re: select single and select upto

Former Member — Tue, 15 May 2007 03:05:33 GMT

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

Re: select single and select upto

Former Member — Tue, 15 May 2007 04:56:31 GMT

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

Re: select single and select upto

Former Member — Wed, 16 May 2007 10:27:57 GMT

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.

Re: select single and select upto

Former Member — Fri, 18 May 2007 11:00:04 GMT

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