A WHERE clause can be used to filter table records, see SAP ABAP Documentation: SELECT-WHERE.

Using a WHERE Clause #

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

Note: When fields with the same name exist in different tables, the field names must be formatted as [table name]~[field name], e.g., MARC~MATNR. This can be the case when extracting multiple tables.

Extraction-Settings-01

[Text mode]
Allows entering a WHERE clause directly into the text field. Text mode is the default method for defining a WHERE clause.

[Editor mode]
Opens the WHERE clause editor. The WHERE clause editor offers a toolkit for those who are not familiar with the syntax of the WHERE clause, see WHERE Clause Editor.

[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.

[Count rows]
Returns the number of rows/data records of an extraction, considering the WHERE and HAVING clauses stored.

[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 Restrictions #

Note: When using table joins, restricting the right table of a LEFT OUTER JOIN is only possible starting from SAP Release 7.40, SP05.

If your SAP System is older than Release 7.40, SP05, the following error appears: *RFC_ERROR_SYSTEM_FAILURE - Illegal access to the right table of a LEFT OUTER JOIN.

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

  • Enter a space before and after the equal sign:
    Correct: YEAR = ‘1999’
    Wrong: YEAR= ‘1999 ‘, YEAR =’1999’ or YEAR=’1999’

  • Set floating point numbers in single quotation mark:
    Correct: KMENG > ‘10.3’
    Wrong: KMENG > 10.3

Note: The WHERE clause doesn’t need any line break (return key).

  • 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.

Script Expressions #

The [Text Mode] of the WHERE clause supports script expressions. They are usually used to determine a dynamic date based on the current date.
When using script expressions in a WHERE Clause, the value must be entered in single quotation marks.

For more information on script expression, see Script Expressions.

Syntax:
[Field_name][Space][Operator][Space]'#[Script-Expression]#'
BUDAT >= '#{DateTime.Now.AddYears(-5).ToString("yyyyMMdd")}#'

Examples:

Input Output Description
#{ DateTime.Now.ToString("yyyyMMdd") }# yyyyMMdd Current date in SAP format
#{ String.Concat(DateTime.Now.Year.ToString(), "0101") }# yyyy0101 Current year concatenated with “0101”
#{ String.Concat(DateTime.Now.ToString("yyyy"), "0101") }# yyyy0101 Current year concatenated with “0101”
#{ String.Concat(DateTime.Now.ToString("yyyyMMdd").Substring(0,4), "0101") }# yyyy0101 Current year concatenated with “0101”

Using Subqueries #

Note: The usage of subqueries is only possible as of SAP Release 7.40, SP05.

A subquery is an SQL query nested inside a larger query. Subqueries are nested queries that provide data to the enclosing query. Subqueries need be enclosed with parenthesis and can return individual values or a list of records. Get more details about subqueries on the SAP help site - Conditions.

In the following example a subquery is used with the IN operator. The following statement returns all the active customers (rows in the table KNA1) that have i.e. a sales document in the table VBAK for sales document header data.

WHERE Clause Subquery

WHERE Clause Editor #

The WHERE clause editor offers a toolkit for those who are not familiar with the syntax of the WHERE clause.
Click [Editor mode] to open the editor.

WHERE-Clause-Builder-01

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.
    • The default structure for a single criteria with parameters is [Column][Operator][Parameter] e.g., MARC~WERKS = [p_WERKS].
  • [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.
WHERE-Clause-Builder-Example Parameter adds a previously defined runtime parameter, see Using Runtime Parameters in the WHERE Clause Editor.

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.

Runtime Parameters in the WHERE Clause Editor #

You can create and assign runtime parameters, when using WHERE Clause.