[SAP ABAP] Databases Part 1

  • INTO: When we choose this, we can select fields to structure. An example can be found in the explanation of SELECT SINGLE.
  • INTO CORRESPONDING FIELDS OF: We can also use this addition, but, during SELECT, the system tries to select and match fields corresponding to data elements in a result. The differences in the results will be shown in the example.
  • INTO [ obj1, obj2 ... ]: We can select fields in relation to a given variable. For example, if we want to select the carrid and connid fields, we can select these fields directly in relation to a variable instead of a structure or table.
  • INTO TABLE: This is the same as INTO, but we can use a table rather than structure and we can also select more than one row.
  • INTO CORRESPONDING FIELDS OF TABLE: This is the same as INTO CORRESPONDING FIELDS OF, but we can select more than one row here, and we need to use the table as an internal variable where we can store the result.
  • APPENDING: Appending can be used when we want to append new rows instead of re

  • How to see data selected from a database

    In order to exercise SELECT, we need to know which values were selected from the database. How to show data is covered in Chapter 7, Building User Interfaces. For now, I will get you a fragment of code to display data, which is selected from your SELECT statement.
     The following is an example of code to show data from an internal table:
    DATA: gr_alv     TYPE REF TO cl_salv_table,
      gr_columns TYPE REF TO cl_salv_columns_table.

CALL METHOD cl_salv_table=>factory
IMPORTING
     r_salv_table = gr_alv
CHANGING
     t_table      = YOUR TABLE.

 gr_columns = gr_alv->get_columns( ).
 gr_columns->set_optimize( value = 'X' ).
 gr_alv->display( ).
    Do you see YOUR TABLE in this piece of code? This is where you can input the name of your table.
    For instance, if you want to see data stored in gt_sflight, you use the following code:
    DATA: gr_alv     TYPE REF TO cl_salv_table,
      gr_columns TYPE REF TO cl_salv_columns_table.

CALL METHOD cl_salv_table=>factory
IMPORTING
     r_salv_table = gr_alv
