Different Select Statements in SAP ABAP

Author | Last Updated| | 33 Comments Facebook Share Twitter Share Google Plus Share A+ A-


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
29 Mar 2018

Quite informative... Thanks for the tutorial.

29 Mar 2018

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

29 Mar 2018

Cool examples, thanks for your effort.

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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?

29 Mar 2018

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

29 Mar 2018

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)

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

THANK U IT IS VERY USEFULL....

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

Useful queries. Thanks for sharing.

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

simply supurub sir thank u sir

29 Mar 2018

Thank you so much. It is very helpful.

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

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

29 Mar 2018

SELECT SINGLE * FROM BSIS WHERE SGTXT = ITAB-SGTXT+0(10).
how field SGTXT only read 10 digit in WHERE ? I have tired, but can't.
SELECT SINGLE * FROM BSIS WHERE SGTXT+0(10) = ITAB-SGTXT+0(10).
SELECT SINGLE * FROM BSIS WHERE SGTXT(10) = ITAB-SGTXT+0(10).
Thanks for you help.

29 Mar 2018

@Basir Please use LIKE, see below example

 SELECT * FROM MARA INTO TABLE IT_MARA WHERE matnr LIKE '11%'.

20 Nov 2020

Anger as 'scumbags' trash beloved Jarrow family magnet Damage to Jarrow Hall in South Tyneside Sign up to FREE email alerts from ChronicleLive dailySubscribeWhen you subscribe we will use the details you provide to send you these newsletters. Sometimes they'll include strategies for other related newsletters or services we offer. OurPrivacy Noticeexplains more how we use your data, and also your rights. You can remove yourself from list at any time. are grateful for subscribingWe have more newslettersShow meSee ourprivacy noticeYobs have trashed part of a beloved South Tyneside attraction. worker at Jarrow Hall are "Sad and exasperated" After discovering a reassembled Anglo Saxon home had been damaged. Pictures posted on Facebook show debris strewn across the garden soil, With wooden furniture outwardly smashed. A police lookup is under way, While CCTV is being examined. The locale, Which is planning to crowdfund 5,000 to repair destruction, Has warned it can look to punish the culprits. In a youtube post shared almost 500 times, Jarrow Hall reconfirmed trespassers had caused "remarkable damage" In the accident, Which is shown to have happened on Tuesday night. "Our team has been working hard behind the curtain to ensure that Jarrow Hall can once again welcome visitors safely when we reopen our doors, But a few individuals have made this task difficult for us, Added the site. "We would like to appeal to anyone who lives locally to keep an eye on the site for us and report any suspicious behaviour to the police. "We have claimed of the incident to Northumbria Police, and we will be reviewing our CCTV footage. If the culprits are referred to we will press charges,Set to 11 acres of an "Historic and external oasis, Jarrow Hall features a museum focused on the life of Bede, Alongside the Anglo Saxon houses and a farm. And supporters of the best selling attraction were sickened by the damage. Some even agreed to volunteer to help repair it. someone wrote on Facebook: "What on earth do these scumbags even escape doing this, The venue has not yet responded to a request for comment. although, A GoFundMe page has now been set up to help raise the funds needed to repair destruction. A Northumbria Police spokesperson added: "last night We received a report of criminal problems with a building at Jarrow Hall, to the Tyneside. NewsallMost ReadMost RecentCoronavirusCoronavirus LIVE up-dates: types will return on Monday; Groups of six can meet outsideFurther UK hospital deaths have actually been recorded in England, Scotland, Wales and n. Ireland Coronavirus features: UK demise rise by 412; NHS test and trace scheme set to launchCoronavirusBoris East European Women Johnson announced can be of the new system as the political fallout continued over Dominic Cummings' trips to Durham during lockdown Newcastle airport terminal flights return next week with 10 golden rules for passengersNewcastle international airportAll passengers must wear masks, As bosses at Newcastle airport terminal welcome the news northern East news LIVE: people taken to hospital after incident in WalkerTraffic TravelChronicleLive's breaking news service including Thursday's traffic, vacation, season, Sport and more covering Newcastle and north of manchester East leaving it with a 5,000 repair billJarrowA Northumbria Police inspection is under way as bosses say they are 'sad and angry' over the damage CoronavirusSchools in England commence reopening from Monday June 1, Boris Johnson confirmsBoris Johnson said the changes were "Limited and aware" As he made the statement during the daily press conference Four arrested and thousands of pills seized during drug raids at Sunderland homesSunderlandThe police warrants were executed on premises in Pinewood Road, Rockingham Road and Colombo Road as part of an investigation into drugs being imported from IndiaParking spaces at St James' Park multi storey given back to Newcastle NHS staff after U turnNewcastle City CentreAround 100 spaces at the stadium car park have been reserved for staff at the Royal Victoria Infirmary again, After council bosses had previously cut cellular phone dedicated spots.

Leave a Comment

Enter Comment Text