SELECT QUREY

[Former Member]

1. what is the purpose of DISTINCT addtion in

select statement ?

2. what is the purpose of SINGLE addtion in

select statement ?

3. what is the difference between DISTINCT and SINGLE additions in SELECT statement?

KIndly help me, it is urgent.

[Former Member]

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.

Reading Several Lines

To read a several entries from the database, use the following:

SELECT [DISTINCT] <cols> ... WHERE ...

If you do not use DISTINCT (<lines> is then empty), the system reads all of the lines that satisfy the WHERE condition. If you use DISTINCT, the system excludes duplicate entries.

The result of the selection is a table. The target area of the INTO clause can be an internal table with a line type appropriate for <cols>. If the target area is not an internal table, but a flat structure, you must include an ENDSELECT statement after the SELECT statement:

SELECT [DISTINCT] <cols> ... WHERE ...

...

ENDSELECT.

The lines are read in a loop one by one into the target area specified in the INTO clause You can work with the target area within the loop.

If at least one line is read, SY-SUBRC is set to 0 after the statement (or loop) has been processed. If no lines are read, SY-SUBRC is set to 4. The number of lines read is placed in the system field SY-DBCNT. Within the loop, SY-DBCNT already contains the number of lines that have already been passed to the target area.

Technically, it is possible to nest SELECT loops. However, for performance reasons, you should avoid doing so. If you want to read interdependent data from more than one database table, you can use a join in the FROM clause or a subquery in the WHERE clause.

Please reward if helpful!!!!!!!!!!

[vinod_vemuru2]

Hi Nilesh,

Select single is used to select single record which satisfies where clause. Even if we have multiple records which satisfies where clause it will select only one record. We will use select single only if we are sure that we have only one record for the where clause conditions.

EG: Selection screen validations using check tables.

Select distinct is used to select all the distinct records. i.e we can select multiple distinct records for the given where clause conditions. Duplicate records are automatically eliminated.

Thanks,

Vinod.

[Former Member]

Hi,

DISTINCT :

SELECT [DISTINCT] <cols> ... WHERE ...

If you use DISTINCT, the system excludes duplicate entries.

If you do not use DISTINCT (<lines> is then empty), the system reads all of the lines that satisfy the WHERE condition.

SINGLE :

If you use select single it will select the first that satisy the where condition.

[Former Member]

Hi

Pls go through this

SINGLE [FOR UPDATE]

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. The addition ORDER BY can also 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.

Notes

When SINGLE is being specified, the lines to be read should be clearly specified in the WHERE condition, for the sake of efficiency. When the data is read from a database table, the system does this by specifying comparison values for the primary key.

If accessing tables for which SAP buffering is planned for single records, the SAP buffer is bypassed if the addition SINGLE is not specified. This behavior depends on the current implementation of the database interface and may change in future releases. In particular, it should not be used to bypass the SAP buffer. You should use the explicit addition BYPASSING BUFFER for this instead.

The addition SINLGE is not permitted in a subquery.

[DISTINCT] { }

Effect

If SINGLE is not specified and if columns does not contain only aggregate expressions, the resulting set has multiple lines. All database lines that are selected by the remaining additions of the SELECT command are included in the resulting list. If the ORDER BY addition is not used, the order of the lines in the resulting list is not defined and, if the same SELECT command is executed multiple times, the order may be different each time. A data object specified after INTO can be an internal table and the APPENDING addition can be used. If no internal table is specified after INTO or APPENDING, the SELECT command triggers a loop that has to be closed using ENDSELECT.

If multiple lines are read without SINGLE, the DISTINCT addition can be used to exclude duplicate lines from the resulting list. If DISTINCT is used, the SELECT command circumvents SAP buffering. DISTINCT cannot be used in the following situations:

If a column specified in columns has the type STRING, RAWSTRING, LCHAR or LRAW

If the system tries to access pool or cluster tables and single columns are specified in columns.

Notes

When specifying DISTINCT, note that this requires the execution of sort operations in the database system, and the SELECT statement therefore bypasses the SAP buffer.

If its useful reward points.