Category: ABAP

How to update Data records in a Database table in SAP ABAP

Using the ‘UPDATE’ keyword, we can modify one or more Data records in an internal table.

The data records can be modified in the following different ways.

  1. Through Structure.
  2. Through Internal table.
  3. Through ‚WHERE‘ Clause.
  • Through Structure:

Single line of record can be modified using structure. Therefore define a structure with reference to the Database table name. The line of record must correspond to the primary key of the structure. Structure name is given after the ‘FROM’ clause. If the data records are updated successfully, then the system field sy-subrc is set to 0. If the data records are not updated successfully, then the system field sy-subrc is set to 4.

Now let’s look at the syntax.

DATA ls_contact TYPE ztt_db_table2.

ls_contact-contact_id = 102.

ls_contact-contact_name = ‘Sai’.

UPDATE ztt_db_table2 FROM ls_contact.

If all the components of the structure are not assigned with values, the initial values will be set in Database table for those components.

  • Through Internal table.

If more than one line of record must be updated, then we can use an Internal table to achieve that. The structure of the Internal table must be similar to the Database table. All lines of records will be updated which have the same primary key values. The lines of record which are not found will not be updated. At the same time, it will not influence the rest of the data records. If all the lines of record are updated successfully, then the system field sy-subrc is set to 0. If atleast one line of record is not updated successfully, then the system field sy-subrc is set to 4. The system field sy-dbcnt will print out the number of records updated successfully.

UPDATE ztt_db_table2 FROM TABLE lt_int_table.

  • Through ‘WHERE’ clause

Through this option we can change more number of records targeted at a particular column. The ‘WHERE’ clause can further be added, to constrain the updated records. Through the addition of the keyword ‘SET’, we can specify which columns of the Database table should be changed.

If atleast one of the data record is updated successfully, then the system field sy-subrc is set to 0. If none of the data records is updated successfully, then the system field is set to sy-subrc = 4. The system field sy-dbcnt prints out the number of Data records which are updated successfully.

The following statement is the syntax for ‘WHERE’ clause.

UPDATE ztt_db_table2 SET contact_address = ‘Street 10’ WHERE contact_name = ‘Mike’.

The execution of above query will set the contact_address to ‘Street 10’ for all the contacts with the contact_name = ‘Mike’.

How to insert Data records in to Database table in SAP ABAP

With the help of ‘INSERT’ keyword, we can insert one or more records in to an internal table.

Data records can be inserted in the following two different ways:

  1. Through structure.
  2. Through Internal table.
  • Through structure.

DATA ls_contact TYPE ztt_db_table2.

ls_contact – contact_id = 104.

ls_contact – contact_name = ‘Bala’.

ls_contact – contact_address = ‘Street 55’.

The data records can be inserted in following two different ways from a structure.

  1. INSERT ztt_db_table2 FROM ls_contact.
  2. INSERT INTO ztt_db_table2 VALUES ls_contact.

Both the above INSERT operations are very much similar. If the Data records are inserted successfully, then the system field sy-subrc is set to 0. If it is not inserted successfully, then the system field sy-subrc is set to 4.

  • Through Internal table.

More than one data record can be inserted into Database table through Internal tables. If the line of record is already present in an internal table, then it results in runtime error. But this can be avoided through the keywords ACCEPTING DUPLICATE KEYS. Through the above keyword, already existing lines of record will be ignored.

If the records are inserted successfully, then the system field sy-subrc is set to 0. If the data records are not inserted successfully, then the system field sy-subrc is set 4. The system field sy-dbcnt will print out the number of records inserted in to the Database table.

DATA lt_int_table TYPE TABLE OF ztt_db_table2.

DATA ls_contact TYPE ztt_db_table2.

ls_contact -contact_id = 105.

ls_contact -contact_name = ‘Suresh’.

ls_contact -contact_address = ‘Street 88’.

APPEND ls_contact TO lt_int_table.

