Sunday, 24 October 2021

ABAP SQL: Indicator Structures

From ABAP release 7.55 you can declare indicator structures along with the statement TYPES. The indicator structure is the last component of the structured type and mirrors the preceding components as indicators of type x of length 1. The new addition WITH INDICATORS to the statement TYPES allows to define an indicator structure as a substructure, i.e., structured component of a nested structure, of a given structured type.

ABAP SQL Indicator

Generally speaking, an indicator is a one-byte character field that is either set on (‘1’) or off (‘0’). Indicators are like switches which normally used to indicate the result or to control the processing of an operation. You can use an indicator structure as an ABAP SQL indicator representing a null indicator in ABAP SQL read or as a set indicator in write statements. In read statements, the indicators can store information while in write statements, indicators can be used to mark columns for change. The components of the null indicator indicate which columns of the result set contain the null value to indicate an undefined value or result and which don’t. The indicator structure is created equally for each data type of a first-level component of structure. This means that, for example, each substructure or each reference variable is mirrored in the same way as an indicator component is mirrored as an elementary component of structure.

How to use ABAP SQL indicator structure?

The main purpose of an indicator structure is to serve as an ABAP SQL indicator. As said, the addition WITH INDICATORS facilitates the definition of null indicators or set indicators for ABAP SQL statements. This is especially important for the UPDATE statement with the addition INDICATORS since no inline declarations can be used there. For ABAP SQL indicators, only the types c and x of length 1 are relevant.

Syntax

TYPES dtype TYPE struct WITH INDICATORS ind.

Here we drive a structured data type with an indicator structure called ind. For struct, an existing local or global structured type must be specified. For ind, a name must be specified that follows the naming conventions.

This variant of the statement TYPES defines a structured data type that has the same components as the structured type struct specified behind TYPE, as well as an additional last component named ind as an indicator structure. The last component ind is a substructure that contains the same number of first-level components as struct, in the same order as in struct and with the same names as in struct. The standard data type of each component is x of length 1 and can be defined explicitly with the optional addition TYPE.

Update a database table partially using set indicators

The addition INDICATORS of the UPDATE FROM clause can be used to specify set indicators for a work area or an internal table. The purpose of set indicators is to indicate columns to be changed. The components of a set indicator indicate the columns of a DDIC database table to be changed using the UPDATE statement.

UPDATE FROM without indicators overwrites all fields of a row but when set indicators are used, only the indicated fields are updated.

Syntax

… INDICATORS {[NOT] SET STRUCTURE set_ind}

    | (indicator_syntax) …

This addition can be specified only after UPDATE FROM for structured work areas wa or internal tables itab with a structured row type. The source work area or internal table must have a structure set_ind as last field with the same number of components as the DDIC database table to be updated, and each component serves as set indicator for one row. All individual components of set_ind must have either the data type c and length 1, or the data type x and length 1. The UPDATE FROM clause checks the content of set_ind and updates only fields that are marked with ‘X’ (data type c) or ‘1’ (data type x). Fields that contain any other character or digit are not updated. Key fields must always be included in the indicator structure. However, the set indicators do not have any effect on key fields.

When using the addition INDICATORS NOT SET, the reverse logic is applied: all fields are updated, except the ones marked with ‘X’ (data type c) or ‘1’ (data type x).

Example

An internal table that has a line structure with an indicator structure, is partly filled with the flight data for a given flight connection from the DDIC database table SFLIGHT. In the internal table, the price is reduced by 80 %. The modified table is used to update the respective date in the database table. While the lines that are to be updated are selected by the content of key fields in the internal table, the column to be updated is indicated by marking the column PRICE of the indicator structure. If you don’t use the INDICATORS addition of the UPDATE statement, all other non-key columns of the database table would be initialized since their values are initial in the internal table.

TYPES wa TYPE sflight WITH INDICATORS ind.

DATA itab TYPE TABLE OF wa WITH EMPTY KEY.

SELECT carrid, connid, fldate, price

       FROM sflight

       WHERE carrid = char`LH` AND

             connid = numc`0400` AND

             fldate > '20180601'

       INTO CORRESPONDING FIELDS OF TABLE @itab.

