select

[Former Member]

thanx in advance ..

kindlt key in a sample code to explain select statement,,,

[Former Member]

Hai Ravi

Hai ravi

Basic form

SELECT result [target] FROM source [where] [GROUP BY fields] [ORDER BY order].

Effect

Retrieves an extract and/or a set of data from a database table or view (see Relational database ). SELECT belongs to the OPEN SQL command set.

Each SELECT command consists of a series of clauses specifying different tasks:

The SELECT result clause specifies

whether the result of the selection is a table or a single record,

which columns the result is meant to have and

whether the result is allowed to include identical lines.

The INTO target clause specifies the target area into which the selected data is to be read. If the target area is an internal table, the INTO clause specifies

whether the selected data is to overwrite the contents of the internal table or

whether the selected data is to be appended to the contents and

whether the selected data is to be placed in the internal table all at once or in several packets.

The INTO clause can also follow the FROM clause.

You can omit the INTO clause. The system then makes the data available in the table work area (see TABLES ) dbtab . If the SELECT clause includes a "*", the command is processed like the identical SELECT * INTO dbtab FROM dbtab statement. If the SELECT clause contains a list a1 ... an , the command is executed like SELECT a1 ... an INTO CORRESPONDING FIELDS OF dbtab FROM dbtab .

If the result of the selection is meant to be a table, the data is usually (for further information, see INTO -Klausel ) read line by line within a processing loop introduced by SELECT and concluded by ENDSELECT . For each line read, the processing passes through the loop once. If the result of the selection is meant to be a single record, the closing ENDSELECT is omitted.

The FROM source clause the source (database table or view ) from which the data is to be selected. It also determines

the type of client handling,

the behavior for buffered tables and

the maximum number of lines to be read.

The WHERE where clause specifies the conditions which the result of the selection must satisfy. It thus determines the lines of the result table. Normally - i.e. unless a client field is specified in the WHERE clause - only data of the current client is selected. If you want to select across other clients, the FROM clause must include the addition ... CLIENT SPECIFIED .

The GROUP-BY fields clause combines groups of lines together into single lines. A group is a set of lines which contain the same value for every database field in the GROUP BY clause.

The ORDER-BY order clause stipulates how the lines of the result table are to be ordered.

Each time the SELECT statement is executed, the system field SY-DBCNT contains the number of lines read so far. After ENDSELECT , SY-DBCNT contains the total number of lines read.

The return code value is set as follows:

SY-SUBRC = 0 At least one line was read.

SY_SUBRC = 4 No lines were read.

SY-SUBRC = 8 The search key was not fully qualified.

(nur bei SELECT SINGLE ). The returned single record is any line of the solution set.

Example

Output the passenger list for the Lufthansa flight 0400 on 28.02.1995:

TABLES SBOOK.

SELECT * FROM SBOOK

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'

ORDER BY PRIMARY KEY.

WRITE: / SBOOK-BOOKID, SBOOK-CUSTOMID, SBOOK-CUSTTYPE,

SBOOK-SMOKER, SBOOK-LUGGWEIGHT, SBOOK-WUNIT,

SBOOK-INVOICE.

ENDSELECT.

Note

Performance

In client/server environments, storing database tables in local buffers (see SAP buffering ) can save considerable amounts of time because the time required to make an access via the network is much more than that needed to access a locally buffered table.

Notes

A SELECT command on a table for which SAP buffering is defined in the ABAP/4 Dictionary is normally satisfied from the SAP buffer by bypassing the database. This does not apply with

- SELECT SINGLE FOR UPDATE

- SELECT DISTINCT in the SELECT clause ,

- BYPASSING BUFFER in the FROM clause ,

- ORDER BY f1 ... fn in the ORDER-BY clause ,

- aggregate functions in the SELECT clause ,

- when using IS [NOT] NULL WHERE condition ,

or if the generic key part is not qualified in the WHERE-Bedingung for a generically buffered table.

Authorization checks are not supported by the SELECT statement, so you must program these yourself.

In dialog systems, the database system locking mechanism cannot always guarantee to synchronize the simultaneous access of several users to the same dataset. In many cases, it is therefore advisable to use the SAP locking mechanism .