CLEAR ls_contact.

ls_contact -contact_id = 106.

ls_contact -contact_name = ‘Sanjay’.

ls_contact -contact_address = ‘Street 98’.

APPEND ls_contact TO lt_int_table.

INSERT INTO ztt_db_table2 FROM TABLE lt_int_table.

The Duplicate records can be ignored by using the following INSERT statement.

INSERT INTO ztt_db_table2 FROM TABLE lt_int_table ACCEPTING DUPLICATE KEYS.

How to delete records from a Database table in SAP ABAP

Using the DELETE statement, you can delete one or more records from a Database table. It can be deleted in the following three different ways.

  • Through ‘WHERE’ clause.
  • Through structure (work area).
  • Through Internal table.

  • Through ‘WHERE’ clause.

The data records from the Database table are deleted based on the condition mentioned in the ‘WHERE’ clause. If at least one record is deleted from the Database table, then the system field is set to sy-subrc = 0. If the deletion is unsuccessful, then system field sy-subrc is set to 4. The system field sy-dbcnt will give out the count of the records deleted.

DELETE FROM ztt_db_table2 WHERE contact_id = 102.

  • Through Structure (Work area).

With the help of the structure, we can delete exactly a single line of record from the Database table. The keyword ‘FROM’ will be used after the table name. If the single line of record is found and deleted, then system field sy-subrc will be set to 0. If the record is not found and therefore not deleted, then the system field sy-subrc will be set to 4.

DATA ls_contact TYPE ztt_db_table2.

ls_contact – contact_id = 101.

ls_contact – contact_name = ‘Thiru’.

ls_contact – contact_name = ‘Street 10’.

DELETE ztt_db_table2 FROM ls_contact.

  • Through Internal table.

The Data records from the Database table can also be deleted through Internal table. Initially, the internal table is loaded with all the data records to be deleted. ‘FROM’ keyword is used after the Database table name and then it is followed by ‘TABLE’ keyword. The internal table name is mentioned after the ‘TABLE’ keyword.

DATA lt_int_table TYPE TABLE OF ztt_db_table2.

DATA ls_data_record TYPE ztt_db_table2.

ls_data_record – contact_id = 103.

ls_data_record – contact_name = ‘Mahesh’.

ls_data_record – contact_address = ‘Street 10’.

APPEND ls_data_record TO lt_int_table.

ls_data_record – contact_id = 102.

ls_data_record – contact_name = ‘Mani’.

ls_data_record – contact_address = ‘Street 11’.

APPEND ls_data_record TO lt_int_table.

DELETE ztt_db_table2 FROM TABLE lt_int_table.

How to use GROUP-BY clause in SAP ABAP

The values of a column can be grouped by using the GROUP BY statement. The GROUP BY clause must always come at the end of the ‘SELECT’ statement. It should always be after the ‘FROM’ clause and ‘WHERE’ clause.

Let us consider an example, where we group all the contact based on contact address.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

SELECT contact_name contact_address FROM ztt_db_table2 INTO TABLE lt_contact

GROUP BY contact_name contact_address HAVING contact_address = ‘Street 12’.

The columns mentioned after ‘SELECT’ keyword must match the columns mentioned after ‘GROUP BY’ keyword. In the above query, we have used additional constraint using the ‘HAVING’ keyword.

The results are grouped based on the columns contact_name and contact_address. So these column names are mentioned exactly after the ‘SELECT’ and ‘GROUP BY’ keyword. The additional constraint such as contact_address = ‘Street 12’ through HAVING option will help us to further filter the data records with street address equal to ‘Street 12’.

How to use ORDER BY in SELECT statement in SAP ABAP

The selected records can be sorted using the ‘ORDER BY’ clause. The data can be sorted based on the column mentioned after the ORDER BY clause. It can be sorted in ascending / increasing order or descending / decreasing order. It can be sorted based on more than one column. The priority of the sorting sequence is based on the order in which the column names are mentioned. For every column, it can be mentioned specifically if it needs to be sorted in ascending or descending order. By default, it is sorted in ascending order.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact ORDER BY contact_id ASCENDING.

