✒️ABAP / El lenguaje SQL y la sentencia SELECT Por Jaime Gomez Arango

SQL Language and the SELECT Statement

SQL (Structured Query Language) is a query language used as an interface to communicate with databases, facilitating access and manipulation of stored information.

Statements in SQL fall into three groups:

• Data Definition Language (DDL): Used for defining structures like schema, databases, database tables, constraints, etc.

• CREATE: Creates a database, database tables, views, etc.
• ALTER: Modifies the structure of the database, e.g., adding or deleting columns from a database table.
• DROP: Deletes objects from the structure, e.g., an index or a sequence.

• Data Manipulation Language (DML): Allows users to input data for subsequent querying or modification of database data.

• SELECT: Used for data queries.
• INSERT: Inserts values into a database table.
• UPDATE: Modifies values of one or more records.
• MODIFY: Also used for modifying values of one or more records.
• DELETE: Deletes rows from a database table.

• Data Control Language (DCL): Enables granting or revoking permissions on objects to users connecting to the database.

• GRANT: Grants permissions.
• REVOKE: Removes previously granted permissions.

The SELECT Statement

Used to retrieve information in SQL, allowing the retrieval of records from the database, from one or more combined tables. It also enables information filtering to obtain only records that meet certain conditions.

In ABAP, the SELECT statement has the following syntax:

SELECT result " Fields to be retrieved
FROM table " Database table from which data is fetched
INTO target " Destination where data will be stored
WHERE conditions. " Search conditions

There are different variants of SELECT, here are some of them:

• SELECT * INTO TABLE: With the asterisk * symbol, we indicate that we want all records from the table. INTO TABLE is used when the query result is stored in an internal table. Important: Avoid using * when only a few fields from the source table are needed.

SELECT *
FROM zuser_table
INTO TABLE it_users.

• SELECT * APPENDING TABLE: With APPENDING TABLE instead of INTO TABLE, we add database records to the internal table without deleting existing records. It is advisable to REFRESH the internal table where the data will be stored before selecting them. If a variable or structure is used, then CLEAR should be executed.

SELECT *
FROM zuser_table
APPENDING TABLE it_users.

• SELECT field1: To select only one field from a database, use an auxiliary table with only the desired field, for example, DNI. Always check SY-SUBRC after a SELECT to determine how to proceed. If you want to retrieve two fields from the database table, write the field names after the SELECT statement, in the same order as they appear in the database table. If the format where the data will be stored does not match the retrieved data, an error occurs.

SELECT dni
FROM zuser_table
INTO TABLE it_users_aux.

• SELECT SINGLE: If you want to retrieve the first occurrence of a table, execute SELECT SINGLE *. The result of this query will be stored in the WA_USERS structure, which is of the same type as the ZUSER_TABLE table.

CLEAR wa_users.

SELECT SINGLE *
FROM zuser_table
INTO wa_users
WHERE name_surname NE space.

If you want to retrieve only one field, store the result in a variable.

CLEAR v_dni.

SELECT SINGLE dni
FROM zuser_table
INTO v_dni
WHERE name_surname NE space.

• SELECT INTO CORRESPONDING FIELDS OF TABLE: If you want to retrieve records from a database table that meet a condition but want to store them in an internal table with a different structure, use the INTO CORRESPONDING FIELDS OF TABLE clause.

SELECT name_surname user_status
FROM zuser_table
INTO CORRESPONDING FIELDS OF TABLE it_users2
WHERE name_surname NE space
AND user_status EQ 'A'.

• SELECT ...WHERE IN: If you want to make a selection from a database table based on user input, do the following. The IN clause is used for select-options in the conditions of a select.

SELECT *
FROM zuser_table
INTO TABLE it_users
WHERE name_surname EQ p_name_surname
AND user_status IN s_user_status.

• SELECT MAX: If you want to get the active user with the highest DNI, you have to use the MAX clause as follows.

CLEAR v_dni.

SELECT SINGLE max dni
INTO v_dni
FROM zuser_table
WHERE user_status EQ 'A'.

• SELECT COUNT: If you want to count the number of active users, use the COUNT clause as follows.

CLEAR v_active_users.

SELECT COUNT (*)
INTO v_active_users
FROM zuser_table
WHERE user_status EQ 'A'.

• SELECT FOR ALL ENTRIES IN: If you want to retrieve all records from a table that exist in an internal table loaded with data, use FOR ALL ENTRIES IN, allowing you to specify conditions at runtime.

SELECT *
INTO TABLE it_users
FROM zuser_table
FOR ALL ENTRIES IN it_providers
WHERE dni EQ it_providers-dni.

• SELECT ...LIKE: If you want to retrieve all records that match a text pattern in any of their fields, use the LIKE clause.