Changes to data in a database are only finalized after a database commit (see LUW ). Prior to this, any database update can be reversed by a database rollback (see Programming transactions ). At the lowest isolation level (see the section on the "uncommitted read" under Locking mechanism ), this can result in the dataset selected by the SELECT command not really being written to the database. While a program is selecting data, a second program can add, change or delete lines at the same time. Then, the changes made by the second program are reversed by rolling back the database system. The selection of the first program thus reflects only a very temporary state of the database. If such "phantom data" is not acceptable for a program, you must either use the SAP locking mechanism or at least set the isolation level of the database system to "committed read" (see Locking mechanism ).

In a SELECT-ENDSELECT loop, the CONTINUE statement terminates the current loop pass prematurely and starts the next.

If one of the statements in a SELECT ... ENDSELECT loop results in a database commit, the cursor belonging to the SELECT ... ENDSELECT loop is lost and the processing terminates with a runtime error. Since each screen change automatically generates a database commit, statements such as CALL SCREEN , CALL DIALOG , CALL TRANSACTION or MESSAGE are not allowed within a SELECT ... ENDSELECT loop.

Related OPEN CURSOR , FETCH und CLOSE CURSOR

Thanks & regards

Sreeni

[former_member404244]

Hi,

what exactly u want..

Regards,

Nagaraj Kumar

[former_member184569]

Hi Swami,

SELECT

Basic form

SELECT select clause [INTO clause] FROM from clause [WHERE cond1] [GROUP BY fields1] [HAVING cond2] [ORDER BY fields2].

Effect

Reads a selection and/or a summary of data from one or more database tables and/or views (see relational database). SELECT is an OPEN SQL statement.

Each SELECT statement consists of a series of clauses, each with a differen task:

The SELECT clause select clause describes

Whether the result of the selection should be a single record or a table,

Which columns should be contained in the result,

Whether identical lines may occur in the result.

The INTO clause INTO clause determines the target area into which the selected data is read. If the target area is an internal table, the INTO clause specifies:

Whether you want to overwrite the contents of the internal table or

Append the results to the internal table, and

Whether you want to place the data in the internal table in a single step, or in a series of packages.

The INTO clause can also occur after the FROM clause. You may omit it if

The SELECT clause contains a "*",

The FROM clause does not contain a JOIN, and

You have declared a table work area dbtab in your program using TABLES.

The data, if it exists in the database, is then made available using the table work area dbtab. The statement is then processed further like the SELECT * INTO dbtab FROM dbtab statement, which has the same effect.

If the result of the selection is a table, the data is normally read line by line (for further information, see INTO clause) in a processing loop, which is introduced with SELECT and concludes with ENDSELECT. The loop is processed once for each line that is read. If you want the result of the selection to be a single record, there is no concluding ENDSELECT statement.

The FROM clause FROM clause specifies the source of the data (database tables or views), from which you want to select the data. It also specifies the

Client handling,

Behavior for buffered tables, and

The maximum number of lines that you want to read.

The WHERE clause cond1 specifies the conditions that the result of the selection must satisfy. By default, only data from the current client is selected (without you having to specify the client field specifically in the WHERE clause). If you want to select data from several clients, you must use the ... CLIENT SPECIFIED addition in the FROM clause.

The GROUP BY clause fields1 combines groups of lines into single lines of the result table. A group is a set of records with the same value of each database field listed in the GROUP BY clause.

The HAVING clause cond2 specifies conditions for the combined lines of the result table.

The ORDER BY clause fields2 specifies how the records in the result table should be arranged.

The system field SY-DBCNT contains the number of lines read so far ecah time the SELECT statement is executed. After ENDSELECT, SY-DBCNT contains the total number of records read.

The return code is set as follows:

SY-SUBRC = 0:

The result table contains at least one record.

SY-SUBRC = 4:

The result table is empty.

SY-SUBRC = 8:

Applies only to SELECT SINGLE FOR UPDATE: You did not specify all of the primary key fields in the WHERE condition. The result table is empty.

Note

The SELECT COUNT( * ) FROM ... statement returns a result table containing a single line with the result 0 if there are no records in the database table that meet the selection criteria. In an exception to the above rule, SY-SUBRC is set to 4 in this case, and SY-DBCNT to zero.

Example

Displaying the passenger list for Lufthansa flight 0400 on 2/28/1995:

DATA: WA_SBOOK TYPE SBOOK.

SELECT * FROM SBOOK INTO WA_SBOOK

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'