How to use WHERE condition in SELECT statement in SAP ABAP

The ‘WHERE’ clause in SELECT query can be used to constrain the selected records from the Database table.

The following operators can be used in the ‘WHERE’ clause of SELECT query.

OperatorExplanation
EQ  /  =The data of operand1 must be equal to operand2.
NE   /   < >The data of operand1 must not be equal to operand2.  
LT   /   <The data of operand1 must be lesser than operand2.
GT / >The data of operand1 must be greater than operand2.
LE / <=The data of operand1 must be lesser than or equal to operand2.
GE / >=The data of operand1 must be greater than or equal to operand2.

We can also use AND, OR conditions in the ‘WHERE’ clause.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact WHERE contact_id = 102.

‘AND’ condition :

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact                                        WHERE contact_id = 102 AND contact_name = ‘Tom’.

‘LIKE’ condition:

When we are not sure about the exact value to be used in the WHERE clause, then we can use the LIKE option to narrow down the records.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE lt_contact

WHERE contact_name LIKE ‘T_’.

The above ‘SELECT’ statement will select all the records with the contact_name starting with the letter ‘T’.

HOW TO USE OPEN-SQL SELECT STATEMENTS IN SAP ABAP

The data from the Database table can be read using SELECT statements. These data records are stored in Dataobjects such as variables, structure and Internal table.

The SELECT statement can be used in the following ways.

  • Reading a single line of record.
  • Reading more lines of record.
  • Reading more lines of record inside a loop.

Reading a single line of record:

              ‘SINGLE’ keyword is used in the select statement to read a single record from the Database table. The first line of record is selected in the Database table.

DATA ls_contact TYPE ztt_db_table2.

SELECT SINGLE * FROM ztt_db_table2 INTO ls_contact.

WRITE:/ ls_contact-contact_id.

Reading more lines of record:

              More than one lines of record can be read from the Database table using the addition ‘INTO’ or ‘APPENDING’. The Addition ‘INTO’ will overwrite the records in the internal table, if it already has some records. The Addition ‘APPENDING’ will not overwrite the records. It will hold the existing records in the internal table. It appends to these existing records. using the ‘SELCT DISTINCT *’, the Duplicate records can be ignored while writing into the Database table.

Using the Addition ‘UP TO’ … ‘ROWS’, the number of records selected can be constrained.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

DATA ls_contact TYPE ztt_db_table2.

SELECT * FROM ztt_db_table2 INTO TABLE lt_contact.

LOOP AT lt_contact INTO ls_contact.

              WRITE:/ ls_contact-contact_id.

ENDLOOP.

APPENDING:

SELECT * FROM ztt_db_table2 APPENDING TABLE lt_contact.

UPTO … ROWS…:

SELECT *FROM ztt_db_table2 INTO TABLE lt_contact UP TO 5 ROWS.

DISTINCT:

SELECT DISTINCT *FROM ztt_db_table2 INTO TABLE lt_contact.

Reading more lines of record inside a loop:

The lines of record can only be stored in a structure or variable. It cannot be stored inside an internal table. The ‘END SELECT’ keyword comes at the end. The line of record are processed between the ‘SELECT’ and ‘ENDSELECT’.

DATA ls_contact TYPE ztt_db_table2.

SELECT * FROM ztt_db_table2 INTO ls_contact.

              WRITE: / ls_contact-contact_id.

ENDSELECT.

The LOOP AT … ASSIGNING is preferred over the ‘SELECT… ENDSELECT’.

How to specify the column names in query.

DATA lt_contact TYPE TABLE OF ztt_db_table2.

The below query can be used before ABAP version 7.40 SP05.

SELECT contact_id contact_name FROM ztt_db_table2 INTO TABLE ztt_db_table2.

