Skip to Content
author's profile photo Former Member
Former Member

different form of SELECT

hi,

I am doing a documentation on select statement, so please provide me links or the various forms of select query like

SELECT ENDSELECT.

SELECT SINGLE

SELECT DISTINCT

SELECT AVG()

etc...

Points assured

regards,

Prabhu

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 05:42 AM

    Hi,

    1. SELECT * FROM SBOOK

    WHERE CARRID = 'LH' AND

    CONNID = '0400'.

    ENDSELECT.

    2. DATA WA TYPE SPFLI.

    SELECT SINGLE CARRID CONNID CITYFROM CITYTO

    INTO CORRESPONDING FIELDS OF WA

    FROM SPFLI

    WHERE CARRID EQ 'LH' AND CONNID EQ '0400'.

    IF SY-SUBRC EQ 0.

    WRITE: / WA-CARRID, WA-CONNID, WA-CITYFROM, WA-CITYTO.

    ENDIF.

    3. DATA: ITAB TYPE STANDARD TABLE OF SPFLI,

    WA LIKE LINE OF ITAB.

    DATA: LINE(72) TYPE C,

    LIST LIKE TABLE OF LINE(72).

    LINE = ' CITYFROM CITYTO '.

    APPEND LINE TO LIST.

    SELECT DISTINCT (LIST)

    INTO CORRESPONDING FIELDS OF TABLE ITAB

    FROM SPFLI.

    IF SY-SUBRC EQ 0.

    LOOP AT ITAB INTO WA.

    WRITE: / WA-CITYFROM, WA-CITYTO.

    ENDLOOP.

    ENDIF.

    4. DATA: AVERAGE TYPE P DECIMALS 2,

    SUM TYPE P DECIMALS 2.

    SELECT AVG( LUGGWEIGHT ) SUM( LUGGWEIGHT )

    INTO (AVERAGE, SUM)

    FROM SBOOK.

    WRITE: / 'Average:', AVERAGE,

    / 'Sum :', SUM.

    For more information check the following link:

    http://sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

    http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/optimization/optimization.html#Nested%20selects

    Regards,

    Bhaskar

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 05:36 AM

    Hi Prabhu,

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

    Then you will see a lot of Select Querys.

    Thanks.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 05:36 AM

    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 pcarrid pconnid ffldate bbookid

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM ( ( spfli AS p

    INNER JOIN sflight AS f ON pcarrid = fcarrid AND

    pconnid = fconnid )

    INNER JOIN sbook AS b ON bcarrid = fcarrid AND

    bconnid = fconnid AND

    bfldate = ffldate )

    WHERE p~cityfrom = 'FRANKFURT' AND

    p~cityto = 'NEW YORK' AND

    fseatsmax > fseatsocc.

    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 scarrid scarrname p~connid

    INTO CORRESPONDING FIELDS OF TABLE itab

    FROM scarr AS s

    LEFT OUTER JOIN spfli AS p ON scarrid = pcarrid 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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 06:20 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 20, 2007 at 06:33 AM

    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 PF1 PF2 FF3 FF4 INTO TABLE ITAB

    FROM EKKO AS P INNER JOIN EKAN AS F

    ON PEBELN = FEBELN.

    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 PF1 PF2 FF3 FF4 INTO TABLE ITAB

    FROM EKKO AS P INNER JOIN EKAN AS F

    ON PEBELN = FEBELN.

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.