Ads

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

Popular posts from this blog

BADI Interview Questions in SAP ABAP

Sample SAP ABAP Programming Examples for Practice

Module Pool Programming Interview Questions and Answers in SAP ABAP

Step by Step tutorial on BDC Session Method Program in SAP ABAP

SAP ABAP Interview Questions and Answers for 10 Years Experienced