After the release of ABAP version 7.40 SP05, the column names can be separated by comma. But the internal table must be specified with @.

SELECT contact_id, contact_name FROM ztt_db_table2 INTO TABLE @ztt_db_table2.

How to specify the target columns in ‘SELECT’ statement:

TYPE: BEGIN OF st_struct,

 contact_name TYPE ztt_db_table2-contact_name,

               contact_address TYPE ztt_db_table2-contact_address

END OF st_struct.

TYPES: tt_contact TYPE TABLE OF st_struct.

DATA pt_contact TYPE tt_contact.

SELECT * FROM ztt_db_table2 INTO CORRESPONDING FIELDS OF TABLE pt_contact.

When the components of the target internal table is different from the components of the database table, then the keyword ‘INTO CORRESPONDING FIELDS OF TABLE’ can be used. Through the `CORRESPONDING FIELDS OF´ keyword, the data records are written to the target internal table. The components of pt_contact are different from ztt_db_table2.

How to explicitly specify the Target columns in SELECT statement:

              The contents of the Database table can also be written to variables. This can be specifically used when the source and target column are different.

DATA lv_contact_id TYPE ztt_db_table2-contact_id.

DATA lv_contact_name TYPE ztt_db_table2-contact_name.

SELECT SINGLE contact_id contact_name FROM ztt_db_table2 INTO (lv_contact_id, lv_contact_name).

Five Important rules to improve the performance of Database access

  1. Output results must be minimum. Please try to use where, having constraints in the query to reduce the data records read from the Database table.
  2. Use SELECT colum1, column2 instead of SELECT *From. The performance will be better as only the important columns are selected in the query.
  3. Use Joins instead of writing multiple select statements. The performance will be better as each select statement is going to be costly.
  4. Try to use Indexes, primary keys in the SELECT query to read data records from the Database table.
  5. Try to use Buffer concept in Database access. So read the records only once and store it in buffer variable. Try to sort the data in the ABAP program instead of using ORDER BY in the SQL query.

How to find the code location to change the column heading of an ALV List

  1. Determine the structure behind the ALV List by right clicking on the ALV List
  2. Determine the Table type for the structure using the Where used option in SAP ABAP
  3. Determin the corresponding Program for the Table type using the Where used option in SAP ABAP (Ctrl+Shift+F3)
  4. Click a particular program in the results list
  5. Click on the Display Object List Icon (Ctrl+Shift+F5)
  6. Click on the corresponding Includes or Function modules or a class in the Tree which appears on the left side of ABAP Editor
  7. Click on the Search glass on top below the menus.
  8. Enter the structure determined in step 1 and click the green check.
  9. Search the Field Catalog in the results list.
  10. Click on the Field Catalog in the Results List.
  11. Change the Column context of the Field Catalog structure.
  12. Save and activate the object

Modifying the internal table contents using MODIFY statement

Using the MODIFY statement, we can modify more than one data record in an internal table.

It is modified with the contents of the structure along with the additional conditions such as WHERE, INDEX, USING KEY.

Using the ‘TRANSPORTING’ keyword, we can specify which component of the structure in the internal table must be transferred.

WHERE:

DATA lt_internal_table TYPE TABLE OF ztt_db_table2.

DATA ls_record TYPE ztt_db_table2.

SELECT *FROM ztt_db_table2 INTO TABLE lt_internal_table.

ls_record-contact_address = ‘Street 44’.

MODIFY lt_internal_table FROM ls_record TRANSPORTING contact_address WHERE contact_id=102.

INDEX:

MODIFY lt_internal_table FROM ls_contact INDEX 2 TRANSPORTING contact_address.

Using MODIFY inside a loop.

LOOP AT lt_internal_table INTO DATA(ls_record) WHERE contact_id=102.

              ls_record-contact_address = ‘Street 99’.

              MODIFY lt_internal_table FROM ls_record TRANSPORTING contact_address.

ENDLOOP.