SELECT *
FROM zuser_table
INTO TABLE it_users
WHERE name_surname LIKE 'A%'.

• SELECT ...INNER JOIN: It is possible to perform a SELECT on more than one database table, known as JOIN. To analyze this point, we will work with two crucial standard SAP database tables, VBRK for Invoice: Header Data, and VBRP for Invoice: Item Data. Suppose we want to retrieve records from both database tables where the VBELN (Invoice Number) field matches. This will be the join condition between both tables, and for these records, we will select the following fields from the VBRK database table:

TYPES: BEGIN OF ty_invoices,
vbeln LIKE vbrk-vbeln, " vbrk - Invoice (VBELN)
fkart LIKE vbrk-fkart, " vbrk - Invoice class (FKART)
fktyp LIKE vbrk-fktyp, " vbrk - Invoice type (FKTYP)
vbtyp LIKE vbrk-vbtyp, " vbrk - Commercial document type (VBTYP)
waerk LIKE vbrk-waerk, " vbrk - Document currency (WAERK)
vkorg LIKE vbrk-vkorg, " vbrk - Sales organization (VKORG)
vtweg LIKE vbrk-vtweg, " vbrk - Distribution channel (VTWEG)
posnr LIKE vbrp-posnr, " vbrp - Invoice item (POSNR)
netwr LIKE vbrp-netwr, " vbrp - Net value of invoice item (NETWR)
END OF ty_invoices.

DATA: it_invoices TYPE STANDARD TABLE OF ty_invoices,
wa_invoices LIKE LINE OF it_invoices.

SELECT t1~vbeln t1~fkart t1~fktyp t1~vbtyp t1~waerk t1~vkorg t1~vtweg t2~posnr t2~netwr
INTO CORRESPONDING FIELDS OF TABLE it_invoices
FROM vbrk AS t1 INNER JOIN vbrp AS t2
ON t2~vbeln = t2~vbeln.

The INNER JOIN clause between two database tables A and B will yield the result of the intersection of sets A and B. In other words, the inner part, i.e., the records common to both database tables based on the connection condition between them.

In ABAP, the LEFT OUTER JOIN clause between two database tables A and B is also available, where the result will be the union between tables A and B plus the outer information part of database table A, from the left side of the union.

E.g

*&---------------------------------------------------------------------*
*& Report ZTEST_ABAP_JEGA_10
*&---------------------------------------------------------------------*
*& To complete this exercise, the message class Z_TEST must be created,
*& a zuser_table_jega must be created and the text corresponding
*& to the Z_BD parameter on the selection screen must be completed.
*&---------------------------------------------------------------------*
REPORT ztest_abap_jega_10.

* Declaration of Tables
DATA: it_users TYPE STANDARD TABLE OF zuser_table_jega,
wa_users LIKE LINE OF it_users.

* Declaration of Variables
DATA: v_birthdate TYPE z_bd.

*----------------------------------------------------------------------*
* Selection Screen
*----------------------------------------------------------------------*
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS: s_bd FOR v_birthdate. "Birthdate
SELECTION-SCREEN END OF BLOCK b1.
*----------------------------------------------------------------------*

*----------------------------------------------------------------------*
AT SELECTION-SCREEN.
*----------------------------------------------------------------------*
* Validate that data is entered on the selection screen
IF s_bd-low IS INITIAL AND s_bd-high IS INITIAL.
MESSAGE e001(z_test) WITH 'Please complete the Birthdate field'.
ENDIF.

*----------------------------------------------------------------------*
START-OF-SELECTION.
*----------------------------------------------------------------------*
CLEAR it_users.
REFRESH it_users.

SELECT *
FROM zuser_table_jega
INTO TABLE it_users
WHERE birthdate IN s_bd.

IF sy-subrc EQ 0.

* Users Title
WRITE: /1(133) sy-uline.
WRITE:/1(1) sy-vline,
2(86) ' Users ',
133 sy-vline.

* Field Headers
WRITE: /1(133) sy-uline.
WRITE:/1(1) sy-vline,
2(8) 'DNI',
11 sy-vline,
12(30) 'Nombre Completo',
43 sy-vline,
44(20) 'Estado',
65 sy-vline,
66(30) 'Birthdate',
133 sy-vline.
WRITE: /1(133) sy-uline.

LOOP AT it_users INTO wa_users.

* Content of the Users Table
WRITE:/1(1) sy-vline,
2(8) wa_users-dni,
11 sy-vline,
12(30) wa_users-nombre_ape,
43 sy-vline,
44(20) wa_users-estado,
65 sy-vline,
97(20) wa_users-birthdate,
133 sy-vline.

ENDLOOP.

WRITE: /1(133) sy-uline.

ELSE.
MESSAGE s001(z_test) WITH 'No data found for the selected criteria'.
ENDIF.