difference between read and select single statement

[Former Member]

hi All,

pl explain wat is the difference between read and select single statement?Thanks.

[Former Member]

hi,

Select single: for this stmt u hav specify all primary keys. it is used to get data from database table.

read: it is used in internal table to get a particular data.

Thanks,

Reward if useful,

Arunprasad.P

[Former Member]

Knowing when to use SELECT SINGLE or SELECT ... UP TO 1 ROWS

A lot of people use the SELECT SINGLE statement to check for the existence of a value in a database. Other people prefer to use the 'UP TO 1 ROWS' variant of the SELECT statement.

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

If you're considering the statements

SELECT SINGLE field INTO w_field FROM table.

and

SELECT field INTO w_field FROM table UP TO 1 ROWS. ENDSELECT.

then looking at the result, not much apart from the extra ENDSELECT statement. Look at the run time and memory usage and they may be worlds apart.

Why is this ?? The answer is simple.

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.

Get the difference ??

If not, here is a good example, credit for this example goes to Richard Harper, a friend of mine on sapfans.com :

Create a Ztable called ZDifference with 2 fields in it, MANDT of type MANDT and POSNR of type POSNR. Make sure both of these are keys. Also create a table maintenance dialog for it (SE11->Utilities->Table Maintenance Generator). Fill the table with ten rows 000001-000010.

Then run the program shown below:

Code:

Program: Z_Difference

Purpose: A program that demonstrates the difference

between SELECT SINGLE and SELECT UP TO n ROWS.

This program requires the data table Z_DIFFERENCE

to have been created according to the structure

outlined in the text above and populated with

at least 10 records.

Creation Date: 21/04/2004

Requested By:

Reference Doc:

Author: R Harper

Modification History:

Date Reason Transport Who

Report Z_Difference

Message-id 38

Line-Size 80

Line-Count 0

No Standard Page Heading.

Start-Of-Selection.

Data: w_Single type Posnr,

t_Rows type standard table of Posnr

initial size 0

with header line.

Select single Posnr

from zDifference

into w_Single.

Select Posnr

into table t_Rows

from zDifference

up to 1 rows

order by Posnr descending.

Write 😕 'Select single:', w_Single.

Skip 1.

Write 😕 'Up to 1 rows :'.

Loop at t_Rows.

Write t_Rows.

EndLoop.

You should see the output:

Select single: 000001

Up to 1 rows : 000010

The first 'SELECT' statement selected the first record in the database according to any selection criterion in the 'WHERE' clause. This is what a 'SELECT SINGLE' does. The second 'SELECT' has asked the database to reverse the order of the records before returning the first row of the result.

In order to be able to do this the database has read the entire table, sort it and then return the first record. If there was no ORDER BY clause then the results would have been identical (ie both '000001') but the second select if given a big enough table to look at would be far slower.

Note that this causes a problem in the Extended Program Check if the full key is not specified in a 'SELECT SINGLE'. Replacing the 'SELECT SINGLE' by an "UP TO 1 ROWS" will give the same exact results without any warning but the program will run slower and consume more memory. This is a good example of a warning that we should ignore... considering you are sure of what you are doing !!

READ

READ TABLE itab INTO wa WITH KEY connid = wa_sflight-connid

different types of read statements are :

READ TABLE <EMPTAB> options:

1 READ TABLE <EMPTAB>.

2 READ TABLE <EMPTAB> WITH KEY <k1> = <v1>…

<kn> = <vn>.

3 READ TABLE <EMPTAB> WITH TABLE KEY <k1> = <v1> ...

<kn> = <vn>.

4 READ TABLE <EMPTAB> WITH KEY = <value>.

5 READ TABLE <EMPTAB> WITH KEY . . . BINARY SEARCH.

6 READ TABLE <EMPTAB> INDEX .

7 READ TABLE <EMPTAB> COMPARING <f1> <f2> . . . .

8 READ TABLE <EMPTAB> COMPARING ALL FIELDS.

Reward if usefull

[Former Member]

Hi,

it is advisable tht we should use read stmnts rether than using the select statements since it comes under the performance issue,...

Read also will read the stmnt....and uses the key to retrive data.

select single will retriev the data in the similar way.....

Hope u understand the difference!

Regards,

Sana....

[Former Member]

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.

READ TABLE IT_HRP1000 WITH KEY OBJID = WA_SOBID-SOBID INTO WA_HRP1000.

in the above statement the read statement reads only one line .

u can place it a loop. so that it reads entire table step by step.

regards,

sreelakshmi

[Former Member]

Hi,

READ:

Read table doesn't allow ne operator.

It reads only one record...Either by index or key.

READ TABLE MY_TAB INDEX 1.

READ TABLE MY_TAB WITH KEY CODE = 'ATG'.

these are the results for SY-SUBRC checks after read table

SY-SUBRC = 0:

An entry was read.

SY-TABIX is set to the index of the entry.

SY-SUBRC = 2:

An entry was read.

SY-TABIX is set to the index of the entry. This return code can only occur when you use the COMPARING addition. For further detauls, refer to the COMPARING section of the additions

SY-SUBRC = 4:

No entry was read.

The value of SY-TABIX depends on the table type and whether the BINARY SEARCH addition was specified.

If the table is a SORTED TABLE or a table sorted in ascending order of the type STANDARD TABLE with the BINARY SEARCH addition, SY-TABIX refers to the next-highest index.

Otherwise, SY-TABIX is undefined.

SY-SUBRC = 8:

No entry was read.

This return code only occurs with a SORTED TABLE or a STANDARD TABLE with the BINARY SEARCH addition. SY-TABIX is set to the number of all entries plus 1.

Reading records with keys

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb35f8358411d1829f0000e829fbfe/content.htm

Reading lines with Index

http://help.sap.com/saphelp_47x200/helpdata/en/fc/eb3730358411d1829f0000e829fbfe/content.htm

SELECT SINGLE:

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.

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

Cheers,

vasavi.

[Former Member]

SELECT:

-

---

To read a single entry from the database, use the following:

SELECT SINGLE <cols> ... WHERE ...

To ensure that the line can be uniquely identified, you must specify values for all of the fields of the primary key of the table in the WHERE clause. If the WHERE clause does not contain all of the key fields, the syntax check produces a warning, and the SELECT statement reads the first entry that it finds that matches the key fields that you have specified.

The result of the selection is either an elementary field or a flat structure, depending on the number of columns you specified in <cols>. The target area in the INTO clause must be appropriately convertible.

If the system finds a line with the corresponding key, SY-SUBRC is set to 0, otherwise to 4.

Read:

-

---

To read a single line of any table, use the statement:

READ TABLE <itab> <key> <result>.

For the statement to be valid for any kind of table, you must specify the entry using the key and not the index. You specify the key in the <key> part of the statement. The <result> part can specify a further processing option for the line that is retrieved.

If the system finds an entry, it sets SY-SUBRC to zero, if not, it takes the value 4, as long as it is not influenced by one of the possible additions. If the internal table is an index table, SY-TABIX is set to the index of the line retrieved. If the table has a non-unique key and there are duplicate entries, the first entry is read.

KEVIN

[Former Member]

Hai.

check the links.

http://www.sap-img.com/abap/interview-question-on-bapi-rfc-abap-objects-tables.htm

http://abapprogramming.blogspot.com/2007/09/bapi-1.html

http://abapprogramming.blogspot.com/2007/09/bapi-2.html

Regards.

sowjanya.b

[Former Member]

Hi,

'Read' statement reads the internal table and fetches the same to a workarea .

'Select single' statement selects single field from the database table into a variable based on the selection criteria.

Regards,

Ramya