reg: select statement ?

[Former Member]

hello everyboby,

what are clause in Select Statement?

what is the difference between "where" and "having" in select statement?

[Former Member]

The Open SQL statement for reading data from database tables is:

SELECT <result>

INTO <target>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

The SELECT statement is divided into a series of simple clauses, each of which has a different part to play in selecting, placing, and arranging the data from the database.

You can only use the HAVING clause in conjunction with the GROUP BY clause.

To select line groups, use:

SELECT <lines> <s1> [AS <a1>] <s2> [AS <a2>] ...

<agg> <sm> [AS <am>] <agg> <sn> [AS <an>] ...

...

GROUP BY <s1> <s2> ....

HAVING <cond>.

The conditions <cond> that you can use in the HAVING clause are the same as those in the SELECT clause, with the restrictions that you can only use columns from the SELECT clause, and not all of the columns from the database tables in the FROM clause. If you use an invalid column, a runtime error results.

On the other hand, you can enter aggregate expressions for all columns read from the database table that do not appear in the GROUP BY clause. This means that you can use aggregate expressions, even if they do not appear in the SELECT clause. You cannot use aggregate expressions in the conditions in the WHERE clause.

As in the WHERE clause, you can specify the conditions in the HAVING clause as the contents of an internal table with line type C and length 72.

Example

DATA WA TYPE SFLIGHT.

SELECT CONNID

INTO WA-CONNID

FROM SFLIGHT

WHERE CARRID = 'LH'

GROUP BY CONNID

HAVING SUM( SEATSOCC ) > 300.

WRITE: / WA-CARRID, WA-CONNID.

ENDSELECT.

This example selects groups of lines from database table SFLIGHT with the value ‘LH’ for CARRID and identical values of CONNID. The groups are then restricted further by the condition that the sum of the contents of the column SEATSOCC for a group must be greater than 300.

The GROUP BY clause summarizes several lines from the database table into a single line of the selection.

The GROUP BY clause allows you to summarize lines that have the same content in particular columns. Aggregate functions are applied to the other columns. You can specify the columns in the GROUP BY clause either statically or dynamically.

Specifying Columns Statically

To specify the columns in the GROUP BY clause statically, use:

SELECT <lines> <s1> [AS <a 1>] <s 2> [AS <a 2>] ...

<agg> <sm> [AS <a m>] <agg> <s n> [AS <a n>] ...

...

GROUP BY <s1> <s 2> ....

To use the GROUP BY clause, you must specify all of the relevant columns in the SELECT clause. In the GROUP BY clause, you list the field names of the columns whose contents must be the same. You can only use the field names as they appear in the database table. Alias names from the SELECT clause are not allowed.

All columns of the SELECT clause that are not listed in the GROUP BY clause must be included in aggregate functions. This defines how the contents of these columns is calculated when the lines are summarized.

Specifying Columns Dynamically

To specify the columns in the GROUP BY clause dynamically, use:

... GROUP BY (<itab>) ...

where <itab> is an internal table with line type C and maximum length 72 characters containing the column names <s 1 > <s 2 > .....

Example

DATA: CARRID TYPE SFLIGHT-CARRID,

MINIMUM TYPE P DECIMALS 2,

MAXIMUM TYPE P DECIMALS 2.

SELECT CARRID MIN( PRICE ) MAX( PRICE )

INTO (CARRID, MINIMUM, MAXIMUM)

FROM SFLIGHT

GROUP BY CARRID.

WRITE: / CARRID, MINIMUM, MAXIMUM.

ENDSELECT.

refer..

http://help.sap.com/saphelp_erp2005/helpdata/en/fc/eb3000358411d1829f0000e829fbfe/frameset.htm

[Former Member]

Hai Selva

Go through the following Document for Select Statement

SELECT

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

or check with the following Link

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/select.htm

Regards

Sreeni

[Former Member]

Hi Selva,

Goto transaction se38.

Create a report program and in the editor screen, type <b>select</b>.

Then place the cursor over <b>select</b>and press <b>F1</b> for help.

You'll get the whole documentation on the <b>select</b> query.

Mark useful answers.

Regards,

Tanuja.