topic Re: select single * in Application Development and Automation Discussions

select single *

Former Member — Wed, 05 Sep 2007 07:11:27 GMT

SELECT SINGLE * INTO CORRESPONDING FIELDS OF ITAB

what is the meaning ... explain me pls.....

Regards,

swathi.

Re: select single *

Former Member — Wed, 05 Sep 2007 07:12:36 GMT

only one record fetch from database

Re: select single *

Former Member — Wed, 05 Sep 2007 07:13:43 GMT

- lines

Syntax

... { SINGLE [FOR UPDATE] }

| { [DISTINCT] { } } ... .

Alternatives:

1. ... SINGLE [FOR UPDATE]

2. ... [DISTINCT] { }

Effect

The data in lines specifies that the resulting set has either multiple lines or a single line.

Alternative 1

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

Alternative 2

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

Re: select single *

Former Member — Wed, 05 Sep 2007 07:14:48 GMT

Select SINGLE -> To fetch only a single records

INTO CORRESPONDING FIELDS OF ITAB... -> ITAB will have several fields & all of them need not be same as the table from which you are retrieving data. So into corresponding automatically populates fields which are common in table & ITAB.

Re: select single *

Former Member — Wed, 05 Sep 2007 07:20:52 GMT

Hi,

This statement fetches single record ( all fields in the table ) and those fetched fields from database table will be put into the appropriate fields in the Internal table.

Rewards points if useful.

Regards,

Vimal

Re: select single *

Former Member — Wed, 05 Sep 2007 07:20:56 GMT

The * means it needs to get out all fields of a database table, INTO means it wants to transfer the selected data to a work area, but if the work area has a different structure you can use the option CORRESPONDING in order to transfer the data of the fields with the same name only:

DATA: BEGIN OF ITAB,
             BUKRS TYPE BUKRS,
             MY_FIELDS,
             BELNR TYPE BELNR,
           END   OF ITAB.

SELECT SINGLE * FROM BKPF INTO CORRESPONDING FIELDS OF ITAB..

Only the data in the fields BUKRS and BELNR of BKPF will be transfered in ITAB.

SELECT SINGLE means only one record will be extracted: so it should use all key fields in WHERE conditions.

Max

Re: select single *

Former Member — Wed, 05 Sep 2007 07:22:09 GMT

Select single f1 command picks only a single record into a work area or a field string, adding corresponds extention to it would meen that only that component f1 of the work area specifield in the select single command would get populated. For our case it is f1, if at all this componenetexists in the work area.

Regards,

Rama Murthy.P

Re: select single *

Former Member — Wed, 05 Sep 2007 07:30:43 GMT

Hi Swathi,

If we want to fetch a single record from database then we use select single command.

Select Single * fetches single record with all fields.

But select single requires all key fields to be mensioned in where clause.

If we don't have all key fields , use select .................up to 1 rows.

select matnr werks lgort into corresponding fields of itab from mard <b>up to 1 rows</b>

where matnr = '0000000000000012'.

Reward points if helpful.

Regards,

Hemant