topic Re: select single in Application Development and Automation Discussions

select single

Former Member — Sat, 01 Sep 2007 10:27:29 GMT

anybody tell what's the difference between using 'SELECT SINGLE' statement as against a 'SELECT .... UP TO 1 ROWS' statement ?

Re: select single

suredarreddy_pulimamidi — Sat, 01 Sep 2007 10:31:18 GMT

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.

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.

reward if Helpful

Re: select single

Former Member — Sat, 01 Sep 2007 10:33:37 GMT

HI,

when you use select single

then you should have all the primary key values

but it is not mandatory

in the extended progam check it will give you an error

if you dont give all the primary key values

if you give some values then it will fetch the first record which satisfies that

conndition

select up to

then no need of all the primary key values

y because if you give some conditions in the where conditions

then it will fetch the first record which satisfies that condition

performance wise up to is good when compared with select single

thanks & regards,

Venkatesh

Re: select single

Former Member — Sat, 01 Sep 2007 10:33:53 GMT

Both are same ,both query will get single record from database

Please use select single when you have key field ,then use select single otherwise you get performance issue.

You can use select up to 1 row any time ,if you do not have key field in where condition then use select up to 1 row instead of select single

Thanks

Seshu

Re: select single

Former Member — Sat, 01 Sep 2007 15:35:17 GMT

Select single should be used only if you specify the complete key as it should return a unique record.

However upto 1 rows retrieves the first record matching the selection criterion.

PS : Please reward points if helpful.

Re: select single

Former Member — Mon, 03 Sep 2007 04:10:47 GMT

select single will find the first hit in the data base table

where as select up to gets the all the records as per the where condition and finally returnd first record

select single mainly used for validation perpouse

reward if usefull

Re: select single

Former Member — Mon, 03 Sep 2007 06:20:19 GMT

According to SAP Performance course the 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.

Mainly: to read data from

Mainly: to check if entries exist.

Select Single is the best one compared to UPto one rows.

Select Single will get the first record from the table which satisfies the given condition.So it will interact once with the database.

UTO 1 rows will get the list of the records for the given match and iwll show the first record from the list.So it will take time to get the record.

SELECT SINGLE VBELN from VBAK

where MATNR = '1M20'.

---Thjis will get the first matched record and will display the record

SELECT VBELN from VBAK

where MATNR = '1M20' upto 1 rows.

---Thjis will get the list of matched records and will display the first record

The Major difference between Select Single and Select UPTO 1 rows is The Usage Of Buffer for each.

Select Single will search for all the satisfied data and bring all that data into Buffer and later it will give to that data to the program.

Select UPTO 1 Rows will end the search after getting the 1st satisfied record and gives that record to the program.

Thus Select Single will take much processing time when compare with Select UPTO 1 rows.

Also

check these threads..

regards,

srinivas

Re: select single

Former Member — Wed, 05 Sep 2007 10:57:25 GMT

Hi Reddy ,

The major different between select single and select up to 1 rows is .

In case of select single it will search whole data base and give the resume , here in where condition is madatory , but in case of select up to 1 the control go to that rows and check the condition , if the condition is true it will give that result other it will show the first record of the database.

Regards.

Nihar swain.