Summary -

In this topic, we described about the open sql select statement with detailed example.

SELECT statement used to read the data from the database tables. When coming to programming, it is always advisable to retrive only one record for a smoother processing. If the SELECT statement retrieving more than one row, the first row from the result set gets retrieved to the work area. If all the rows retrieved using SELECT statement are required to process in the ABAP program, then it advisable to use CURSOR statement.

Syntax -

SELECT      required-columns 
  INTO      host-variables
  FROM      source-table 
  [WHERE    logical-condition]
  [GROUP BY grouping-columns] 
  [HAVING   having-condtion]
  [ORDER BY ordering-columns].

ClauseDescription
required-columnsSpecifies the table column names from where the data retrieved to host-variables.
host-variablesSpecifies the host variables to where the column data retrieved from required-columns.
source-tableSpecifies the database table or view name.
logical-conditionSpecifies condition for the selection criteria.
grouping-columnsSpecifies the grouping columns to produce a single rows from the multiple rows having the same value.
having-condtionSpecifies a logical condition for GROUP BY clause.
ordering-columnsSpecifies the ordering of resulted rows. The ordering may be Ascending (ASC) or Descending (DESC).

After every SELECT statement execution, the system field sy-subrc updated with value 0 if the operation was successful, other than 0 if not successful.

After an SELECT statement execution, the system field sy-dbcnt contains the number of database lines retrieved.


Example -

The below example to display the LG product information from the table ZTC_TPRODUCT.

ZTC_PRODUCT table data -
ZTC_PRODUCT Table Data

Code - 

*&---------------------------------------------------------------------*
*& Report  Z_OPENSQL
*&---------------------------------------------------------------------*
*& Written by TutorialsCampus
*&---------------------------------------------------------------------*

REPORT  Z_OPENSQL.

* Specifying table name
TABLES ZTC_TPRODUCT.

* Declaring cursor and work area
DATA: Cur TYPE CURSOR, WA LIKE ZTC_TPRODUCT.

* Opening cursor
OPEN CURSOR Cur FOR SELECT * FROM ZTC_TPRODUCT 
					WHERE PRODUCTID LIKE 'LG%'
                    ORDER BY PRODUCTID.

* Displaying header line for output
WRITE: /'PRODUCTID  | PRODUCT                                 '
' |    PRODUCT PRICE         |'.
ULINE.

* DO loop until End of Cursor.
DO.

* Retrieving data from the table record by record
      FETCH NEXT CURSOR Cur INTO WA.
      
* Closing cursor when fetching is not successful      
      IF SY-SUBRC <> 0.
        CLOSE CURSOR Cur.
        EXIT.
      ENDIF.

* Displaying table data for output.
      WRITE: / WA-PRODUCTID, '|', WA-PRODUCT,  '|'
      ,WA-PRODUCT_PRICE,  '|'.

ENDDO.
ULINE.

Output -

SELECT Statement example output