Select quires?

[Former Member]

Hi,

what is the use of 'for all entries' and select single, select single *, inner joins,

into corresponding fields, read table, order by .In which situation i will use these quiers?

Regards,

srihitha

[Former Member]

Hi,

what is the use of 'for all entries' and select single, select single *, inner joins,

into corresponding fields, read table, order by .In which situation i will use these quiers?

Regards,

srihitha

[Former Member]

Below is the information required by you.

Please mark this question answered if deemed fit.

SINGLE

Effect

If SINGLE is specified, the resulting set has a single line. If the remaining additions to the SELECT command select more than one line from the database, the first line that is found is entered into the resulting set. The data objects specified after INTO may not be internal tables, and the APPENDING addition may not be used.

An exclusive lock can be set for this line using the FOR UPDATE addition when a single line is being read with SINGLE. The SELECT command is used in this case only if all primary key fields in logical expressions linked by AND are checked to make sure they are the same in the WHERE condition. Otherwise, the resulting set is empty and sy-subrc is set to 8. If the lock causes a deadlock, an exception occurs. If the FOR UPDATE addition is used, the SELECT command circumvents SAP buffering.

FOR ALL ENTIRES IN TABLE

Uses all entires in the specified internal table in the mentioned WHERE condition.

INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab [PACKAGE SIZE n]

Effect

If the result set consists of multiple lines, an internal table itab of any table type can be specified after INTO or APPENDING. The row type of the internal table must meet the prerequisites.

The result set is inserted into the internal table itab line-by-line; a sorting process is executed in the case of a sorted table. If INTO is used, the internal table is initialized before the first line is inserted. Previous lines remain intact if APPENDING is used.

Before any assignment of a line of the result set, an initial row of the internal table itab is created and the line of the result set is assigned to this row. When assigning a line of the result set to a row of the internal table with or without CORRESPONDING FIELDS, the same rules apply as when assigning to an individual work area wa (see above).

ORDER BY

Effect:

The addition ORDER BY sorts the resulting set by the content of the specified column. The order of the rows in the result set refers to all columns that are not listed after ORDER BY, is undefined, and can be different in repeated executions of the same SELECT statement.

The addition ORDER BY can only be used with the addition PRIMARY KEY at the same time as the addition FOR ALL ENTRIES of the WHERE condition.

[Former Member]

Hi,

for all entries increases performances used when u cant use joins

select single * when u want single record from group of records

inner joins when u want to join group of tables

into corresponding fields when u want to move field values in the order u choose

read table when u want to read contents of internal table

order by when u want in the sequence u prescribed

more clarifications revert me back

regards,

p498863

[Former Member]

hi,

SELECT result FROM source [INTO target] [WHERE condition] [GROUP BY fields] [ORDER BY order].

SELECT clause

Variants:

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

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

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

FROM clause

Variants:

1. ... FROM dbtab

•

Additions:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

2. ... FROM (dbtabname)

•

Additions:

1. ... CLIENT SPECIFIED

2. ... BYPASSING BUFFER

3. ... UP TO n ROWS

INTO target

(This form of the FROM clause works only in conjunction with the INTO clause.)

INTO clause

Variants:

1. ... INTO wa

2. ... INTO CORRESPONDING FIELDS OF wa

3. ... INTO (f1, ..., fn)

4. ... INTO TABLE itab

5. ... INTO CORRESPONDING FIELDS OF TABLE itab

6. ... APPENDING TABLE itab

7. ... APPENDING CORRESPONDING FIELDS OF TABLE itab

WHERE clause

Variants:

1. ... WHERE f op g

2. ... WHERE f [NOT] BETWEEN g1 AND g2

3. ... WHERE f [NOT] LIKE g

4. ... WHERE f [NOT] IN (g1, ..., gn)

5. ... WHERE f [NOT] IN itab

6. ... WHERE f IS [NOT] NULL

7. ... WHERE NOT cond

8. ... WHERE cond1 AND cond2

9. ... WHERE cond1 OR cond2

10. ... WHERE (itab)

11. ... WHERE cond AND (itab)

12. ... FOR ALL ENTRIES IN itab WHERE cond

Operator Meaning

EQ or = equal to

NE or < > not equal to

LT or < less than

LE or < = less than or equal to

GT or > greater than

GE or >= greater than or equal to

GROUP-BY clause

Variants:

1. ... GROUP BY f1 ... fn

2. ... GROUP BY (itab)

ORDER-BY clause

Variants:

1. ... ORDER BY PRIMARY KEY

2. ... ORDER BY f1 ... fn

3. ... ORDER BY (itab)

regards,

pavan

[Former Member]

hi,

SELECT <result>

The SELECT clause defines the structure of the data you want to

read, that is, whether one line or several, which columns you

want to read, and whether identical entries are acceptable or not.

INTO <target> [The INTO clause determines the target area <target> into which

the selected data is to be read.

FROM <source>

The FROM clause specifies the database table or view

<source> from which the data is to be selected. It can also be

placed before the INTO clause.

WHERE <cond>

The WHERE clause specifies which lines are to be read by

specifying conditions for the selection.

GROUP BY <fields> [

The GROUP-BY clause produces a single line of results from

groups of several lines. A group is a set of lines with identical

values for each column listed in <fields>.

HAVING <cond>

The HAVING clause sets logical conditions for the lines

combined using GROUP BY.

ORDER BY <cond>

The ORDER-BY clause defines a sequence <fields> for the lines

resulting from the selection.

regards,

pavan

[Former Member]

Thanks for replying.

Regards,

srihitha

[Former Member]

Hi Srihitha ,

Please mark this Questions as ANSWERED if you problem is solved so that other people can understand whilst referencing it.

Thanks.

[Former Member]

Hi Srihitha,

Use of FOR ALL Entries

Outer join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

1. Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

2. If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

3. If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Not Recommended

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

Recommended

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Difference Between Select Single and Select UpTo One Rows

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.

Reading Lines of Tables Locate the document in its SAP Library structure

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.

[Former Member]

... FOR ALL ENTRIES IN itab WHERE cond

Effect

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

... INTO CORRESPONDING FIELDS OF TABLE itab

Addition:

... PACKAGE SIZE n

Effect

Works like ... INTO CORRESPONDING FIELDS OF wa, except that the selected data is not placed in the internal table itab line by line, but in a single operation. In this case, SELECT does not introduce a processing loop, so there can be no ENDSELECT statement. The old contents of itab are overwritten. Fields of the internal table itab which are not filled are initialized based on their ABAP data type

SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

Effect

The columns of the result set will have exactly the same sequence, names, database type, and length as the fields of the database table or view specified in the FROM clause.

Read Table:

It is just reading the table based on ur condition

read table i_table with key vbeln = w_kna1-vbeln.

like that u have to read a table..

Thanks,

Ari