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

Using a WHERE Clause #

  1. Open an Xtract Table component.
  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.

[Extraction Settings]
Opens the extraction settings menu, see Extraction Settings.

[Edit Parameters]
Opens the runtime parameter menu, see Using Runtime Parameters in the 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.

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.

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.

Runtime Parameters in the WHERE Clause Editor #

To create and assign runtime parameters, follow the steps below:

  1. In the main window of the component click [Edit Parameters]. The window “Edit Runtime Parameters” opens.
    dd-parameters
  2. Click [Add Scalar] to define scalar parameters to be used as placeholders for actual values.
    Click [Add List] to define list parameters that contain multiple values separated by commas e.g., 1,10 or “1”, “10”.
    The placeholders need to be populated with actual values at extraction runtime.
    Tip: Parameter0..-n is the default naming for the added parameter. You can enter a name of your choice.
  3. Click the drop-down menu (2) and assign one of the following data types to a parameter.
    The data types may correlate to SAP data types.
    • String: Can be used for any type of SAP selection field. List parameters are always of type String.
    • Number: Can be used for numeric SAP selection fields.
    • Flag: Can only be used for SAP selection fields, which require an ‘X’ (true) or a blank ‘‘ (false) as input value.
  4. Click [OK] (3) to confirm.
  5. Navigate to the WHERE clause tab in the main window of the extraction and open the WHERE clause editor.
  6. Add a new WHERE clause criteria that uses the [Default with Parameter] template.
  7. Click on the Parameter component. A drop-down list that displays all available parameters opens. Select a parameter from the list.
    WHERE-Clause-Builder-Example
  8. To test the WHERE clause, click [Load live Preview]. Provide parameter values when prompted.

For more information on how to use runtime parameters in Alteryx, see Edit Runtime Parameters.

How to Pass Values at Runtime #

Follow the steps below to replace runtime parameters with actual values at runtime:

  1. Create and assign runtime parameters in the Xtract component.
  2. Close the Xtract component.
  3. Define the input values for the runtime parameters (1), e.g., single values or lists.
    table-input
  4. Make sure that the data type of the input values match the data type of the corresponding runtime parameter.
  5. Make sure that the name of the input matches the name of the corresponding runtime parameter, e.g, the values of the input Parameter0 is automatically assigned to a runtime parameter by the same name.
  6. Connect the input to the Xtract component using the input anchor “P” (2).
  7. Run the workflow to test if the parameters are assigned correctly.

Note: Parameterization via input anchors is supported as of Xtract for Alteryx version 1.19. When using an older version of Xtract for Alteryx, parameterize Xtract components using batch macros, see Parameterizing via Batch Macros.