select statement

[Former Member]

I would like to know about

1) select single * and select upto one row

2)select for all entries

regards

ratna

[Former Member]

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.

2)If you need to retreive all the related data for the records of one internal table, You can use for all entries.

For example, You have an internal table ITAB with 8 records (let us say customers). You may need the related information for all these customers let us say the tax categories, then instead of writing 8 selects, you may just select the tax categories for all entries in itab where customer = itab-customer. This way you will be able to retreive the data pertaining to the customer records of the table itab.

Please donot forget to check for the records in the table before using for all entries. i.e if not itab[] is initial --- then only retreive the related data.

Message was edited by:

[Former Member]

Hi Ratna,

<b>select single * and up to 1 rows</b>

http://www.sap-basis-abap.com/abap/difference-between-select-single-and-up-to-1-row.htm

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

select single is used for primary key fields

select up to 1 rows is used for non key fields.

and select single st. stops the search after finding corresponding value. but select up to 1 rows does the search for the entire table even after matching the criteira.

<b>select for all entries</b>

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records (in the current client).

<b>Notes</b>

1.You can only use ... FOR ALL ENTRIES IN itab WHERE cond in a SELECT statement.

2.In the logical condition cond, the symbol itab-f is always a replacement symbol, and should not be confused with the component f of the header line of the internal table itab. The internal table itab does not need to have a header line.

3.Each component of the internal table that occurs in a replacement symbol in the WHERE condition must have exactly the same type and length as the corresponding component in the database table.

4.You cannot use replacement symbols in comparisons in LIKE, BETWEEN, or IN expressions.

5.If you use FOR ALL ENTRIES IN itab, you cannot use ORDER BY f1 ... fn in the ORDER-BY clause.

Reward points if that helps.

Manish

Message was edited by:

Manish Kumar

[Former Member]

Using

Select Single --> you can retrieve the Particular Data from the Table. Here we specify the primary key combinations

Select upto One Row.--> you can retrieve the data which is First record. which is of that

For all entries is to retrieve the Data from Tables.

Like if

a

b

c

e

f

are the entries of the First Table

b

c

e

are the entries of the second Table

And u want to retrieve the data of the records which r present in the first table.

Then we go for For aLL Entries