IBM Support

Support for Query Options in SAP OData connector

White Papers


Abstract

A query option is a set of query string parameters applied to a resource that can help control the amount of data being returned for the resource in the URL. It is is basically requesting that a service perform a set of transformations such as filtering, sorting, etc. to its data before returning the results.

Content

 Supported Query Options for SAP OData connector
SAP OData Query Functions
TOP

The option requests the number of items to be included in result.
SKIP The option skips the first N number of items in the query and are not included in result
FILTER The Query option filters collection of resources. The expression given in filter query is evaluated for each resource in collection
ORDERBY The Query request resource to be retrieved either in ascending or descending order based on the given expression.
SELECT The Query retrieves only the given columns in the expression along with the key columns.
SKIPTOKEN The Query enables efficient server-side paging of query results when the size of the data set is too large to be retrieved in a single request.
Input for Query Options

For a new Datastage flow-

  • Drag Asset Browser to create a new Datastage flow.
  • Select the connection for SAP OData.
  • Select the Service Name
  • Select Entity Set and click on Add.

A job would be created with the input properties given by you.

image-20240409190550-1
  • Double click the connector to open properties.
  • In the stage property, there will be Query Options property.

image-20240409190550-2

  •  You need to give value in this property for implementing Query Options in the SAP OData connector.

For an existing job

    

  • Select the Datastage flow to be modified.
  • The job will open, double click the connector to open the palette.
  • In the stage property, there will be Query Options property.
image-20240409191112-1

  • You need to give value in this property to implement Query Options in the SAP OData connector.

Format to give query expression in Query Options property: 

  • Simple Query – A query with single query options applied.
  1. filter=Property_Name eq ‘XYZ’
  2. select=Name
  3.  filter=CreationDate eq datetime'2023-05-04T00:00:00'
  4. orderby=PurchaseOrder [desc|asc]
  5. skiptoken=800
  • Complex Query – A query with combination of two or more Query Options with same or different type.
  1. filter=Name eq ‘John’ and Currency=’USD’
  2. filter=startswith(PurchaseOrder,’4’) eq true & orderby=Name
  3. filter=Currency eq ‘USD’ & orderby= Name desc & skiptoken=500

Note – The select query should always contain key properties along with any other properties that you might include.

For example, if an entity set A_PurchaseOrder has PurchaseOrder as its key property then select query must include PurchaseOrder.

Example: select=PurchaseOrder, PurchaseOrderType, Currency.

If you give select=Currency, Address, the job will fail as PurchaseOrder is not given in select query which is the key property of the entity set.

Input for Top and Skip query
Input for Top and Skip query should always be given in Row Limit and Row to start reading property respectively.
Note: Top and skip query should not be given in Query Options property of the connector.
TOP
You should give value in Row Limit property for top query option.

image-20240410111957-1

image-20240410111957-2

SKIP
You should give value in Row to start reading property for skip query option.

image-20240410112023-3

image-20240410112023-4

Note - In case of large number of records, SAP OData connector might limit retrieving maximum 1000 records due to pagination of service. If you give skip value of 200 in a data set of 3000 records the connector shall return records numbers from 201 to 1200. You can verify the skip functionality by verifying the records retrieved by the connector.

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSHGYS","label":"IBM Cloud Pak for Data"},"ARM Category":[{"code":"a8m3p000000UoTQAA0","label":"Utilities"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Document Information

Modified date:
17 April 2024

UID

ibm17147776

Page Feedback