ORDER BY PRIMARY KEY.

WRITE: / WA_SBOOK-BOOKID, WA_SBOOK-CUSTOMID,

WA_SBOOK-CUSTTYPE, WA_SBOOK-SMOKER,

WA_SBOOK-LUGGWEIGHT, WA_SBOOK-WUNIT,

WA_SBOOK-INVOICE.

ENDSELECT.

Note

Performance:

Storing database tables in a local buffer (see SAP buffering) can lead to considerable time savings in a client/server environment, since the access time across the network is considerably higher than that required to access a locally-buffered table.

Notes

A SELECT statement on a table for which SAP buffering has been declared in the ABAP Dictionary usually reads data from the SAP buffer without accessing the database. This does not apply when you use:

- SELECT SINGLE FOR UPDATE or

- SELECT DISTINCT in the SELECT clause,

- BYPASSING BUFFER in the FROM clause,

- ORDER BY f1 ... fn in the ORDER BY clause,

- Aggregate functions in the SELECT clause,

- When you use IS [NOT] NULL in the WHERE condition,

or when the table has generic buffering and the appropriate section of the key is not specified in the WHERE condition.

The SELECT statement does not perform its own authorization checks. You should write your own at program level.

Proper synchronization of simultaneous access by several users to the same set of data cannot be assured by the database lock mechanism. In many cases, you will need to use the SAP locking mechanism.

Changes to data in the database are not made permanent until a database commit (see LUW) occurs. Up to this point, you can undo any changes using a databse rollback (see Programming Transactions). At the lowest isolation level (see lock mechanism ), the "Uncommitted Read", it can sometimes be the case that data selected by a SELECT statement was never written to the database. While a program is selecting data, a second program could be adding data to, changing data in, or deleting data from the database at the same time. If the second program then executes a rollback, the first program has selected a set of data that may only represent a temporary state from the database. If this kind of "phantom data" is unacceptable in the context of your application, you must either use the SAP locking mechanism or change the isolation level of the database system to at least "Committed Read" (see locking mechanism).

In a SELECT - ENDSELECT loop, the CONTINUE statement terminates the current loop pass and starts the next.

If a SELECT - ENDSELECT loop contains a statement that triggers a database commit, the cursor belonging to the loop is lost and a program termination and runtime error occur. Remote Function Calls and changes of screen always lead to a database commit. The following statements are consequently not allowed wihtin a SELECT-ENDSELECT loop: CALL FUNCTION ... STARTING NEW TASK , CALL FUNCTION ... DESTINATION , CALL FUNCTION ... IN BACKGROUND TASK , CALL SCREEN, CALL DIALOG, CALL TRANSACTION, and MESSAGE.

Swami,

Check in the SAP help. In addition you will get detailed explanation of all the clauses.

Thanks,

Susmitha.

Dont forget to reward points for helpful answers

[Former Member]

HI

GOOD

Basic form

SELECT result [target] FROM source [where] [GROUP BY fields] [ORDER BY order].

Effect

Retrieves an extract and/or a set of data from a database table or view (see Relational database ). SELECT belongs to the OPEN SQL command set.

Each SELECT command consists of a series of clauses specifying different tasks:

The SELECT result clause specifies

whether the result of the selection is a table or a single record,

which columns the result is meant to have and

whether the result is allowed to include identical lines.

The INTO target clause specifies the target area into which the selected data is to be read. If the target area is an internal table, the INTO clause specifies

whether the selected data is to overwrite the contents of the internal table or

whether the selected data is to be appended to the contents and

whether the selected data is to be placed in the internal table all at once or in several packets.

The INTO clause can also follow the FROM clause.

You can omit the INTO clause. The system then makes the data available in the table work area (see TABLES ) dbtab . If the SELECT clause includes a "*", the command is processed like the identical SELECT * INTO dbtab FROM dbtab statement. If the SELECT clause contains a list a1 ... an , the command is executed like SELECT a1 ... an INTO CORRESPONDING FIELDS OF dbtab FROM dbtab .

If the result of the selection is meant to be a table, the data is usually (for further information, see INTO -Klausel ) read line by line within a processing loop introduced by SELECT and concluded by ENDSELECT . For each line read, the processing passes through the loop once. If the result of the selection is meant to be a single record, the closing ENDSELECT is omitted.

