This blog will help folks who just on-boarded for ABAP in understanding the codes in line with it.
Note:
1st block of code indicates incorrect way of coding. Codes that follow the golden rules are written just after that.
1. Having small result sets
1.1 WHERE condition
Requirement: Fetch customers whose customer type is 'B'.
DATA : lv_id TYPE scustom-id,
lv_name TYPE scustom-name,
lv_discount TYPE scustom-discount,
lv_custtype TYPE scustom-custtype.
SELECT id name discount custtype FROM scustom
INTO (lv_id, lv_name, lv_discount, lv_custtype).
IF lv_custtype = 'B'.
WRITE : / lv_id, lv_name, lv_discount, lv_custtype.
ENDIF.
ENDSELECT.
TYPES: BEGIN OF ty_scustom,
id TYPE S_CUSTOMER,
name TYPE S_CUSTNAME,
discount TYPE S_DISCOUNT,
custtype TYPE S_CUSTTYPE,
END OF ty_scustom.
DATA: it_scuctom TYPE TABLE OF ty_scustom,
wa_scustom TYPE ty_scustom.
SELECT id name discount custtype FROM scustom
INTO TABLE it_scustom
WHERE custtype = 'B'.
LOOP AT it_scustom INTO wa_scustom.
WRITE : / wa_scustom-id, wa_scustom-name,
wa_scustom-discount, wa_scustom-custtype.
ENDLOOP.
Reason: Fetching filtered data is better than fetching the entire data set and then filtering it.
1.2 HAVING clause
Requirement: Fetch details of flights having the minimum seat occupancy. The minimum seat occupancy should be greater than zero.
DATA: lv_sflight TYPE sflight,
lv_min TYPE sflight-seatsocc.
SELECT carrid connid MIN( seatsocc ) FROM sflight
INTO (lv_sflight-carrid, lv_sflight-connid, lv_min)
GROUP BY carrid connid.
IF lv_min > 0.
WRITE:/ lv_sflight-carrid, lv_sflight-connid, lv_min.
ENDIF.
ENDSELECT.
TYPES: BEGIN OF ty_sflight,
carrid TYPE S_CARR_ID,
connid TYPE S_CONN_ID,
seatsocc TYPE S_SEATSOCC,
END OF ty_sflight.
DATA: it_sflight TYPE TABLE OF ty_sflight,
wa_sflight TYPE ty_sflight.
SELECT carrid connid MIN( seatsocc ) FROM sflight
INTO TABLE it_sflight
GROUP BY carrid connid
HAVING MIN( seatsocc ) > 0.
LOOP AT it_sflight INTO wa_sflight.
WRITE:/ wa_sflight-carrid, wa_sflight-connid, wa_sflight-min_socc.
END LOOP.
Reason: Do the calculation in the select query itself
1.3 Transferring required rows
Requirement: Fetch details
DATA: lt_scustom TYPE TABLE OF scustom,
lw_scustom TYPE scustom.
SELECT id name discount custtype FROM scustom
INTO CORRESPONDING FIELDS OF TABLE lt_scustom
WHERE country = 'DE'.
DELETE lt_scustom WHERE custtype = 'P'.
LOOP AT lt_scustom INTO lw_scustom.
WRITE: / lw_scustom -id, lw_scustom -name,
lw_scustom-discount, lw_scustom -custtype.
ENDLOOP.
SELECT id name discount custtype FROM scustom
INTO CORRESPONDING FIELDS OF TABLE lt_scustom
WHERE country = 'DE' AND custtype <> 'P'.
LOOP AT lt_scustom INTO lw_scustom.
WRITE: / lw_scustom-id, lw_scustom-name,
lw_scustom-discount, lw_scustom-custtype.
ENDLOOP.
Reason: Fetching required rows is better than fetching the entire data set and then delete the unwanted rows
2. Having small transferred data sets
2.1 DISTINCT clause
Requirement: Fetch distinct details of customers who avail discounts
DATA : lt_scustom TYPE TABLE OF scustom,
lw_scustom TYPE scustom.
SELECT custtype discount FROM scustom
INTO CORRESPONDING FIELDS OF TABLE lt_scustom
WHERE discount > 0
ORDER BY custtype discount DESCENDING.
DELETE ADJACENT DUPLICATES FROM lt_scustom.
LOOP AT lt_scustom INTO lw_scustom.
WRITE : / lw_scustom-id, lw_scustom-discount.
ENDLOOP.
SELECT DISTINCT custtype discount FROM scustom
INTO CORRESPONDING FIELDS OF TABLE lt_scustom
WHERE discount > 0
ORDER BY custtype discount DESCENDING.
LOOP AT lt_scustom INTO lw_scustom.
WRITE : / lw_scustom-id, lw_scustom-discount.
ENDLOOP.
2.2 Using aggregate functions
Requirement: Display the total number of seats reserved in an airline in any given year
DATA : lv_sum TYPE sflight-seatsocc,
lv_seatsocc TYPE sflight-seatsocc.
SELECT seatsocc FROM sflight
INTO lv_seatsocc
WHERE carrid = 'LH'AND fldate LIKE '2013 %'.
lv_sum = lv_sum + lv_seatsocc.
ENDSELECT.
WRITE : / lv_sum.
SELECT SUM( seatsocc )FROM sflight
INTO lv_sum
WHERE carrid = 'LH'AND fldate LIKE '2013 %'.
WRITE : / lv_sum.
2.3 Changing only required columns
Requirement: Change the connection number of a specific flight
DATA : lw_book TYPE sbook.
SELECT * FROM sbook
INTO lw_book
WHERE carrid = 'LH'AND connid = '0400'AND fldate >= '20160101'.
lw_book-connid = '0500'.
UPDATE sbook FROM lw_book.ENDSELECT.
UPDATE sbookSET connid ='0500'
WHERE carrid = 'LH' AND connid = '0400' AND fldate >= '20160101'.
3. Reducing number of queries
3.1 Using set operations instead of individual operations
Requirement: Insert a record into the table SBOOK
LOOP AT it_sbook INTO lw_sbook.
INSERT INTO sbook VALUES lw_sbook.
ENDLOOP.
INSERT sbook FROM TABLE lt_sbook.
3.2 Avoiding multiple accesses
Requirement: Delete details of flights with carrier ID AA and connection ID 17
SELECT SINGLE * FROM sflight
INTO lv_sflight WHERE carrid = 'AA' AND connid = '0017'.
IF sy-subrc EQ 0.
DELETE FROM sflight WHERE carrid = 'AA' AND connid = '0017'.
IF sy-subrc EQ 0.
COMMIT WORK.
ENDIF.
ENDIF.
DELETE FROM sflight
WHERE carrid = 'AA' AND connid = '0017'.
IF sy-subrc EQ 0.
COMMIT WORK.
ENDIF.
Reason: Delete data in the repository directly instead of fetching the dataset and then deleting it
3.3 Avoiding SELECT inside LOOPs
Requirement: Fetch details
LOOP AT lt_sflight INTO lw_sflight.
SELECT SINGLE bookid customid FROM sbook
INTO lw_sbook
WHERE carrid = lv_sflight-carrid AND connid = lv_sflight-connid
AND fldate = lv_sflight-fldate.
WRITE : / lv_sflight-carrid, lv_sflight-connid, lv_sflight-fldate,
lv_sbook-bookid, lv_sbook-customid.
ENDLOOP.
IF lt_sflight IS NOT INITIAL.
SELECT SINGLE bookid customid FROM sbook
INTO CORRESPONDING FIELDS OF TABLE lt_sbook
FOR ALL ENTRIES IN lt_sflight
WHERE carrid = lv_sflight-carrid AND connid = lv_sflight-connid
AND fldate = lv_sflight-fldate.
ENDIF.
Bluesky
