Different Select
Statements in SAP ABAP
Step1: Using Select * in SAP ABAP Important
Select
* is a statement which is used to read whole data from a database table.The
below is the example code for reading data from MARA table.
**Declare internal table
DATA: IT_MARA TYPE TABLE OF MARA.
*use select * to read data
SELECT * FROM MARA INTO TABLE IT_MARA.
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_MARA TYPE TABLE OF MARA.
* read data
SELECT * FROM MARA INTO TABLE IT_MARA WHERE MTART = ‘FERT’.
Step2: Using Select Single in SAP
ABAP Normal
Select Single is a statement which is used to read
single data from a database table. The below is the example code for reading
single record from MARA table.
Note: Whenever we use select single, we must pass
key field in where condition.
**Declare internal table
DATA: WA_MARA TYPE TABLE OF MARA.
*use select * to read data
SELECT SINGLE * FROM MARA INTO WA_MARA WHERE MATNR =
'000001'. .
Step3: Using Select Max in SAP ABAP Important
By
using this query we can get highest numeric value of a column in SAP Database
tables.
Syntax: SELECT MAX () INTO
() FROM .
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 LIFNR.
SELECT MAX (LIFNR)
INTO (LV_MAX)
FROM LIFNR.
WRITE: /
LV_MAX.
Step4: Using Select Min in SAP ABAP Important
By
using this query we can get minimum value of a column in SAP database table.
Syntax: SELECT MIN ()
INTO
()
FROM .
Get Minimum Value of a column in SAP ABAP
The below query will get the minimum value of LIFNR
(Vendor Account No) from LFA1 table.
DATA LV_MIN
TYPE LIFNR.
SELECT MIN (LIFNR)
INTO (LV_MIN)
FROM LFA1.
WRITE: /
LV_MIN.
Step5: Using Select UP TO in SAP ABAP Normal
BY
using Select up to query we will get the specific no of records from a data
base table, it will get records from starting (beginning).
Syntax: select * FROM INTO TABLE
UP TO rows.
Get specific number of rows (records) from a database
table in SAP ABAP.
Data: it_mara type TABLE OF Mara.
**Get 50 rows form starting
select * FROM mara INTO TABLE it_mara UP TO 50 rows.
**Get 50 rows form starting
select * FROM mara INTO TABLE it_mara UP TO 50 rows.
Step6: Using Select Distinct in SAP
ABAP Normal
Select
Distinct is used to get distinct (unique) values of a particular column in SAP
ABAP.
Syntax: SELECT DISTINCT FROM
INTO TABLE .
The below example is used to get distinct material
type values from MARA table.
REPORT ZSAPN_SELECT_DISTINCT.
TYPES: BEGIN OF TY_MARA,
MTART TYPE MARA-MTART,
END OF TY_MARA.
DATA: IT_MARA TYPE TABLE OF TY_MARA.
DATA: WA_MARA TYPE TY_MARA.
SELECT DISTINCT MTART FROM MARA INTO TABLE IT_MARA.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/ WA_MARA-MTART.
ENDLOOP.
TYPES: BEGIN OF TY_MARA,
MTART TYPE MARA-MTART,
END OF TY_MARA.
DATA: IT_MARA TYPE TABLE OF TY_MARA.
DATA: WA_MARA TYPE TY_MARA.
SELECT DISTINCT MTART FROM MARA INTO TABLE IT_MARA.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/ WA_MARA-MTART.
ENDLOOP.
Step7: Using Select Order by in SAP
ABAP Important
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 INTO TABLE
ORDER BY ASCENDING/DESCENDING.
The below is the example program of using orderby with
select in SAP ABAP.
*example1 Sort in ASCENDING ORDER
REPORT ZSAPN_SORT_ASCENDING.
DATA: IT_MARA TYPE TABLE OF MARA.
DATA: WA_MARA TYPE MARA.
SELECT * FROM MARA INTO TABLE IT_MARA ORDER BY MATNR
ASCENDING.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/ WA_MARA-MATNR.ENDLOOP.
*example2 Sort in DESCENDING ORDER
REPORT ZSAPN_SORT_ASCENDING .
DATA : IT_MARA TYPE TABLE OF MARA.
DATA : WA_MARA TYPE MARA.
SELECT * FROM MARA INTO TABLE IT_MARA ORDER BY MATNR
DESCENDING.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/ WA_MARA-MATNR.
ENDLOOP.
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
ASCENDING/DESCENDING.
Step8: Using Wildcards in Selects Normal
SQL
Wildcards are used to search for data in a database table, below are the
examples of using wildcards in SAP ABAP.
The below example will get all records from MARA where
MATNR contains 11.
REPORT ZSAPN_WILDCARDS.
DATA: IT_MARA TYPE TABLE OF MARA.
DATA: WA_MARA TYPE MARA.
SELECT * FROM MARA INTO TABLE IT_MARA WHERE matnr LIKE
'%11%'.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/
WA_MARA-MATNR.
ENDLOOP.
The below example will get all records from MARA where
MATNR ends with 11.
REPORT ZSAPN_WILDCARDS.
DATA : IT_MARA TYPE TABLE OF MARA.
DATA : WA_MARA TYPE MARA.
SELECT * FROM MARA INTO TABLE IT_MARA WHERE matnr LIKE
'%11'.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/
WA_MARA-MATNR.
ENDLOOP.
The below example will get all records from MARA where
MATNR starts 11.
REPORT ZSAPN_WILDCARDS.
DATA : IT_MARA TYPE TABLE OF MARA.
DATA : WA_MARA TYPE MARA.
SELECT * FROM MARA INTO TABLE IT_MARA WHERE matnr LIKE
'11%'.
LOOP AT IT_MARA INTO WA_MARA.
WRITE:/
WA_MARA-MATNR.
ENDLOOP.
SELECT WITH JOINS statement is used
to read data simultaneously from multiple database tables.
As
per performance standards, SELECT WITH JOINS for more than 3 tables is not
advisable, as it puts heavy load on database
Syntax:
SELECT T1~FIELD1
T1~FIELD2
T2~FIELD1
T2~FIELD2
INTO TABLE
FROM T1 INNER JOIN T2 ON (T1~FIELD1 = T2~FIELD)
WHERE T1~FIELD = .
**
Here T1 and T2 are database tables, FIELD1 and FIELD2 are fields in respective
tables
Select into corresponding fields
Select
into corresponding is used to get data from a data base table into a user
defined internal table or work area without specifying the list of fields.
Syntax: SELECT * FROM
INTO
CORRESPONDING FIELDS OF TABLE .
As per SAP standards select into corresponding
statement is not advisable as it effects the performance of an application
because it has to compare each field with database.
Example using select into corresponding
The below example explains how to use select into
corresponding statement to get data into a database table with out specifying
list of fields in select statement.
REPORT ZSAPN_SELECT_CORRESPONDING.
TYPES : BEGIN OF TY_MARA,
"USER
DEFINED TYPE WITH FOUR FIELDS
MATNR
TYPE MARA-MATNR,
MTART TYPE MARA-MTART,
MBRSH
TYPE MARA-MBRSH,
MEINS
TYPE MARA-MEINS,
END OF
TY_MARA.
DATA : IT_MARA TYPE TABLE OF TY_MARA .
DATA : WA_MARA TYPE TY_MARA .
SELECT * FROM MARA
INTO
CORRESPONDING FIELDS OF TABLE IT_MARA
UP TO 50
ROWS.
LOOP AT IT_MARA INTO WA_MARA.
LOOP AT IT_MARA INTO WA_MARA.
WRITE :/ WA_MARA-MATNR, WA_MARA-MTART, WA_MARA-MBRSH,
WA_MARA-MEINS. "DISPLAY OUT
PUT
ENDLOOP.
ENDLOOP.
No comments:
Post a Comment