WHERE Clause
A WHERE clause can be used to filter table records, see SAP ABAP Documentation: SELECT-WHERE. Enter WHERE clauses manually in Text mode or use the WHERE Clause Editor in Editor Mode.
Create a WHERE Clause
- Open a Xtract Table component.
- Navigate to the tab WHERE Clause.
- Enter a WHERE clause manually or use the WHERE Clause Editor.
- Click [Load live preview] to display the results in the Preview section.
WHERE Clause Text Mode
The WHERE clause text mode allows you to directly enter a WHERE clauses. The text mode of the WHERE clause supports script expressions.
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.
WHERE Clause Syntax
The WHERE Clause syntax generally uses the following structure:
Examples:
| Rule | Correct | Wrong |
|---|---|---|
| Enter a space before and after the equal sign |  YEAR = '1999' |  YEAR= '1999 ', YEAR ='1999' or YEAR='1999' |
| Set floating point numbers in single quotation mark | KMENG > '10.3' | KMENG > 10.3 |
| Values must use the internal SAP representation: Date: YYYYMMDD Year Period: YYYYPPP Numbers with leading zeroes, e.g., customer numbers | 19990101 1999001 0000001000 | 01.01.1999 001.1999 1000 |
The following operations are supported in the WHERE clause:
| Operator | Description |
|---|---|
| =, 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. |
For more details on the OpenSQL syntax, see SAP Help: Select WHERE
Tip
To increase extracting performance, make sure to place the indexed fields as the first selection filter operation in the WHERE clause.
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.
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 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. 
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
[Table~Column][Operator][Filer-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].
- The default structure for a single criteria with static values is
- [Add Criteria Group] adds a group of criteria.
- The default structure for a criteria group is
([Table~Column1][Operator1][Filter-Value1][Boolean][Table~Column2][Operator2][Filter-Value2])e.g., (MARC~PSTAT = 'L' OR MARC~PSTAT = 'LB').
- The default structure for a criteria group is
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
The following buttons and options are available in the WHERE Clause Editor:
| Icon | Component | Function |
|---|---|---|
| Delete row | deletes a criteria. | |
| Move row up | changes the sequence of the criteria. The selected criteria moves up. The sequence of criteria can also be changed with Drag and drop. | |
| Move row down | changes the sequence of the criteria. The selected criteria moves down. The sequence of criteria can also be changed with Drag and drop. | |
| Column | adds a column. Click on the component to select a column from the available tables. | |
| SQL | adds an Open SQL statement, see SAP Help: Open SQL. | |
| Operator | adds an operator e.g., =, <, >, etc. | |
| 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, see Working with Lists in the WHERE-Clause Editor. | |
| Criteria | adds a new criteria after the selected criteria. | |
| Group | adds a new group of criteria the selected criteria. | |
| Parameter | adds a previously defined SSIS variable, see Parameterization using SSIS Variables. |
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.
Edit and Delete 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.
SAP System Fields
You can use SAP system fields for date and time in a WHERE clause. The usage of SAP system fields requires SAP NW 7.4 SP5 or higher and the custom function module /THEO/READ_TABLE.
Example:
- Navigate to WHERE Clause Editor and select a column of the type Date here: BUDAT .
- Delete the criterion "Value" and use the criterion "SQL" .
- Within the "SQL" criterion, use the supported system fields for date and time with a preceding "@" character, here: @sy-datum .
- Click [Load live preview] to check the result.
Dynamic WHERE Clause with SSIS Variables
You can use SSIS variables and parameters as placeholders for filter values in the WHERE Clause. To parameterize the whole WHERE Clause, see Parameterization using Expression Properties.
- Define an SSIS variable / parameter. Make sure the data type of the variable matches the data type of the table records you want to filter.
- Open the Xtract Table component and navigate to the WHERE Clause tab.
- Click [Editor mode] to open the WHERE Clause Editor.
- Add a new filter criteria and use the [Default with Parameter] template.
- Click inside the Parameter component. A drop-down-list that displays all available SSIS variables and parameters opens. Select your SSIS variable / parameter from the list.
- To test the WHERE clause, assign a default value for the SSIS variable and click [Load live preview].
- Click [OK] to save the component.
Related Links
- Knowledge Base Article: Delta Table Extraction
- Knowledge Base Article: Working with Lists in the WHERE-Clause Editor
- Knowledge Base Article: LIKE Operand in WHERE Clauses