different form of SELECT

Hi Prabhu,

In ABAP Editor type "SELECT" ans select it and press F4.

Then you will see a lot of Select Querys.

Thanks.

Defining Selections

The SELECT clause defines the structure of the result set (selection) that you want to read from the database.

The selection can be flat (one line) or tabular (several lines). You can specify whether to accept or exclude duplicate entries. The SELECT clause also specifies the names of the columns to be read. You can replace the names of the database fields with alternative names. Aggregate functions can be applied to individual columns.

The SELECT clause can be divided into two parts for lines and columns:

SELECT . 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 . If the target area is not an internal table, but a flat structure, you must include an ENDSELECT statement after the SELECT statement:

SELECT ( ( ( COL_2 )

INTO RESULT

FROM TEST.

WRITE RESULT.

The following table shows the results of this program extract according to different combinations of aggregate expressions specification in the SELECT clause determines the structure (line type) of the target area.

If you select a single line, the target area must be flat. If you select more than one line, the target area may be either tabular or flat. If the target area is flat, you need to use a SELECT loop.

When you select all of the columns, the target area must either be a structure or convertible into one. When you select individual columns, the target area can be a component of a structure or a single field.

The elementary data types in the selection in the SELECT clause are Dictionary types. These Dictionary types must be able to be converted into the ABAP data types of the corresponding elementary components of the target area. For a table of data types, refer to Data Types in the ABAP Dictionary.

Specifying a Flat Work Area

You can specify a flat work area regardless of whether you are reading from a single line or from several. To read data into one, use the following in the INTO clause:

SELECT ... INTO as the database table itself. You must declare this table work area using the TABLES statement. Before Release 4.0, this was necessary before you could read data from a database table at all, and was frequently used as an implicit work area. Nowadays, table work areas are still useful as interface work areas, but should no longer be used as the work area in the SELECT statement. Like internal tables without header lines, having different names for the source and target areas makes programs clearer.

Specifying Internal Tables

When you read several lines of a database table, you can place them in an internal table. To do this, use the following in the INTO clause:

SELECT ... INTO|APPENDING TABLE JOIN LEFT JOIN lines in the selection set.

Examples

Specifying a database table statically:

REPORT demo_select_static_database.

DATA wa TYPE scarr.

SELECT *

INTO wa

FROM scarr UP TO 4 ROWS.

WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

The output is:

The system reads four lines from the database table SCARR.

Specifying a database table dynamically:

REPORT demo_select_dynamic_database.

DATA wa TYPE scarr.

DATA name(10) TYPE c VALUE 'SCARR'.

SELECT *

INTO wa

FROM (name) CLIENT SPECIFIED

WHERE mandt = '000'.

WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

A condition for the MANDT field is allowed, since the example uses the CLIENT SPECIFIED option. If NAME had contained the value ‘scarr’ instead of ‘SCARR’, a runtime error would have occurred.

Inner join:

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

This example links the columns CARRID, CONNID, FLDATE, and BOOKID of the table SPFLI, SFLIGHT, and SBOOK, and creates a list of booking numbers for all flights from Frankfurt to New York that are not fully booked. An alias name is assigned to each table.

Left outer join:

REPORT demo_select_left_outer_join.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid AND

p~cityfrom = 'FRANKFURT'.

LOOP AT itab INTO wa.

WRITE: / wa-carrid, wa-carrname, wa-connid.

ENDLOOP.

The output might look like this:

The example links the columns CARRID, CARRNAME, and CONNID of the tables SCARR and SPFLI using the condition in the left outer join that the airline must fly from Frankfurt. All other airlines have a null value in the CONNID column in the selection.

If the left outer join is replaced with an inner join, the list looks like this:

Only lines that fulfill the ON condition are included in the selection.

Selecting Lines

The WHERE clause restricts the number of lines selected by specifying conditions that must be met.

As well as in the SELECT statement, the WHERE clause is also used in the OPEN CURSOR, UPDATE, and DELETE statements. The general form of the WHERE clause is:

SELECT ... WHERE <

not equal to

LT

less than

<

less than

LE

less than or equal to

<=

less than or equal to

GT

greater than

greater than

GE

greater than or equal to

>=

greater than or equal to

The values of the operands are converted if necessary. The conversion may be dependent on the platform and codepage.

Values in Intervals

To find out whether the value of a column lies within a particular interval, use:

SELECT ... WHERE BETWEEN is between the values of the data objects LIKE matches the pattern in the data object IN ( is in the list IN is contained in the results set of the scalar subquery .

To find out whether the selection of a subquery contains lines at all, use:

SELECT ... WHERE EXISTS contains at least one line. The subquery does not have to be scalar.

You cannot check a subquery in the ON condition of the FROM clause.

Checking Selection Tables

To find out whether the value of a column satisfies the conditions in a selection table, use:

SELECT ... WHERE IN satisfy the conditions stored in IS NULL ...

The condition is true if the value of is null.

Negating Conditions

To negate the result of a condition, use:

SELECT ... WHERE NOT ASCENDING

ASCENDING ...

The lines are sorted by the columns

You can only enter a single field in the SELECT clause.

Subqueries in Conditions

A non-scalar subquery ... [can only have a WHERE or HAVING clause

in the EXISTS condition. This condition is true if the result set of the subquery contains at least one line.

Scalar Subqueries in Conditions

As well as in the above condition, you can also use scalar subqueries in further conditions.

Checking a Value of the Subquery

The following is a possible condition with scalar subqueries:

... IN is contained in the results set of the scalar subquery = ALL ( select seatsocc

FROM sflight

WHERE carrid = wa-carrid AND

connid = wa-connid )

ORDER BY seatsmax.

WRITE: / plane, seats.

ENDSELECT.

The list output, after double-clicking a line, looks like this:

The detail list displays all aircraft types that have fewer seats than the currently-allocated aircraft type, but enough to carry all of the passengers currently booked on the flight.

Hi Prabhu

Regarding select statements,try these links :

http://www.erpgenie.com/abap/performance.htm

http://erpgenie.com/abaptips/index2.php?option=com_content&do_pdf=1&id=230

http://www.sap-img.com/abap/difference-between-select-single-and-select-upto-one-rows.htm(for select single)

http://www.erpgenie.com/abaptips/content/view/239/62/ (select distinct)

http://help.sap.com/saphelp_nw04/helpdata/en/3b/b3ddedd3c542bbb3ee2e1fca627b9e/frameset.htm (select avg)

for-all-entries (for all entries)

thankx

Neerja

Hi

see this document i am sending you all the SELECT statements Regarding performance point of view

<b>Reward if useful</b>

<b>Ways of Performance Tuning</b>

1. Selection Criteria

2. Select Statements

• Select Queries

• SQL Interface

• Aggregate Functions

• For all Entries

• Select Over more than one Internal table

<b>Selection Criteria</b>1. Restrict the data to the selection criteria itself, rather than filtering it out using the ABAP code using CHECK statement.

2. Select with selection list.

Note: It is suggestible to make at least on field mandatory in Selection-Screen as mandatory fields restrict the data selection and hence increasing the performance.

Points # 1/2

SELECT * FROM SBOOK INTO SBOOK_WA.

CHECK: SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

ENDSELECT.

The above code can be much more optimized by the code written below which avoids CHECK, selects with selection list

SELECT CARRID CONNID FLDATE BOOKID FROM SBOOK INTO TABLE T_SBOOK

WHERE SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

<b>Select Statements Select Queries</b>

1. Avoid nested selects

2. Select all the records in a single shot using into table clause of select statement rather than to use Append statements.

3. When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

4. For testing existence , use Select.. Up to 1 rows statement instead of a Select-Endselect-loop with an Exit.

5. Use Select Single if all primary key fields are supplied in the Where condition .

Point # 1

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

Note: A simple SELECT loop is a single database access whose result is passed to the ABAP program line by line. Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop. One should therefore use nested SELECT loops only if the selection in the outer loop contains very few lines or the outer loop is a SELECT SINGLE statement.

Point # 2

SELECT * FROM SBOOK INTO SBOOK_WA.

CHECK: SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

ENDSELECT.

The above code can be much more optimized by the code written below which avoids CHECK, selects with selection list and puts the data in one shot using into table

SELECT CARRID CONNID FLDATE BOOKID FROM SBOOK INTO TABLE T_SBOOK

WHERE SBOOK_WA-CARRID = 'LH' AND

SBOOK_WA-CONNID = '0400'.

Point # 3

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields . In certain scenarios, it is advisable to check whether a new index can speed up the performance of a program. This will come handy in programs that access data from the finance tables.

Point # 4

SELECT * FROM SBOOK INTO SBOOK_WA

UP TO 1 ROWS

WHERE CARRID = 'LH'.

ENDSELECT.

The above code is more optimized as compared to the code mentioned below for testing existence of a record.

SELECT * FROM SBOOK INTO SBOOK_WA

WHERE CARRID = 'LH'.

EXIT.

ENDSELECT.

Point # 5

If all primary key fields are supplied in the Where condition you can even use Select Single.

Select Single requires one communication with the database system, whereas Select-Endselect needs two.

<b>Select Statements contd.. SQL Interface</b>

1. Use column updates instead of single-row updates

to update your database tables.

2. For all frequently used Select statements, try to use an index.

3. Using buffered tables improves the performance considerably.

Point # 1

SELECT * FROM SFLIGHT INTO SFLIGHT_WA.

SFLIGHT_WA-SEATSOCC =

SFLIGHT_WA-SEATSOCC - 1.

UPDATE SFLIGHT FROM SFLIGHT_WA.

ENDSELECT.

The above mentioned code can be more optimized by using the following code

UPDATE SFLIGHT

SET SEATSOCC = SEATSOCC - 1.

Point # 2

SELECT * FROM SBOOK CLIENT SPECIFIED INTO SBOOK_WA

WHERE CARRID = 'LH'

AND CONNID = '0400'.

ENDSELECT.

The above mentioned code can be more optimized by using the following code

SELECT * FROM SBOOK CLIENT SPECIFIED INTO SBOOK_WA

WHERE MANDT IN ( SELECT MANDT FROM T000 )

AND CARRID = 'LH'

AND CONNID = '0400'.

ENDSELECT.

Point # 3

Bypassing the buffer increases the network considerably

SELECT SINGLE * FROM T100 INTO T100_WA

BYPASSING BUFFER

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

The above mentioned code can be more optimized by using the following code

SELECT SINGLE * FROM T100 INTO T100_WA

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

<b>Select Statements contd… Aggregate Functions</b>

• If you want to find the maximum, minimum, sum and average value or the count of a database column, use a select list with aggregate functions instead of computing the aggregates yourself.

Some of the Aggregate functions allowed in SAP are MAX, MIN, AVG, SUM, COUNT, COUNT( * )

Consider the following extract.

Maxno = 0.

Select * from zflight where airln = ‘LF’ and cntry = ‘IN’.

Check zflight-fligh > maxno.

Maxno = zflight-fligh.

Endselect.

The above mentioned code can be much more optimized by using the following code.

Select max( fligh ) from zflight into maxno where airln = ‘LF’ and cntry = ‘IN’.

<b>Select Statements contd…For All Entries</b>

• The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

• Large amount of data

• Mixing processing and reading of data

• Fast internal reprocessing of data

• Fast

The Minus

• Difficult to program/understand

• Memory could be critical (use FREE or PACKAGE size)

Points to be must considered FOR ALL ENTRIES

• Check that data is present in the driver table

• Sorting the driver table

• Removing duplicates from the driver table

Consider the following piece of extract

Loop at int_cntry.

Select single * from zfligh into int_fligh

where cntry = int_cntry-cntry.

Append int_fligh.

Endloop.

The above mentioned can be more optimized by using the following code.

Sort int_cntry by cntry.

Delete adjacent duplicates from int_cntry.

If NOT int_cntry[] is INITIAL.

Select * from zfligh appending table int_fligh

For all entries in int_cntry

Where cntry = int_cntry-cntry.

Endif.

Select Statements contd… Select Over more than one Internal table

1. Its better to use a views instead of nested Select statements.

2. To read data from several logically connected tables use a join instead of nested Select statements. Joins are preferred only if all the primary key are available in WHERE clause for the tables that are joined. If the primary keys are not provided in join the Joining of tables itself takes time.

3. Instead of using nested Select loops it is often better to use subqueries.

Point # 1

SELECT * FROM DD01L INTO DD01L_WA

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

SELECT SINGLE * FROM DD01T INTO DD01T_WA

WHERE DOMNAME = DD01L_WA-DOMNAME

AND AS4LOCAL = 'A'

AND AS4VERS = DD01L_WA-AS4VERS

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

The above code can be more optimized by extracting all the data from view DD01V_WA

SELECT * FROM DD01V INTO DD01V_WA

WHERE DOMNAME LIKE 'CHAR%'

AND DDLANGUAGE = SY-LANGU.

ENDSELECT

Point # 2

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

Point # 3

SELECT * FROM SPFLI

INTO TABLE T_SPFLI

WHERE CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK'.

SELECT * FROM SFLIGHT AS F

INTO SFLIGHT_WA

FOR ALL ENTRIES IN T_SPFLI

WHERE SEATSOCC < F~SEATSMAX

AND CARRID = T_SPFLI-CARRID

AND CONNID = T_SPFLI-CONNID

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

The above mentioned code can be even more optimized by using subqueries instead of for all entries.

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.