The FROM source clause the source (database table or view ) from which the data is to be selected. It also determines

the type of client handling,

the behavior for buffered tables and

the maximum number of lines to be read.

The WHERE where clause specifies the conditions which the result of the selection must satisfy. It thus determines the lines of the result table. Normally - i.e. unless a client field is specified in the WHERE clause - only data of the current client is selected. If you want to select across other clients, the FROM clause must include the addition ... CLIENT SPECIFIED .

The GROUP-BY fields clause combines groups of lines together into single lines. A group is a set of lines which contain the same value for every database field in the GROUP BY clause.

The ORDER-BY order clause stipulates how the lines of the result table are to be ordered.

Each time the SELECT statement is executed, the system field SY-DBCNT contains the number of lines read so far. After ENDSELECT , SY-DBCNT contains the total number of lines read.

The return code value is set as follows:

SY-SUBRC = 0 At least one line was read.

SY_SUBRC = 4 No lines were read.

SY-SUBRC = 8 The search key was not fully qualified.

(nur bei SELECT SINGLE ). The returned single record is any line of the solution set.

Example

Output the passenger list for the Lufthansa flight 0400 on 28.02.1995:

TABLES SBOOK.

SELECT * FROM SBOOK

WHERE

CARRID = 'LH ' AND

CONNID = '0400' AND

FLDATE = '19950228'

ORDER BY PRIMARY KEY.

WRITE: / SBOOK-BOOKID, SBOOK-CUSTOMID, SBOOK-CUSTTYPE,

SBOOK-SMOKER, SBOOK-LUGGWEIGHT, SBOOK-WUNIT,

SBOOK-INVOICE.

ENDSELECT.

Note

Performance

In client/server environments, storing database tables in local buffers (see SAP buffering ) can save considerable amounts of time because the time required to make an access via the network is much more than that needed to access a locally buffered table.

Notes

A SELECT command on a table for which SAP buffering is defined in the ABAP/4 Dictionary is normally satisfied from the SAP buffer by bypassing the database. This does not apply with

- SELECT SINGLE FOR UPDATE

- SELECT DISTINCT in the SELECT clause ,

- BYPASSING BUFFER in the FROM clause ,

- ORDER BY f1 ... fn in the ORDER-BY clause ,

- aggregate functions in the SELECT clause ,

- when using IS [NOT] NULL WHERE condition ,

or if the generic key part is not qualified in the WHERE-Bedingung for a generically buffered table.

Authorization checks are not supported by the SELECT statement, so you must program these yourself.

In dialog systems, the database system locking mechanism cannot always guarantee to synchronize the simultaneous access of several users to the same dataset. In many cases, it is therefore advisable to use the SAP locking mechanism .

Changes to data in a database are only finalized after a database commit (see LUW ). Prior to this, any database update can be reversed by a database rollback (see Programming transactions ). At the lowest isolation level (see the section on the "uncommitted read" under Locking mechanism ), this can result in the dataset selected by the SELECT command not really being written to the database. While a program is selecting data, a second program can add, change or delete lines at the same time. Then, the changes made by the second program are reversed by rolling back the database system. The selection of the first program thus reflects only a very temporary state of the database. If such "phantom data" is not acceptable for a program, you must either use the SAP locking mechanism or at least set the isolation level of the database system to "committed read" (see Locking mechanism ).

In a SELECT-ENDSELECT loop, the CONTINUE statement terminates the current loop pass prematurely and starts the next.

If one of the statements in a SELECT ... ENDSELECT loop results in a database commit, the cursor belonging to the SELECT ... ENDSELECT loop is lost and the processing terminates with a runtime error. Since each screen change automatically generates a database commit, statements such as CALL SCREEN , CALL DIALOG , CALL TRANSACTION or MESSAGE are not allowed within a SELECT ... ENDSELECT loop.

Related OPEN CURSOR , FETCH und CLOSE CURSOR

THANKS

MRUTYUN

[former_member188685]

Hi,

Refer the <b>ABAPDOCU</b> transaction, you can get good Examples and it is well documented also.

select is used in many places depending on requirement, it can be normal select, to fetch the data into internal table based on conditions.

it can be nested selects.

Joins,

select single to fetch single records(all keys must in where condition).

select upto rows depends on your requirement you can fetch single or more.

Regards

vijay