Skip to content

Working with Lists in the WHERE-Clause Editor

The following article shows how to use lists and SELECT statements in the WHERE Clause Editor of the Xtract Table component.
Lists can contain multiple values separated by commas e.g., 1,10 or “1”, “10”.

Static Lists

The depicted example statement returns all active customers (rows in the table KNA1) that have an address in one of the following cities: Berlin, Stuttgart, Paris, Seattle, Hong Kong or Dongguan.

  1. Create a Xtract Table extraction.
  2. Look up the table KNA1, see Documentation: Define the Xtract Table Component.
  3. Open the WHERE Clause tab and click [Editor Mode] to open the WHERE clause editor.
    where-clause-editor
  4. Click [Add criteria], then [Default with literal] to create an empty template in the WHERE clause editor.
  5. Select the column ORT01 from KNA1 as the data you want to filter.
  6. Select IN as the operator. IN is the only operator that can be used for lists.
  7. Select List as the type of the static filter value.
    where-clause-editor
  8. Click [Press to Edit] in the static value component of the WHERE clause. The window "Edit List" opens.
  9. Select String as the Type of the list. When working with numbers, select Number.
  10. Click [Add] to add items to the list. You can edit items via double-click.
    where-clause-editor
  11. Click [OK] to confirm your input.
  12. Click [Load live Preview] or run the SSIS package to check the output.
    When providing values for the list parameter, use multiple values separated by commas e.g., 1,10 or “1”, “10”.

SELECT Statement

SELECT statements can be used to select data from SAP tables, see ABAP Documentation: Open SQL SELECT.
The depicted example statement returns all active customers (rows in the table KNA1) that have a sales document in the table VBAK for sales document header data.

Note

The usage of SELECT statements is only possible as of SAP Release 7.40, SP05.

  1. Create a new Xtract Table extraction.
  2. Look up the table KNA1, see Documentation: Define the Xtract Table Component.
  3. Open the WHERE Clause tab and click [Editor Mode] to open the WHERE clause editor.
    where-clause-editor
  4. Click [Add criteria], then [Default with literal] to create an empty template in the WHERE clause editor.
  5. Select the column KUNNR from KNA1 as the data you want to filter.
  6. Select IN as the operator. IN is the only operator that can be used for lists.
  7. Select List as the type of the static filter value.
    where-clause-editor
  8. Click [Press to Edit] in the static value component of the WHERE clause. The window "Edit List" opens.
  9. Select SELECT as the Type of the list.

  10. Enter the following SELECT statement to create a list that contains all items of the column KUNNR from the SAP table VBAK:

    SELECT KUNNR FROM VBAK

    where-clause-editor

  11. Click [OK] to confirm your input.

  12. Click [Load live Preview] or run the SSIS package to check the output.
Last update: February 28, 2024