CHANGING
     t_table      = gt_sflight.

 gr_columns = gr_alv->get_columns( ).
 gr_columns->set_optimize( value = 'X' ).
 gr_alv->display( ).
    It is important to note that your SELECT statement must be executed first, otherwise you will not be able to see any data.
  • In OpenSQL in ABAP, we have more advanced possibilities. In this section, we will cover the following topics:
    • The WHERE conditions
    • The logical operators in WHERE
    • Casting in WHERE
    • The IN operator
    • Range table
    • The dynamic WHERE condition
    • SELECT SINGLE FOR UPDATE
    • GROUP BY
    • ORDER BY
    • UP TO N ROWS
    • SELECT and ENDSELECT
    • FOR ALL ENTRIES
    • Subqueries
    There are many possibilities here. When we are familiar with them, we can write selects in effective ways while retaining the speed of the executing statement.

    WHERE conditions

    In the WHERE condition, we can make it a requirement that fields need to have some values. The important thing here is that when we have STRING or RAWSTRING plus LCHR and LRAW, we cannot use this field in WHERE. Creating WHERE with the mandt fields is not possible, as the client automatically handles this, but this automatization can be deactivated using CLIENT SPECIFIED after the FROM clause. However, this is only the most basic possibility of the WHERE condition.
    In SELECT, we can use dynamic parameters. When creating the code with the exact values of the WHERE condition, we rarely know exactly what values we need. In such a case, dynamic values would be helpful. They might be found, for example, on some screens:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid EQ gv_carrid.
    In this example, values in carrid are the same as values in the gv_carrid variable.
    We can also use other operands as follows:
    EQ or =: Select all data where the field is equal to the second operand:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid EQ 'AA'.
    This code snippet provides an example of code that shows data from the internal table.
    The result is the SELECT statement's carrid and connid parameters, where carrid is equal to AA.
    NE or <>: This operand selects Get all data when the field is not equal to the second operand:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid NE 'AA'.
    As a result, all carrid and connid fields are selected, except those in which carrid is equal to AA.
    LT or <: This operand selects Get all data when the value of the field is less than the second operand:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid LT 'AA'.
    As a result, the carrid and connid fields are selected only if carrid is smaller than AA.
    GT or >: This operand selects Get all data when the field is greater than the second operand:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid GT 'AA'.
    As a result, the carrid and connid fields are selected only if carrid is greater than AA.
    LE or <=: SELECT gets all of the data when the field is less than, or equal to, the second operand:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid LE 'AA'.
    As a result, the carrid and connid fields are selected only if carrid is less than, or equal to, AA.
    GE or >=: SELECT gets all of the data when the field is greater than, or equal to, the second operand:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid GE 'AA'.
    As a result, the carrid and connid fields are selected only if carrid is greater than, or equal to, AA.
    In the WHERE condition, we can also use the BETWEEN condition:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid BETWEEN 'AA' AND 'DL'.
    The result of that SELECT statement is to get all the carrid and connid fields where carrid is inclusive between'AA' and'DL'.
    We can get the same result if we use SELECT like this:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid GE 'AA'
     AND carrid LE 'DL'.
    We can also use the LIKE operator, which may be helpful for getting data from a table, using only parts of searched words. The operator is built as follows: LIKE 'XXX_', where XXX stands for the part of the word we are looking for, and _ stands for any other set of symbols. XXX and (_) can be used in any order. In the given example in the preceding code snippet, SELECT gets all the carrid values when the values start with AND. In this case, the second character may be any letter.
    The following is an example of LIKE in SELECT:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   WHERE carrid LIKE 'A_'.
    In the results, we get two different carrid parameters. The first of these is 'AA' and the second is 'AZ'.
    When using LIKE, we must have characters as a value. However, if we want to use a non-character, we can try to use CAST.
    In our table, we have CONNID, for example, which does not have character-like values:
    SELECT carrid, connid
   FROM sflight
   WHERE CAST( connid AS CHAR ) LIKE '00__'
   INTO CORRESPONDING FIELDS OF TABLE @gt_sflight.
    All of the WHERE conditions can also be usedwith the NOT addition.
    For instance, we can use NOT BETWEEN to select where we get all data from the table, excluding data with selected fields that are not in range.
    The next operator in WHERE is IN. The IN operator is used to create ranges in a SELECT query. In this example, we add three values to a range. We add 'AA', 'DL', and 'LH' as values of carrid:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
     WHERE carrid in ('AA', 'DL', 'LH' ).
    In the result, we get values where carrid is 'AA', 'DL', or 'LH'.
    In the IN operator, we can also use a special internal table to describe the conditions. One of the most common uses of these possibilities is when we want to dynamically establish conditions in WHERE based on one value, or by using ranges from the selection screen (we will go into more detail about the selection screen in Chapter 7, Building User Interfaces).
    The range table has four fields, and three of them need to be filled. These fields are as follows:
    - SIGN
- OPTION
- LOW
- HIGH
    In the SIGN field we can input I or E. If we input I, values of this row will be included in the selection. If we input E, the value will not be included.
    In the OPTION field, we have the same option as in WHERE (which is EQ or GE) and we can also put CP and BT here. CP is similar to the LIKE command, and BT is similar to BETWEEN.
    When we put an EQ or ELSE option using only one operand, values should be in LOW. HIGH is used only when using BETWEEN in the OPTION field.
    The range table is really useful for maintaining code, which will be easier to maintain as the SELECT statement is shorter, as demonstrated in the following example:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
     WHERE carrid IN gt_carrid.
    In the gt_carrid table, we have the following values:
    SIGN
    OPTION
    LOW
    HIGH
    I
    EQ
    AA
    I
    BT
    DL
    NW
    I
    GE
    SA
    E
    EQ
    NG
    E
    CP
    F_

    If we want to make this directly in SELECT, it should look like this:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
     WHERE carrid EQ 'AA'
       AND carrid BETWEEN 'DL' AND 'NW'
       AND carrid GE 'SA'
       AND carrid NE 'NG'
       AND carrid NOT LIKE 'F_'.
    This WHERE condition in SELECT is more complicated, and even if we have dynamic values, we cannot use more than five values with operands that have been set up in advance in this example.

    Dynamic SELECT in WHERE

    If we do not know what field we need in the WHERE condition, we can use the dynamic WHERE condition. We can decide during runtime which field will be added to the WHERE condition.
    The dynamic WHERE condition is a variable typed as a string, where we have stored text that is similar to code. Let's take a look at the SELECT statement:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
     WHERE carrid EQ 'AA'
       AND connid EQ '0017'
       AND fldate GT '01.01.2015'.
    We can also develop this SELECT statement as the following:
    SELECT carrid connid
 FROM sflight
 INTO CORRESPONDING FIELDS OF TABLE gt_sflight
 WHERE (lv_dyn_where).
    lv_dyn_where has the following values: carrid EQ 'AA', connid EQ '0017', and fldate GT '01.01.2015'. The result of both of these SELECT statements is identical.

    SINGLE FOR UPDATE

    The FOR UPDATE statement can be used to set an exclusive lock for a selected row. However, if we have more than one entry with the same primary key, the result set will be empty. Consequently, it is really important to specify a full key. Furthermore, when our FOR UPDATE statement causes deadlock, an exception will be raised. When we use the FOR UPDATE statement, it is also important for the SELECT statement to bypass SAP buffering.
    An example of SELECT SINGLE FOR UPDATE is as follows:
    SELECT SINGLE FOR UPDATE carrid connid
