DIFFERENT SELECT STATEMENT

Former Member · ‎07-16-2007

Hi All,

Can anybody provide me different select statement on different scenerios.

Like:

for different fields from data base.

for validation.

select single.

select.......corresponding.

select........with keys.

Please provide me example also.

thanks

rahul.

Former Member · ‎07-16-2007

hi,

There are many different variations of the SELECT statement in ABAP. You have SELECT/ENDSELECT, SELECT Single, SELECT into TAble, inner joins, outter joins, etc.

Here is some help.

http://help.sap.com/saphelp_470/helpdata/en/fc/eb3983358411d1829f0000e829fbfe/frameset.htm

pls refer this thred.

https://forums.sdn.sap.com/click.jspa?searchID=254485&messageID=1260573

Syntax Diagram

SELECT

Basic form

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

In an ABAP Objects context, a more severe syntax check is performed that in other ABAP areas. See Short Forms Not Allowed and * Work Areas Not Allowed.

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.

On some database systems (for example DB2/390)

locking conflicts can be caused even by read access. You can prevent this problem from occurring using regular database commits.

Message was edited by:

Roja Velagapudi

Former Member · ‎07-16-2007

hi,

check this out:

http://www.sts.tuharburg.de/teaching/sap_r3/ABAP4/select_c.htm

Message was edited by:

Sudha Rani Pathuri

Former Member · ‎07-16-2007

For validation u can use..

parametes: p_matnr like mara-matnr.

at selection-screen.

select single matnr from mara into p_matnr where matnr = p_matnr.

if sy-subrc ne 0

message 'Enter correct value' type 'I'.

endif.

Former Member · ‎07-16-2007

Hi,

Check the following link:

http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/optimization/optimization.html

Regards,

Bhaskar

Former Member · ‎07-16-2007

Hi

Basic form

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

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.

Syntax for select single

SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

DATA wa        TYPE sflight. 
DATA seatsfree TYPE I. 

SELECT SINGLE * FROM sflight INTO wa 
       WHERE 
         carrid   = 'LH '      AND 
         connid   = '0400'     AND 
         fldate   = '20010228'. 
         FLDATE   = '19950228'. 
seatsfree = wa-seatsmax - wa-seatsocc. 

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

SELECT STATEMENT USING FOR

DATA wa TYPE spfli. 

SELECT * FROM spfli INTO wa 
         WHERE 
           cityfrom = 'FRANKFURT' AND 
           cityto   = 'NEW YORK'. 
  WRITE: / wa-carrid, wa-connid. 
ENDSELECT.

SELECT * FROM EXAMPLE

Example to display the free seats on Lufthansa flight 0400 on 02/28/2001:



DATA wa        TYPE sflight. 
DATA seatsfree TYPE I. 

SELECT SINGLE * FROM sflight INTO wa 
       WHERE 
         carrid   = 'LH '      AND 
         connid   = '0400'     AND 
         fldate   = '20010228'. 
         FLDATE   = '19950228'. 
seatsfree = wa-seatsmax - wa-seatsocc. 

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

SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn

Example to display all destinations served by Lufthansa from Frankfurt:

DATA: target TYPE spfli-cityto. 

SELECT DISTINCT cityto 
       INTO target FROM spfli 
       WHERE 
         carrid   = 'LH '       AND 
         cityfrom = 'FRANKFURT'. 
  WRITE: / target. 
ENDSELECT.

Example to display the number of airlines that fly to New York:

DATA   count TYPE I. 

SELECT COUNT( DISTINCT carrid ) 
       INTO count 
       FROM spfli 
       WHERE 
         cityto = 'NEW YORK'. 
WRITE: / count.

SELECT STATEMENT USING DISTINCT STATEMENT

DATA: wa   TYPE spfli, 
      ftab TYPE TABLE OF STRING. 

APPEND 'CITYFROM' TO ftab. 
APPEND 'CITYTO'   TO ftab. 

SELECT DISTINCT (ftab) 
       FROM spfli 
       INTO CORRESPONDING FIELDS OF wa 
       WHERE 
         carrid   = 'LH'. 
  WRITE: / wa-cityfrom, wa-cityto. 
ENDSELECT.

SELECT * INTO

DATA: BEGIN OF wa1, 
        client   TYPE scarr-mandt, 
        carrid   TYPE scarr-carrid, 
        carrname TYPE scarr-carrname, 
        url      TYPE scarr-url, 
        rest(100), 
      END OF wa1. 

SELECT * INTO wa1 FROM SCARR. 
  WRITE: / wa1-carrid, wa1-carrname. 
ENDSELECT.

SELECT STATEMENT USING INNER JOIN AND WHERE CONIDITION

 DATA: DATE   LIKE SFLIGHT-FLDATE, 
      CARRID LIKE SFLIGHT-CARRID, 
      CONNID LIKE SFLIGHT-CONNID. 

