Using WHERE Clause #
- Select and double-click an extraction of type “table” in the main window of the Designer. The window “Define data source for SAP Table “ opens.
- Navigate to tab WHERE Clause.
- Enter the WHERE clause.
- 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. MAKT~MATNR. This can be the case with table joins.
[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.
Returns the number of rows/data records of an extraction, considering the WHERE and HAVING clauses stored.
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 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.
Tip: To increase extracting performance, make sure to place the idexed fields as the first selection filter operation in the WHERE clause.
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.
|=, 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 the operand operand1 matches (does not match) the pattern in the operand operand2.|
|(NOT) BETWEEN||True if the content of the operand operand (not) lies between the values of the operands operand1 and operand2.|
Get more details on the OpenSQL syntax on the SAP help site - Select WHERE
Using Subqueries #
Note: The usage of subqueries is only possible starting from 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 Restrictions #
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.
Script Expressions #
Starting from version 2.13.5 scripted expressions are supported within the WHERE clause.