FROM sflight
INTO CORRESPONDING FIELDS OF TABLE gs_sflight
WHERE carrid EQ 'AA'
AND connid EQ '0017'
AND fldate GT '01.01.2015'.

    GROUP BY

    The GROUP BY clause combines identical content in columns specified by a GROUP BY clause, or content that has the same result in a SQL expression for a single row.
    The GROUP BY clause combines identical content in columns specified by a GROUP BY clause, or content that has the same result in a SQL expression for a single row:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
    GROUP BY carrid connid.
    The result of that query is as follows:
    As you can see, the result set is pretty much the same in that example as when we use SELECT DISTINCT. However, in GROUP BY, we can also specify a field with an IS NOT key, and SELECT DISTINCT only selects unique values in a selected key.

    ORDER BY

    ORDER BY clauses are used to sort data directly through the SELECT statement. Sorting can be carried out using a primary key:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
    ORDER BY PRIMARY KEY.
    It can also sort other columns in ascending or descending order, or even in a dynamic order, which is created on the same principle as dynamic, with different values compared with a dynamic variable:
    SELECT carrid connid planetype
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight
   ORDER BY planetype ASCENDING.

    UP TO and ROWS

    UP TO (natural number) ROWS is used for defining a limit in terms of the number of rows that can be selected in SELECT:
    SELECT carrid connid planetype
   UP TO 10 ROWS
   FROM sflight
   INTO CORRESPONDING FIELDS OF TABLE gt_sflight.
    In this example, we will get the first 10 rows:

    SELECT and ENDSELECT

    SELECT and ENDSELECT are used when we want to create a loop on a database:
    SELECT carrid connid planetype
   FROM sflight
   INTO CORRESPONDING FIELDS OF gs_sflight.
ENDSELECT.
    This means that this SELECT statement gets one row following a single execution and the loop ends when  SELECT cannot get the next rows with the defined key.
    This is helpful when we want to make changes directly to data after SELECT. In this example, we delete leading zeros from connid before appending them to the main gt_sflight table:
    SELECT carrid connid planetype
   FROM sflight
   INTO CORRESPONDING FIELDS OF gs_sflight.
   SHIFT gs_sflight-connid LEFT DELETING LEADING '0'.
   APPEND gs_sflight TO gt_sflight.
 ENDSELECT.
    We can also get data in a loop on a database, sent directly to a table with a PACKAGE SIZE addition. PACKAGE SIZE is responsible for establishing how many entries need to be selected in one execution of a loop:
    SELECT carrid connid planetype PACKAGE SIZE 100
   FROM sflight
   INTO CORRESPONDING FIELDS OF gt_sflight.