SELECT F~CARRID F~CONNID F~FLDATE 
    INTO (CARRID, CONNID, DATE) 
    FROM SFLIGHT AS F INNER JOIN SPFLI AS P 
           ON F~CARRID = P~CARRID AND 
              F~CONNID = P~CONNID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '20010910' AND '20010920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / DATE, CARRID, CONNID. 
ENDSELECT.

SELECT STATEMENT USING WHERE CONDITION

 TYPES: BEGIN OF ftab_type, 
         carrid TYPE sflight-carrid, 
         connid TYPE sflight-connid, 
       END OF ftab_type. 

DATA:  ftab TYPE STANDARD TABLE OF ftab_type WITH 
                 NON-UNIQUE DEFAULT KEY INITIAL SIZE 10, 
       free TYPE I, 
       wa_sflight TYPE sflight. 

* Suppose FTAB is filled as follows: 
* 
* CARRID  CONNID 
* -------------- 
* LH      2415 
* SQ      0026 
* LH      0400 

SELECT * FROM sflight INTO wa_sflight 
    FOR ALL ENTRIES IN ftab 
    WHERE CARRID = ftab-carrid AND 
          CONNID = ftab-connid AND 
          fldate = '20010228'. 
  free = wa_sflight-seatsocc - wa_sflight-seatsmax. 
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free. 
ENDSELECT. 

* The statement has the same effect as: 

SELECT DISTINCT * FROM sflight INTO wa_sflight 
    WHERE ( carrid = 'LH'   AND 
            connid = '2415' AND 
            fldate = '20010228' ) OR 
          ( carrid = 'SQ'   AND 
            connid = '0026' AND 
            fldate = '20010228' ) OR 
          ( carrid = 'LH'   AND 
            connid = '0400' AND 
            fldate = '20010228' ). 
  free = wa_sflight-seatsocc - wa_sflight-seatsmax. 
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free. 
ENDSELECT.

SELECT STATEMENT USING GROUP COMMAND

DATA:  count TYPE I, sum TYPE P DECIMALS 2, avg TYPE F. 
DATA:  connid TYPE sbook-connid. 

SELECT connid COUNT( * ) SUM( luggweight ) AVG( luggweight ) 
       INTO (connid, count, sum, avg) 
       FROM sbook 
       WHERE 
         carrid   = 'LH'       AND 
         fldate   = '20010228' 
       GROUP BY connid. 
  WRITE: / connid, count, sum, avg. 
ENDSELECT.

SELECT STATEMENT WITH GROUP & HAVING

 PARAMETERS: comp(80)    TYPE C, 
            tabname(80) TYPE C. 
DATA:   dref      TYPE REF TO DATA, 
        long_name TYPE STRING, 
        count     TYPE I, 
        fieldlist TYPE STRING, 
        condition TYPE STRING. 
FIELD-SYMBOLS: <fs>. 

CONCATENATE tabname '-' comp INTO long_name. 
CREATE DATA dref TYPE (long_name). 
ASSIGN dref->* TO <fs>. 

CONCATENATE comp ' count(*)' INTO fieldlist. 
condition = 'count(*) > 3'. 

SELECT DISTINCT (fieldlist) 
       INTO (<fs>, count) 
       FROM (tabname) 
       GROUP BY (comp) 
       HAVING (condition). 
  WRITE: / <fs>, count. 
ENDSELECT.

SELECT STATEMENT USING ORDER OR TO SORT


DATA: wa_sbook TYPE sbook. 

SELECT * FROM sbook INTO wa_sbook 
         WHERE 
           carrid   = 'LH '      AND 
           connid   = '0400'     AND 
           fldate   = '20010228' 
         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.

SUBQUREIES


DATA: WA_SFLIGHT TYPE SFLIGHT. 

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT 
    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'. 
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, 
           WA_SFLIGHT-FLDATE. 
ENDSELECT.

Reward all helpfull answers

Regards

Pavan

Former Member · ‎07-16-2007

Example

In this example, four columns of the result set are read into four correspondingly named components of a work area.

DATA wa TYPE spfli.

SELECT carrid connid cityfrom cityto

FROM spfli

INTO CORRESPONDING FIELDS OF wa.

WRITE: / wa-carrid, wa-connid, wa-cityfrom, wa-cityto.

ENDSELECT.

Example

DATA wa TYPE spfli.

SELECT SINGLE carrid connid cityfrom cityto

FROM spfli

INTO CORRESPONDING FIELDS OF itab.

WRITE: / itab-carrid, itab-connid, itab-cityfrom, itab-cityto.

Example

Reading of flights with a primary key that corresponds to the user entries on the selection screen.

DATA spfli_wa TYPE spfli.

SELECT-OPTIONS: s_carrid FOR spfli_wa-carrid NO INTERVALS

NO-EXTENSION,

s_connid FOR spfli_wa-connid NO INTERVALS

NO-EXTENSION.

SELECT SINGLE *

FROM spfli

INTO spfli_wa

WHERE carrid IN s_carrid AND

connid IN s_connid.

Regards,

Pavan