select

[Former Member]

what is the difference between select single * and for all entries statement in abap

[Former Member]

Hi sandeep

Select single * means it will get datas from particular row in table.

select * from table means - it wil give all datas from table.

if it useful give points.

thanks

senthil

[Former Member]

hi,

<b>Select single *</b> is to read a single row from database.

Ex : SELECT SINGLE * FROM mara WHERE matnr = p_matnr.

here in where condition as far as possible we need to mention primary key.

<b>Select ... for all entries in ITAB</b> is basically used to read certain number of rows database corresponding to the rows in ITAB. we must make sure that ITAB has at lease one row in it. Otherwise for all entries statement leads to performance hit.

Ex. IF NOT g_t_mara[] IS INITIAL.

SELECT *

FROM mvke INTO TABLE g_t_mvke

FOR ALL ENTRIES IN g_t_mara

WHERE matnr = g_t_mara-matnr AND

vkorg IN s_vkorg AND

vtweg IN s_vtweg.

hope that helps.

pls reward if sueful...

[Former Member]

Hi Sandeep

Select..Endselect will take more time because the Database table is locked for most of the time and everytime the data record has to be fetched from the database table in a loop.

so better to use INTO table ITAb ..to avaoid Endselect..

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

In order to avoid nested select statements we can go

for "FOR ALL ENTRIES".First fetch values from database

table to internal table and only for the values in the

internal table we can fetch values from other database

table based on conditions in the where clause.

it is mandetory that we have to declare allthe primary keys

in either select Stmt or in where condition. orelse

duplicate records will not get selected.

to avoid duplaction of records.

check this

http://www.sap-img.com/abap/several-nested-inner-join-statements-can-be-inefficient.htm

Reward all helpfull answers

Regards

Pavan

[Former Member]

Hi Sandeep,

SELECT * FROM means it will select all the available records,

SELECT SINGLE * FROM means it will select only particular single record only.

EXAMPLE:

**************

SELECT SINGLE MATNR FROM MARA INTO ITAB WHERE MATNR = '1300-340'.

It will select only one record.

SELECT * FROM MATNR FROM MARA INTO ITAB WHERE MATNR = '1300-340'.

It will select more than one record related to that material number.

IF USEFULL REWARD

[Former Member]

Hi

if any doubt refer this,

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

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

The columns of the result table will have the same format as the column references s1 ... sn.

If si stands for a field f, MAX( f ), MIN( f ), or SUM( f ), the corresponding column in the result set will have the same ABAP Dictionary format as f. For COUNT( DISTINCT f ) or COUNT( * ) the column has the type INT4. For AVG( f ) it has the type FLTP.

If you use aggregate functions with one or more database fields in the SELECT clause, you must include all of the database fields that are not used in the aggregate function in the GROUP BY clause. The result of the selection in this case is a table.

If the SELECT clause only contains aggregate functions, the result of the selection will be a single entry. In this case, SELECT does not have a corresponding ENDSELECT statement

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

Effect:

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn if the variable source_text contains the list s1 ... sn as source code (see Dynamic Source Code in Open SQL). If source_text is empty, the statement works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] *.

if useful reward points

Thanks

senthil

[Former Member]

Hi

Select single will used for selecting single records from db by using all key fields ..

For all entries is used to select no of record from a DB with respect to a internal table. Here u need to have a common field in Db as well as in internal table.

Reward me if its helpful.

Regards

Ravi

[Former Member]

Difference between select single * from and Select .... up to <N> rows?. where N =  1,2,3..... n.

Both are used to validating the field values. But the difference is

1. select single * from ---  It validate the single field of the first row of the records based on user input.

2.select.... up to <N> rows----- It validate the first <N> row of the records based on user input.