ENDSELECT.
    In this case, we get 100 rows in 1 loop.

    FOR ALL ENTRIES

    FOR ALL ENTRIES can be used when we have two tables and we want get data from the second table based on a field in the first table:
    SELECT carrid connid
   FROM sflight
   INTO CORRESPONDING FIELDS OF gt_sflight
   WHERE planetype = '747-400'.

   IF gt_sflight IS NOT INITIAL.
     SELECT carrid connid counryfr cityfr
            airpfrom countryto
       FROM spfli
       INTO CORRESPONDING FIELDS OF TABLE gt_spfli
       FOR ALL ENTRIES IN gt_sflight
       WHERE carrid = gt_sflight-carrid
         AND carrid = gt_sflight-connid.
   ENDIF.
    In the first SELECT statement, we get data about carrid and connid, but only if planetype is '747-400'. In the second table, as we do not have planetype, we can only getrows where we want them.
    One thing that we need to remember when using FOR ALL ENTRIES is that we need to check before executing SELECT with FOR ALL ENTRIES, if the table using this is not empty. If the table is empty, all records will be obtained from the spfli table in this example.

    Subqueries

    Subqueries can be used in the WHERE condition to get maximum values directly from another table. For example, SUBQUERY can be used with the following additions:
    - ALL|ANY|SOME
    If using ALL, the expression is true if the comparison is true for all rows in the results set of the scalar subquery. Consequently, if the ANY or SOME addition is used, the expression is true if it is true for at least one of the rows in the results set of the subquery:
    - EXIST
    If using the EXIST subquery, the expression is true if the table with a subquery contains at least one row:
    - IN
    The IN operator is working in the same way as in the WHERE condition, but the result will be taken dynamically from the database.
    For instance, if we want to book a flight with the highest price, we can make the following subquery:
    SELECT *
FROM sflight
INTO CORRESPONDING FIELDS OF gt_sflight
WHERE price = ( SELECT MAX( price )
FROM sflight ).

Comments

Post a Comment

Popular posts from this blog

[SAP ABAP] Data declaration

After learning about the objects in the Data Dictionary, we can  start  talking about the syntax declaration in ABAP. The easiest way to declare variables is as follows: Copy data: lv_first_variable TYPE c. Variables are named data objects that are used to store values within the allotted memory area of a program. It is good practice to properly name local variables. It is assumed that the first two characters in the variable name determine its use. The first letter indicates whether the variable is global or local. If the local variable starts with  l , then in the case of global variables,  g  is the first letter. The second character determines what type the object is: Variable :  v Line :  s Internal table :  t Each variable must have a type that describes what is stored inside. A variable may have the following types: Elementary types References User-defined types Generic ABAP types In the ABAP language, you can use predefined types to define local data
Read more

[SAP ABAP] BAPI

Image
BAPI BAPI is a standard  interface  that is available in every SAP system. This interface has been designed to access data from the third system (SAP ERP, SAP CRM, other systems). BAPI is a remotely activated function module and can be called by applications that are implemented, for example, in Java. Additionally, the BAPI architecture allows the use of business logic, validation, and checking permissions, which are  available  in the business object level. BAPI is registered in the  Business Object Repository  ( BOR ). Implementing BAPI BAPI is relatively easy to implement. However, the  programmer  must remember some important rules when creating the interface. We will describe these rules next. The first step of implementing BAPI is to create a function module in  SE37  Tcode (transaction code ). To do this, navigate to  Function module   | Attributes , and select the options that are displayed in the following screenshot: If a user requires input data for
Read more

[SAP HR] Payroll Schema

Schemas and Functions Schemas ( Use tcode PE01 ) is a collection of functions executed in a specific order Schemas are stored in T52C0 (SAP standard) / T52C1 ( Customer created) Example is X000 ( X stands for International ) Another Example is L000 ( L stands for Malaysia ) Functions ( Use tcode PE04 ) WPBP a common function used to process IT0, IT1, IT7, IT8 Wagetypes   Wagetypes are used to store a rate/number/amount. It ends up as an object in the payroll result database. It can be earnings, deductions, taxes. Wagetype attributes are stored mainly in T512W. 3 types of Wagetypes:- Model Wagetypes = Start with a letter. Wagetype M320 is a sample wagetype. To implement for customer, it is copied to 0001 for example Technical wagetypes = Start with /. Used for very specific processing. Not advisable to edit/delete this type of wagetype. These wagetypes are used during calculations and cannot be seen in Infotypes User wagetypes = Starts with a number. Will not be
Read more