Show TOC

Procedure documentationCreating SQL Check Steps Locate this document in the navigation structure

 

You want to check whether specific database tables, table fields or table entries exist. You can create identifying subtypes of SQL check steps, to check the following cases:

  • Whether a certain database table exists

  • Whether a certain field of a database table exists

  • Whether certain values occur in fields of a database table

  • How often certain values occur in fields of a database table

You can also create descriptive SQL check steps that do not return a value (table/field/field content occurs or does not occur), they display content from selected tables, in the form of results tables.

Procedure

  • Follow the instructions for creating a check step. For more information, see Creating or Editing a Check Step.

  • Note the special features when creating an SQL check step.

Special Features

Define the following attributes of the check step:

  • Check step type. Choose SQL.

Check the Existence of a Table
  1. To check whether a specific database table is used, select the subtype Existence of Table.

  2. Enter the name of the database table and press Enter.

    If the table exists and the system is able to find a description of it, it displays it.

Checking the Existence of a Table Field
  1. To check whether a specific field in a database table is used, select the entry Existence of Table Field.

  2. Enter the name of the database table and press Enter.

    If the table exists and the system is able to find a description of it, it displays it.

  3. In the Field Name field, enter the name of the table field or select a table field from the list.

    The list is available if the system was able to find a table description.

Checking the Existence of Values or Counting Values
  1. Specify the subtype of the SQL check step:

    • To check whether specific values occur at least once in a specific field of a database table, choose the entry Existence of Table Entry.

    • To check how often specific values occur in a specific field of a database table, choose the entry Number of Table Entries. This subtype of SQL check step creates a higher workload because the query cannot be canceled as soon as one occurrence of the defined value is discovered.

  2. Under Database Tables, click on Create.

    Note Note

    You can specify up to three tables, which you have to link using join conditions.

    Example: You want to identify the number of specific orders in one month. To do this, you join tables with information about customers and orders.

    End of the note.
  3. Enter the name of the database table and press Enter.

    If the table exists and the system is able to find a description of the table, the description of the table and additional information are displayed.

    • Size category. The system creates an SQL statement from the check step. The table's size category provides information about the possible workload that could be created by the SQL statement.

    • The Data Type column displays the table's data type.

    • The check box in the Client-Specific column shows whether the table is used for differentiation by means of the client field.

    • The Used in Check Steps column shows how many SQL check steps already use the table. You can re-use the check steps in question.

    Note Note

    You can display detailed information in the ABAP Dictionary (transaction SE11) in the system that can be reached using the specified RFC connection.

    End of the note.
  4. Under Join Condition, link tables via identical values for fields (for example, link the Customer Number field in the Customer Master Data table, to the Customer Number field in the Orders table).

    Join Condition is only active if you have created more than one table for the check step.

    • In the Table 1 and Field 1 columns, specify the first of the fields that you want to join.

    • In the Table 2 and Field 2 columns, specify the second of the fields that you want to join.

  5. Under Field Criteria, choose Create.

    • In the Field Name column, enter the name of the table field whose values you want to check or count, or select a table field from the list.

      If the table field exists and the system is able to find a description of the table field, the description and other options are displayed:

    • Second value field. The check box is only active if you selected a date field.

      • To check the table field to establish whether the dates for the periods you specify when you create the analysis occur, select the check box.

      • To check the table field to establish whether specific days outside the analysis period occur, record the date in the Value column or specify multiple dates in the Multiple Values column.

    • OrgUnit. The check box is only active for descriptive SQL check steps.

    • In the Value column, enter one or more values with relational operators to form a logical condition for the table field.

      Caution Caution

      Using relational operators such as “not equal to”, “less than”, and “greater than” can have a significant impact on performance in medium and large tables. Avoid using these operators where possible.

      End of the caution.
Displaying Information Using Descriptive Check Steps
  1. To display information about the content of database tables in a results table, choose subtype Descriptive.

    SQL check steps of the subtype Descriptive return a table, not a value. Descriptive check steps can therefore not be checked against threshold values and can only be assigned to informative check items.

  2. Under Database Tables, choose up to three tables that you would like to join.

  3. Under Join Condition, link tables via identical values for fields (for example, link the Customer Number field in the Customer Master Data table, to the Customer Number field in the Orders table).

    Join Condition is only active if you have created more than one table for the check step.

  4. Under Field Criteria, choose Create.

    • In the Field Name field, enter the name of the table field and press Enter.

      If the table field exists and the system is able to find a description of the table field, the description and other information are displayed:

    • Second value field. The check box is only active if you selected a date field. You would normally use this field to define a second, restricting criterion.

    • Organizational unit and mode. To group the content of a table field, either select the OrgUnit check box, or choose the entry Group by Value in the Mode column.

  5. You have two options for refining the check step by specifying further criteria:

    • You can define further grouping criteria.

    • You can define conditions:

      Note Note

      To change the sequence of grouping criteria, select a criterion and choose Move Up or Move Down.

      End of the note.
      • To restrict the check step to certain periods (which you specify when you create the analysis), choose a date field and select the Time Field Use check box.

      • To restrict the check step to one or more days, choose a date field and enter the days you require.

      • To evaluate table fields only if a specific condition is fulfilled, choose the Restrict by Rating entry in the Mode column for the applicable table field. In the Value column, enter one or more values with relational operators to form a logical condition for the table field.

        Caution Caution

        Using relational operators such as “not equal to”, “less than”, and “greater than” can have a significant impact on performance in medium and large tables. Avoid using these operators where possible.

        End of the caution.
Checking Definitions
  1. To check that the check step is consistent and how high a workload the corresponding SQL statement is expected to create, choose Check.

    If the definition is consistent, a rating and a pseudo-SQL statement are displayed under Check and Test Results.

    If the SQL statement is expected to create a large workload, change the table field selection, for example. The rating depends on the size category and the type of table fields. The workload is smaller if you use key fields or index fields.

    The pseudo-SQL statement shows how the check step is executed, in principle. In this way, you can check whether the sorting and selection criteria are defined according to your objectives.

  2. To execute the check step via the RFC connection, choose Test.

    The duration of execution is displayed.

Note Note

The duration of execution is only an approximate estimation of the expected runtime. It does not state the actual length of time it would take to execute a check step during an analysis.

End of the note.