Different Select Statements in SAP ABAP

Author | Last Updated| | 30 Comments | F Share

Different types of select statements used in SAP ABAP programming to read data from database table.

+ -

Step1:Using Select * in SAP ABAP Important Top^

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 Top^

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: When ever 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 Top^

By using this query we can get highest numeric value of a column in SAP Database tables.

 Syntax:  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 LIFNR.
  SELECT MAX( LIFNR )
  INTO (LV_MAX)
  FROM LIFNR .
  WRITE:/ LV_MAX.

Step4:Using Select Min in SAP ABAP Important Top^

By using this query we can 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 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 Top^

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(begining).

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_mara type TABLE OF mara.
**Get 50 rows form starting
select * FROM mara INTO TABLE it_mara UP TO 50 rows.

Step6:Using Select Distinct in SAP ABAP Normal Top^

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>.
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.

Step7:Using Select Order by in SAP ABAP Important Top^

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 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 <ITAB> ASCENDING/DESCENDING.

Step8:Using Wildcards in Selects Normal Top^

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.

Tutorial Comments

Total Comments: Add your Comment
30 Apr 2014

Quite informative... Thanks for the tutorial.

06 May 2014

Hi , Can I get some full ALV Grid reports in SD , MM

15 May 2014

Cool examples, thanks for your effort.

30 Jun 2014

Very useful to learn...thank you....:)

14 Jul 2014

Thank you for providing the details in a spoon feed method.

29 Aug 2014

consider statement below: SELECT SUM( TSL01 ) FROM glt0 INTO gv_erlos WHERE rldnr = '00' AND rrcty = '0' AND rvers = '001' AND bukrs = wa_zfeecalc_pay-dbukrs AND ryear = sy-datum 0(4) AND racct = '11001' AND rbusa = wa_zfeecalc_pay-dbukrs. here i am summing values in column TSL01 which corresponds to period 1. however i want to dynamically detemine the appropriate column TSL?? depending on the current period, i.e. if we are in period 01 then select TSl01, if we are in period 02, then select TSL02. the current period is input by the user in the selection screen. this is the code: lv_period = p_period. " p_period is a user entered parameter value CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT' " to add leading spaces with '0'. if user entered '2', then result is '02'. number of '0' to add is " determined by the size lv_period EXPORTING input = lv_monat IMPORTING output = lv_monat. CONCATENATE 'TSL' lv_monat INTO lv_select. " if for period 2, then result is TSL02 so, the select statement should be: SELECT SUM( lv_select ) FROM glt0 INTO gv_erlos WHERE rldnr = '00' AND rrcty = '0' AND rvers = '001' AND bukrs = wa_zfeecalc_pay-dbukrs AND ryear = sy-datum 0(4) AND racct = '11001' AND rbusa = wa_zfeecalc_pay-dbukrs. however abap complains! does anyone know how to fix this?

02 Sep 2014

Good ................................................

17 Sep 2014

Hello, I'm new to SAP ABAP, can anyone explain me the following.... Types : begin of st_emp, empna(10) type c, empno(10) type c. end of st_emp. What is this above portion called and whats the need for declaring types and data. Data: IT_emp type standard table of ST_emp. ( Can anyone explain me this part)

23 Sep 2014

thnaku for giving this information... its helpful a lot...

23 Sep 2014

thnaku for giving this information... its helpful a lot...

15 Oct 2014

Hi Good examples. select statements should very carefully also. through secondary index fetching should be also added here.

28 Nov 2014

Good work..! Keep it up .......!

20 May 2015

it was nice expericance to know about select statemnts in abap reporting .

09 Jun 2015

Nice and very helpful .....thanks for sharing.

14 Jul 2015

thank you very useful.........

14 Jul 2015

thank you very useful.........

10 Sep 2015

very very good stuf regarding to SELECT QUARY . please also provide the ALE-IDOC related SAP to NON-SAP

05 Oct 2015

THANK U IT IS VERY USEFULL....

05 Jan 2016

can we use select statement to select some fields from internal table.

11 Mar 2016

Excellent Work. Sharing Knowledge is the best thing in the World. GOD bless

12 Mar 2016

sir i want take data from multiple tables using select query pls send me syntax and one simple example

15 Apr 2016

Very useful to learn......thank you

21 Jul 2016

Useful queries. Thanks for sharing.

03 Dec 2016

Hello there, I would like to give some of the doubts/suggestions, to improve this section of the tutorial. 1. Please provide the difference between order by and sort keywords. Because you have mentioned, order by decreases performance and I would like to know why. 2. Select FOR ALL ENTRIES is not dealt in this section. Regards, Chitra.S

17 Jan 2017

Thank you so much for your kind information sharing with us its very useful stuff..

10 May 2017

simply supurub sir thank u sir

25 May 2017

Thank you so much. It is very helpful.

20 Jun 2017

select kunnr name1 land1 as sortl from kna1 This query how works please suggest me

05 Jul 2017

Nice and very helpful .....thanks for sharing.

04 Sep 2017

yeah its very helpful can you check this post also http://sap-oo-abap.blogspot.ae/2016/02/sap-abap-select-statement.html

Leave a Comment

Enter Comment Text