IF sy-subrc  = 0.

  LOOP AT itab ASSIGNING FIELD-SYMBOL(<wa>).

    <wa>-price *= '0.8'.

    <wa>-ind-price = '01'.

  ENDLOOP.

  UPDATE sflight FROM TABLE @itab INDICATORS SET STRUCTURE ind.

ENDIF.

Below, sflight table for Lufthansa flights in 2018 with flight connection number of 0400 is shown:

ABAP SQL: Indicator Structures, ABAP Development, SAP ABAP Exam Prep, SAP ABAP Career, SAP ABAP Guides, SAP ABAP Preparation, SAP ABAP

If you use the UPDATE statement without INDICATORS, all those non selected fields of the database table SFLIGHT will be initialized as well. In other words, the values of those fields which are not selected from the database table SFLIGHT, remains initial in your internal table as it is shown below:

ABAP SQL: Indicator Structures, ABAP Development, SAP ABAP Exam Prep, SAP ABAP Career, SAP ABAP Guides, SAP ABAP Preparation, SAP ABAP

With the addition of “INDICATORS SET STRUCTURE ind” in UPDATE statement as follow:

UPDATE sflight FROM TABLE @itab INDICATORS SET STRUCTURE ind.

only the “Price” field will get updated. Then, all other fields in the database table won’t be affected and remain untouched as it can be seen:

ABAP SQL: Indicator Structures, ABAP Development, SAP ABAP Exam Prep, SAP ABAP Career, SAP ABAP Guides, SAP ABAP Preparation, SAP ABAP

So, one can say that the INDICATORS structure acts as a flag to update the selected fields.

INTO Clause and the Concept of Null Indicator


The addition INDICATORS of the INTO clause can also be used to specify indicators. It is currently possible to specify a null indicator that stores information about which columns of the result set contain the null value and which do not. A null value is a special value returned by a database to indicate an undefined value or result. Null values do not correspond to any content of data objects in ABAP and has a better performance than COALESCE. Especially, a null value is not the same as a type-dependent initial value.

Syntax

… INDICATORS [NOT] NULL STRUCTURE null_ind

This Specifies the substructure null_ind of a structured target area specified in the INTO clause as a null indicator after a specified existing work area wa or existing internal table itab, this must contain a substructure with the name null_ind as a component. The substructure must contain at least as many components as the number of columns in the result set defined using the SELECT list. These may also be components of other substructures. Each component must be of type x or c with length 1. The position and name of the substructure must be defined in such a way that it is not affected by the assignment of data from the result set for the query.

The addition INDICATORS after an inline declaration with @DATA(wa) or @DATA(itab) adds a substructure with the name null_ind to the end of the structure or row structure declared inline. For each column in the result set, this substructure contains an identically named component of type x and length 1 in the same order. If preceding components of the structure declared inline are substructures, the substructure null_ind is also structured accordingly. The name null_ind must not be used as the name of a column in the result set. It is recommended to give the components of the null indicator the same name as the columns of the result set.

If the optional addition NOT is specified, the component value hexadecimal 1 for type x and “X” for type c mean that the corresponding column does not contain the null value. For columns that contain the null value, the associated components are initialized.

Example

The third column of the results set of the SELECT statement contains the null value because the WHEN condition of the CASE expression is false and no ELSE is specified. Accordingly, component z of substructure wa-null_ind contains the value hexadecimal 1 as expected.

DELETE FROM demo_expressions.
INSERT demo_expressions FROM @( VALUE #( id = 'X' num1 = 1 ) ).

SELECT SINGLE
       FROM demo_expressions
       FIELDS
         num1 AS x,
         CASE WHEN num1 = 0 THEN 0 ELSE 1 END AS y,
         CASE WHEN num1 = 0 THEN 0 END AS z
       INTO @DATA(wa) INDICATORS NULL STRUCTURE null_ind.

cl_demo_output=>new(
  )->write( |{ wa-x } { wa-null_ind-x }|
  )->write( |{ wa-y } { wa-null_ind-y }|
  )->write( |{ wa-z } { wa-null_ind-z }|
  )->display( ).

So, the output would be:

1 00
1 00
0 01

Source: sap.com

No comments:

Post a Comment