Using a WHERE Clause #

  1. Open a Table (CDC) extraction.
  2. Navigate to the tab WHERE Clause.
  3. Enter a WHERE clause using the WHERE Clause Editor.
  4. To display the results in the Preview section, click [Load live preview].

Extraction-Settings-01

[Load live preview]
Allows a real-time preview of the extraction data without executing the extraction.
You can also preview the data with aggregation functions.

[Refresh metadata]
A new lookup is performed on the selected table(s). Existing mappings and field selections are retained, which is not the case when the table is added again.
It may be necessary to renew the metadata, for example, if a table has been adjusted on the SAP side, another source system has been connected, or the source system has been updated. In such cases, data inconsistencies can occur that are resolved by this function.

WHERE Clause Editor #

There are 2 options for adding criteria to the WHERE clause:

  • [Add Criteria] adds single criteria.
    • The default structure for a single criteria with static values is [Column][Operator][Value] e.g., MARC~WERKS = 1000.
  • [Add Criteria Group] adds a group of criteria.
    • The default structure for a criteria group is ([Column1][Operator1][Value1][Boolean][Column2][Operator2][Value2]) e.g., (MARC~PSTAT = ‘L’ OR MARC~PSTAT = ‘LB’).

Tip: Combine multiple criteria and criteria groups to create complex filters e.g., MARC~WERKS = 1000 AND (MARC~PSTAT = ‘L’ OR MARC~PSTAT = ‘LB’) extracts only data where the column WERKS equals 1000 and the column PSTAT equals either ‘L’ or ‘LB’.

Components of the WHERE Clause Editor

WHERE-Clause-Builder-Example

The following components are available in the editor:

Icon Component Function
WHERE-Clause-Builder-Example Delete row deletes a criteria.
WHERE-Clause-Builder-Example Move row up changes the sequence of the criteria. The selected criteria moves up.
The sequence of criteria can also be changed with drag&drop.
WHERE-Clause-Builder-Example Move row down changes the sequence of the criteria. The selected criteria moves down.
The sequence of criteria can also be changed with drag&drop.
WHERE-Clause-Builder-Example Column adds a column. Click on the component to select a column from the available tables.
WHERE-Clause-Builder-Example SQL adds an Open SQL statement, see SAP Documentation: Open SQL.
WHERE-Clause-Builder-Example Operator adds an operator e.g., =, <, >, etc.
WHERE-Clause-Builder-Example Value adds a static value of type String, Number, Flag or List. List offers a separate editor to create lists of type String, Number or Select. Select enables usage of SELECT statements. For more information, refer to the knowledge base article Working with Lists in the WHERE-Clause Editor.
WHERE-Clause-Builder-Example Criteria adds a new criteria after the selected criteria.
WHERE-Clause-Builder-Example Group adds a new group of criteria the selected criteria.

Note: When adding or editing a criteria only the relevant components are displayed e.g., Add Operator is only available if there is a column or SQL statement to use an operator on.

Editing and Deleting Components

  • Click on a component to edit it. All areas that are marked green can be edited.
  • To delete a component, click the (x) icon above the component.

Using SAP system fields #

Note: System fields can only be used with SAP NW 7.4 SP5 or higher and with the function modules Z_THEO_READ_TABLE or /THEO/READ_TABLE.

You can use SAP system fields for date and time.

Example:

  1. Navigate to WHERE-Clause-Editor and select a column of the type Date here: BUDAT. img-01
  2. Delete the criterion “Value” and use the criterion “SQL”. img-02
  3. Within the “SQL” criterion, use the supported system fields for date and time with a preceding “@” character, here: @sy-datum. img-03
  4. Click [Load Live Preview] to check the result.
  5. Run an extraction.

WHERE Clause Syntax #

Warning! Extraction fails due to incorrect syntax!
The extractions fail, if incorrect syntax is used in the WHERE clause. Make sure to use correct SAP OpenSQL syntax. Several important syntax rules are listed in this help section.

Tip: To check the syntax of the WHERE clause, click [Load live preview]. This way there is no need to run an extraction to see, if the syntax is correct.

Syntax Rules

  • Values must have the internal SAP representation:
    • The date 01.01.1999 has the internal representation 19990101 (YYYYMMDD)
    • The year period 001.1999 has the internal representation 1999001 (YYYYPPP)
    • Numbers must contain the leading zeros, e.g., customer number 1000 has the internal representation 0000001000.
Operator Meaning
=, EQ True if the content of operand1 is equal to the content of operand2.
<>, NE True if the content of operand1 is not equal to the content of operand2.
<, LT True if the content of operand1 is less than the content of operand2.
>, GT True if the content of operand1 is greater than the content of operand2.
<=, LE True if the content of operand1 is less than or equal to the content of operand2.
>=, GE True if the content of operand1 is greater than or equal to the content of operand2.
(NOT) LIKE True if the value of operand1 matches (does not match) the pattern in operand2.
(NOT) IN True if the content of operand1 is (not) part of the content of operand2. Operand2 must be of type LIST or SQL.

Get more details on the OpenSQL syntax on the SAP help site - Select WHERE

Tip: To increase extracting performance, make sure to place the indexed fields as the first selection filter operation in the WHERE clause.