IBM Support

SAP Delta Extract Connector Filter Support

Preventive Service Planning


Abstract

The document provides an overview of how to use SAP Delta Filter in CPD (Cloud Pak for Data) to extract specific data based on various conditions.

Content

Introduction

The document provides an overview of how to use SAP Delta Filter in CPD (Cloud Pak for Data) to extract specific data based on various conditions. It covers the use of operators like EQ (Equal) and BT (Between) to filter data for extraction, along with practical examples of how to structure queries for multiple fields using uniform separators. Additionally, it explains the steps for setting up filter queries in CPD.

image-20250407120314-1

SAP Delta Extract Filter Query in CPD

To design a new DataStage flow job, follow these steps. 

Step 1: Click on New Asset
image-20250407120346-2
Step 2: Click on transform and integrate data 
image-20250407120404-3
Step 3: Provide job name and click on create
image-20250407120421-4
Step 4: Double click on Asset browser
image-20250407120435-5
Step 5: Search and select the connection, ODP contest and data source. 
image-20250407120453-6
Step 6: Alternate way to create a job, drag and drop Delta Extract Connector from pack and Sequential file from under palette and create a connection between Delta Extract Connector and sequential file. 
image-20250407120523-7
Step 7: Double click on stage and provide SAP Connection details & provide ODP Subscriber name and select ODP Context (SAPI) for this testcase. Add filter query in SAP Delta filter textbox.  
image-20250407120542-8
image-20250407120603-9
image-20250407120620-10
Step 8: Provide output file name in sequential file and click on Save.
Step 9: Compile and run job.
image-20250407120637-11
image-20250407120654-12
Step 10: Job running fine using filter and fetching record.
image-20250407120707-13

Summary of SAP Delta Filters in CPD

In CPD (Cloud Pak for Data), the query provided would be written in the filter section when setting up an SAP Delta Extract connection or a similar data extraction process. Here's the query as it would be input in CPD.

Query in CPD:

{"column":"SPRAS","op":"EQ","value":"E”}
Column: SPRAS 
  • Operator: EQ(Equal)
  • Value: E(English)
{"column":"MATNR","op":"EQ","value":"000000000000000011"}
 Column: MATNR
  • Operator: EQ(Equal)
  • Value: 000000000000000011 (specific material number) 
Explanation of the Query:
  • Column: This represents the filed in the data being filtered. In the CPD UI, a filed called 'SAP Delta Filter' can be seen, where the filter can be input. 
  • Operator (op): The operator defines the condition or comparison type applied to the column.
  • Value: The value is the specific condition being filtered for. This is typically a field where the actual value is input to compare the data against. For example, E for the SPRAS column or 000000000000000011 for the MATNR column.

The filter support options like EQ and BT are used to define the conditions for data extraction. Here's what they mean:

1. EQ (Equal): 

  • Definition: The EQ operator stands for "Equal." It is used to filter data based on an exact match.
  • Use Case: When you need to extract data that is exactly equal to a specific value. For example, you can use EQ to extract records where a certain field (e.g., a date, status, or ID) matches a specific value.
  • Example: {"column":"MATNR","op":"EQ,"value":"42433"}
2. BT (Between): 
  • Definition: The BT operator stands for "Between." It is used to filter data that falls within a specified range of values.
  • Use Case: When you need to extract records where a field value falls within a range, such as a date range or numeric range.
  • Example: {"column": "MATNR", "op": "BT", "value": "000000000000000015", "value": "000000000000000022"}

      OPERATOR

          QUERY

EQ

{"column":"MATNR","op":"EQ,"value":"42433"}; {"column": "MATNR", "op": "EQ", "value": "000000000000000019"}

BT

{"column": "MATNR", "op": "BT", "value": "000000000000000015", "value": "000000000000000022"}; {"column": "MATNR", "op": "BT", "value": "000000000000000022", "value": "000000000000000030"}
Operator Support for SAP Delta Extract - till Release 5.1.2

"AND" Separator till Release 5.1.2

Starting from release/5.0.2 till release/5.1.2, the "AND" keyword functioned as a separator rather than a logical operator. The connector did not handle logical combinations directly instead, it sent independent filters to SAP for processing.

Example: {"column":"SPARS","op":"EQ","value":"E"}AND{"column":"MATNR","op":"EQ","value":"000000000000000011"} 

In this case, SAP processed each condition separately and extracted data from SAP.

Operator Support for SAP Delta Extract from Release 5.1.3 & onwards
Starting from release 5.1.3, user can create multiple queries using a separator (;) instead of using ‘AND’ separator. ‘AND’ separator will not be supported going forward to apply filters conditions in CPD.
SAP Delta Connector will be providing an alternative to this using separator (;)
  • The separator (;) used with the same field will act as an OR operation in SAP.
  • The separator (;) used with different fields will act as an AND operation in SAP.
  • Example: {"column":"SPRAS","op":"EQ,"value":"E"}; {"column": "MATNR", "op": "EQ", "value": "000000000000000011"}
These improvements provide greater control over query execution and ensure consistency in logical evaluations within SAP Delta Extract.
Syntax to write Filter Query in CPD
Multiple Fields with Separator (;) (Works Like AND):
  • When you specify conditions on multiple fields using a separator (;).
  • The connector itself doesn't manage these logical combinations directly but sends individual filters to SAP, which then interprets them. Filters on different fields are automatically combined with an AND condition by SAP.
  • Example: {"column":"SPRAS","op":"EQ,"value":"E"}; {"column": "MATNR", "op": "EQ", "value": "000000000000000011"}.
Same Field with Separator (;) (Works Like OR):
  • When you apply conditions to the same field using a (;), The connector itself doesn't manage these logical combinations directly but sends individual filters to SAP, which then interprets them. Filters on the same fields are automatically combined with an OR condition.
  • This means the records returned will match any of the specified values for that field, like how an OR condition works.
  • Example: {"column":"SPRAS","op":"EQ,"value":"Z"}; {"column": "SPRAS", "op": "EQ", "value": "D"}

Query

Syntax

SAP internal Interpretation

Query on same field

{“column”:” field_1”,” op”:” operator_name”,”value”:”value_name1”}.

{“column”:” field_1”,” op”:” operator_name”,”value”:”value_name2”}

Field_1 IN (‘value_name1’,’value_name2’)

Query on different field

{“column”:” field_1”,” op”:” operator_name”,”value”:”value_1”}.

{“column”:” field_2”,” op”:” operator_name”,”value”:”value_2”}

Field_1 = ‘value_1’ AND field_2 = ‘value_2’
Sample Use Cases and Queries Syntax

Datasource

FieldName

Operator

Query

Interpretation OR/AND as per SAP behaviour

0material_text

Same_field (OR)

EQ

{"column":"SPRAS","op":"EQ,"value":"Z"}; {"column": "SPRAS", "op": "EQ", "value": "D"}

SPRAS IN (‘Z’,’D’)

Different_field (AND)

EQ

{"column":"SPRAS","op":"EQ,"value":"E"}; {"column": "MATNR", "op": "EQ", "value": "000000000000000011"}

MATNR= ‘000000000000000011’ AND

SPRAS = ‘E’

0material_attr

Same_field (OR)

EQ

{"column":"MTART","op":"EQ,"value":"XYZ"}; {"column": "MTART", "op": "EQ", "value": "ROH"}

MTART IN (‘ROH’,’XYZ’)

Different_Field (AND)

EQ

{"column":"MTART","op":"EQ,"value":"ROH"}; {"column": "MATNR", "op": "EQ", "value": "000000000000000014”}    

MATNR = ‘000000000000000014’ AND MTART = ‘ROH’

Zds_demo_filter

Same_Field (OR)

EQ

{"column":"TIME_FIELD","op":"EQ","value":"083000"}; {"column": "TIME_FIELD", "op": "EQ", "value": "101010"}; {"column":"TIME_FIELD","op":"EQ","value":"120000"}

TIME_FIELD IN (‘083000’, ‘101010’,’120000’)

Different_Field (AND)

EQ

{"column":"TIME_FIELD","op":"EQ,"value":"083000"}; {"column": "DATE_FIELD", "op": "EQ", "value": "20250115"}; {"column":"CREATED_ON","op":"EQ,"value":"20250115"}

TIME_FIELD = ’083000’ AND DATE_FIELD = ‘20250115’ AND

CREATED_ON = ‘20250115’

[{"Type":"MASTER","Line of Business":{"code":"LOB76","label":"Data Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSVSEF","label":"IBM InfoSphere DataStage"},"ARM Category":[{"code":"a8m0z0000001ir7AAA","label":"Cloud Pak for Data"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
07 April 2025

UID

ibm17230237

Page Feedback