topic Re: Diff. between 2 select statements in Application Development and Automation Discussions

Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:37:51 GMT

Hi all,

Can anybody tell me the difference between SELECT SINGLE and SELECT UP TO 1 ROWS statements.

Thanks

Suresh

Re: Diff. between 2 select statements

abdul_hakim — Mon, 08 May 2006 06:39:49 GMT

In SELECT SINGLE u need to specify the all the key fields inorder to uniquely identify the records.

But SELECT UP TO 1 ROWS will fetch the first entry from your table regardless of the key fields...Also check the ABAP FAQs Section in SDN....

Cheers,

Abdul Hakim

Re: Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:40:20 GMT

when ur using select single the where condition should have all the key fields of the table here .

select up to 1 rows can be used if keyfields of table are not used in where condition to resolve performance issue.

Re: Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:40:29 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.

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.

You can refer to the below link..

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

Re: Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:44:45 GMT

Hi Suresh,

Select Single : Will return with the first occurence of the record matching the where clause.

Select upto 1 row : Will select all the records matching the where clause but will return the first entry out of the matching set ofr records.

Performance wise the SELECT SINGLE is better.

<b>Please mark helpful answer.</b>

Regards,

Amit Mishra

Re: Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:45:36 GMT

HI,

When you say SELECT SINGLE, it means that you are expecting only one row to be present in the database for the condition you're going to specify in the WHERE clause. so that means, you will have to specify the primary key in your WHERE clause. Otherwise you get a warning.

SELECT UP TO 1 ROWS is used in cases where you just want to make sure that there is at least one entry in the database table which satisfies your WHERE clause. Generally, it is meant to be used for existence-check. You may not want to really use the values returned by the SELECT statement in this case (thought this may not necessarily be so).

rgds,

latheesh

Re: Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:46:23 GMT

Hai Suresh

1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

1) SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

Effect

In the result set, the columns correspond exactly in terms of order, ABAP/4 Dictionary type and length to the fields of the database table (or view ) specified in the FROM clause .

Example

Output all flight connections from Frankfurt to New York:

TABLES SPFLI.

SELECT * FROM SPFLI

WHERE

CITYFROM = 'FRANKFURT' AND

CITYTO = 'NEW YORK'.

WRITE: / SPFLI-CARRID, SPFLI-CONNID.

ENDSELECT.

Example

Output all free seats on the Lufthansa flight 0400 on 28.02.1995:

TABLES SFLIGHT.

DATA SEATSFREE TYPE I.

SELECT SINGLE * FROM SFLIGHT

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'.

SEATSFREE = SFLIGHT-SEATSMAX - SFLIGHT-SEATSOCC.

WRITE: / SFLIGHT-CARRID, SFLIGHT-CONNID,

SFLIGHT-FLDATE, SEATSFREE.

2) SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

Effect

The order, ABAP/4 Dictionary type and length of the columns of the result set are explicitly defined by the list s1 ... sn . Each si has the form

ai or ai AS bi .

Here, ai stands either for

a field f of the database table or

a aggregate print.

Examples

Output all flight destinations for Lufthansa flights from Frankfurt:

TABLES SPFLI.

DATA TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO

INTO TARGET FROM SPFLI

WHERE

CARRID = 'LH ' AND

CITYFROM = 'FRANKFURT'.

WRITE: / TARGET.

ENDSELECT.

Output the number of airline carriers which fly to New York:

TABLES SPFLI.

DATA COUNT TYPE I.

SELECT COUNT( DISTINCT CARRID )

INTO COUNT FROM SPFLI

WHERE

CITYTO = 'NEW YORK'.

WRITE: / COUNT.

Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.1995:

TABLES SBOOK.

DATA: COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F.

DATA: CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT )

INTO (CONNID, COUNT, SUM, AVG)

FROM SBOOK

WHERE

CARRID = 'LH ' AND

FLDATE = '19950228'

GROUP BY CONNID.

WRITE: / CONNID, COUNT, SUM, AVG.

ENDSELECT.

3) SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab)

Effect

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn if the internal table itab contains the list s1 ... sn as ABAP/4 source code, and like SELECT [SINGLE [FOR UPDATE] | DISTINCT] * , if itab is empty. The internal table itab can only have one field which must be of type C and cannot be more than 72 characters long. itab must appear in parentheses and there should be no blanks between the parentheses and the table name.

Note

With this variant, the same restrictions apply as for SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn .

Example

Output all Lufthansa flight routes:

TABLES: SPFLI.

DATA: FTAB(72) OCCURS 5 WITH HEADER LINE.

REFRESH FTAB.

FTAB = 'CITYFROM'. APPEND FTAB.

FTAB = 'CITYTO'. APPEND FTAB.

SELECT DISTINCT (FTAB)

INTO CORRESPONDING FIELDS OF SPFLI

FROM SPFLI

WHERE

CARRID = 'LH'.

WRITE: / SPFLI-CITYFROM, SPFLI-CITYTO.

ENDSELECT.

SELECT UP TO 1 ROWS Means

It will fetch the first row depends on the where condition

and the rest of the rows omitted

SELECT Single Means

It will fetch exact row depends on the where condition

and the rest of the rows omitted

this will increase the performance

Thanks & Regards

Sreenivasulu P

Message was edited by: Sreenivasulu Ponnadi

Re: Diff. between 2 select statements

Former Member — Mon, 08 May 2006 06:52:35 GMT

Hey Amit,

U say SELECT SINGLE is faster than SELECT... UP TO 1 ROWS, but Shravanthi say the other way. im bit confused.

anyhow thanks to all of u...

Re: Diff. between 2 select statements

abdul_hakim — Mon, 08 May 2006 07:07:14 GMT

hi suresh,

you can check the below link for your clarification..

https://www.sdn.sap.com/irj/sdn/developerareas/abap?rid=/library/uuid/840ad679-0601-0010-cd8e-9989fd650822

Cheers,

Abdul Hakim