Different Types of SAP ABAP Select Statements
Different Types of SAP ABAP Select Statements
As a SAP ABAP Consultant ,Our main role is fetching data using Select Statement and displaying it in output for End user .So Here i shared Different types of Select statements and their definitions and syntaxes and example programs.SELECT is a keyword used in SAP ABAP programming.
SELECT Statement Syntax:
SELECT result [target] FROM source [where] [GROUP BY fields] [ORDER BY order].
Select Retrieves or 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 result table might be a table or a single record,
The INTO target clause defines the target place into which the selected
data is to be retrived. If the target area is an internal table, the INTO
clause specifies
The INTO clause specify the result table that may be work area or internal table in which fetching will be stored.
The FROM source clause the source (database table or view ) from which the data is to be selected. It also determines
The WHERE where clause specifies the conditions which the result of the
selection must satisfy.
The GROUP-BY fields clause combines groups of lines together into single lines.
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 .
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.
Using Select * in SAP ABAP
Select * is a statement which is used to read whole data from a database table.Declare internal table:
DATA : IT_VBAK TYPE TABLE OF VBAK.
Use select to read data:
SELECT * FROM VBAK INTO TABLE IT_VBAK .
The below example code is used to get data based on a condition with all fields(columns) from MARA table.
Declare internal table:
DATA : IT_VBAK TYPE TABLE OF VBAK.
Read data :
SELECT * FROM VBAK INTO TABLE IT_VBAK WHERE VBELN = '4969' .
Select Single in SAP ABAP
Select Single is a statement which is used to read single data from a database table.
When ever we use select single, we must pass key field in where condition.
Declare internal table:
DATA : WA_VBAK TYPE TABLE OF VBAK.
Select * to read data:
SELECT SINGLE * FROM VBAK INTO WA_VBAK WHERE VBELN = '4969'. .
Select Max in SAP ABAP
Using this query we can get highest numeric value of a column in SAP Database tables.
SELECT MAX( <COLUMNNAME> ) INTO (<VARIABLE>) FROM <TABLE> .
Get Maximum value of a column using Select Max
The below statement will get the maximum value of column LIFNR from LFA1 table.
DATA LV_MAX TYPE vbeln.
SELECT MAX( vbeln )
INTO (LV_MAX)
FROM vbeln .
WRITE:/ LV_MAX.
Select Min in SAP ABAP :
This query is used to get minimum value of a column in SAP database table.
Syntax: SELECT MIN( <column> )
INTO (<variable>)
FROM <table>.
Get Minimum Value of a column in SAP ABAP
The below query will get the minimum value of vbeln from VBAK table.
DATA LV_MIN TYPE VBELN.
SELECT MIN( VBELN )
INTO (LV_MIN)
FROM VBAK .
WRITE:/ LV_MIN.
Select UP TO in SAP ABAP
Select Up To query we will get the specific no of records from a data base table, it will get records from starting.
Syntax:select * FROM <TABLE> INTO TABLE <ITAB> UP TO <NUMBEROFROWS> rows.
Get specific number of rows (records) from a database table in SAP ABAP.
data : it_VBAK type TABLE OF VBAK.
Fetch 20 rows form starting
select * FROM VBAK INTO TABLE it_VBAK UP TO 20 rows.
Select Distinct in SAP ABAP
Select Distinct is used to get distinct (unique) values of a particular column in SAP ABAP.
Syntax: SELECT DISTINCT <COULMN> FROM <TABLE> INTO TABLE <ITAB>.
REPORT SAP_ABAP_WEBDYNRPO_TUTORIALS.
TYPES: BEGIN OF TY_VBAK,
VBELN TYPE VBAK-VBELN,
END OF TY_VBAK.
DATA : IT_VBAK TYPE TABLE OF TY_VBAK.
DATA : WA_VBAK TYPE TY_VBAK.
SELECT DISTINCT VBELN FROM VBAK INTO TABLE IT_VBAK.
LOOP AT IT_VBAK INTO WA_VBAK.
WRITE:/ WA_VBAK-VBELN.
ENDLOOP.
Select Order by in SAP ABAP
SELECT ORDERBY is used to fetch data from database table with sorted result set, by default the result will be sorted in ascending order, to sort in descending order you have to specify
Syntax: SELECT * FROM <TABLE> INTO TABLE <ITAB> ORDER BY <COLUMN> ASCENDING/DESCENDING.
The above statement is educational purpose only, in your real-time projects don`t use SELECT ORDERBY, it decreases performance of a program, instead use SORT after fetching data.SORT <ITAB> ASCENDING/DESCENDING.
Wildcards in Selects
SQL Wildcards are used to search for data in a database table, below are the examples of using wildcards in SAP ABAP.
For performance purpose ,we have to use :
SELECT <FI> <F2> <F3> FROM <DATABASE TABLE> INTO Table <ITAB>
WHERE <CONDITION>.
Quick view on Select statements
1. To Read multiple rows from DB table into Work area (Row by row in a loop)
SELECT <FIELDS> FROM <DB TABLE> INTO <WA>.
ENDSELECT.
2. To read a single row from dB table into work area(Based on Primary key)
SELECT SINGLE <FIELDS> FROM <DBTABLE> into <WA> where <cond>.
3. To read a group of rows from DB table into Internal Table (Array fetch)
SELECT <fields> from <DBTABLE> into TABLE <ITAB>.
Both 2 and 3 are efficient performance wise.
SAP's OPEN SQL, syntax of the SELECT statement
Basic form:
SELECT result FROM source [INTO target] [WHERE condition] [GROUP BY fields] [ORDER BY order].
SELECT clause
Variants:
1. SELECT [SINGLE [FOR UPDATE] DISTINCT] *
2. SELECT [SINGLE [FOR UPDATE] DISTINCT] s1 ... sn
3. SELECT [SINGLE [FOR UPDATE] DISTINCT] (itab)
FROM clause
Variants:
1. ... FROM dbtab
Additions:
1. ... CLIENT SPECIFIED
2. ... BYPASSING BUFFER
3. ... UP TO n ROWS
2. ... FROM (dbtabname)
Additions:
1. ... CLIENT SPECIFIED
2. ... BYPASSING BUFFER
3. ... UP TO n ROWS
INTO target
(This form of the FROM clause works only in conjunction with the INTO clause.)
INTO clause
Variants:
1. ... INTO wa
2. ... INTO CORRESPONDING FIELDS OF wa
3. ... INTO (f1, ..., fn)
4. ... INTO TABLE itab
5. ... INTO CORRESPONDING FIELDS OF TABLE itab
6. ... APPENDING TABLE itab
7. ... APPENDING CORRESPONDING FIELDS OF TABLE itab
WHERE clause
Variants:
1. ... WHERE f op g
2. ... WHERE f [NOT] BETWEEN g1 AND g2
3. ... WHERE f [NOT] LIKE g
4. ... WHERE f [NOT] IN (g1, ..., gn)
5. ... WHERE f [NOT] IN itab
6. ... WHERE f IS [NOT] NULL
7. ... WHERE NOT cond
8. ... WHERE cond1 AND cond2
9. ... WHERE cond1 OR cond2
10. ... WHERE (itab)
11. ... WHERE cond AND (itab)
12. ... FOR ALL ENTRIES IN itab WHERE cond
Operator Meaning
EQ or = equal to
NE or < > not equal to
LT or < less than
LE or < = less than or equal to
GT or > greater than
GE or >= greater than or equal to
GROUP-BY clause
Variants:
1. ... GROUP BY f1 ... fn
2. ... GROUP BY (itab)
ORDER-BY clause
Variants:
1. ... ORDER BY PRIMARY KEY
2. ... ORDER BY f1 ... fn
3. ... ORDER BY (itab)
Comments
Post a Comment