Sterling OMS First Complex Query

By | 10/29/2018

First Complex Query

Sterling OMS First Complex Query

Sterling OMS First Complex Query : Do you where exactly complex query been used part of Sterling Order Management System Out of the box (OOB) ? Any guess ?

From the Menu — Order — Order Console is the best example for Complex query.

Order Console Search Screen

When user trying to search order, getOrderList() api been called with input. Input XML input helps to create the SQL query to fetch the data.

Complex Query Definition from Internet: Complex queries help to narrow a detailed listing obtained as output from an API. To generate the desired output, you can pass queries using AND or OR operators in the input XML of an API.

Sterling OMS First Complex Query

As part of this post, lets understand the complex query with help of some examples.

Complex Query Example for AND / OR / LIKE / NE Operators

getItemList API Input Result
<Item OrganizationCode=”Matrix” ItemGroupCode=”DS” /> 3 Items found
Delivery
Home_Delivery
Return
<Item OrganizationCode=”Matrix” ItemGroupCode=”DS” >
<ComplexQuery Operator=”AND”>
<And>
<Or>
<Exp Name=”ItemID” Value=”Delivery” QryType=”LIKE”/>
</Or>
</And>
</ComplexQuery>
</Item>
2 Items found
Delivery
Home_Delivery
<Item OrganizationCode=”Matrix” ItemGroupCode=”DS” >
<ComplexQuery Operator=”AND”>
<And>
<Or>
<Exp Name=”ItemID” Value=”Delivery” QryType=”NE”/>
</Or>
</And>
</ComplexQuery>
</Item>
2 Items found
Home_Delivery
Return
<Item OrganizationCode=”Matrix” ItemGroupCode=”PROD” >
<ComplexQuery Operator=”AND”>
<And>
<Or>
<Exp Name=”Description” Value=”Plasma HDTV” QryType=”LIKE”/>
<Exp Name=”Description” Value=”Plasma” QryType=”LIKE”/>
</Or>
</And>
</ComplexQuery>
</Item>
2 records found

Complex Query Example for Date Range

getOrderList API Input Result
<Order EnterpriseCode=”Matrix-R” FromOrderDate=”20170301″
ToOrderDate=”20170307″ OrderDateQryType=”DATERANGE” />
11 records found in this date range

User Defined Complex Query

Is it possible to create our own complex query ? Yes we can create our input xml (shown above) and call API’s defined below.

Only item, organization, order, order line, shipment and shipment line entities are supported for performing complex queries

Complex queries are supported for the following APIs:

  • deletePricelistAssignmentList
  • deletePricingRuleAssignmentList
  • getAttributeAllowedValueList
  • getClassificationPurposeList
  • getCustomerContactList
  • getExceptionList
  • getInventoryReservationList
  • getItemList
  • getOrderLineList
  • getOrderList
  • getOrganizationList
  • getSearchIndexTriggerList
  • getShipmentList
Field Data Supported Query Type
Char/VarChar2 EQ – Equal to
FLIKE – Starts with
LIKE – Contains
GT – Greater than
LT – Less than
NE – Not equal to
Number BETWEEN – Range of values
EQ – Equal to
GE – Greater than or equal to
GT – Greater than
LE – Less than or equal to
LT – Less than
NE – Not equal to
Date DATERANGE – Range of dates
EQ – Equals
GE – Greater than or equal to
GT – Greater than
LE – Less than or equal to
LT – Less than
NE – Not equal to
Date-Time BETWEEN – Range of dates
EQ – Equals
GE – Greater than or equal to
GT – Greater than
LE – Less than or equal to
LT – Less than
NE – Not equal to
Null ISNULL – Return records that are null.
NOTNULL – Return records that are not null.

Tips

  • You can define only one ComplexQuery under a single element. For example, you cannot have two ComplexQuery operator under an Item element.
  • You cannot add a single complex query against two different tables.
  • The attribute with no value is not considered in the complex query, like Attribute=””.
  • For attributes appended with QryType, specify a query type value from the following table. This is case sensitive.
  • There can be only one element under the ComplexQuery namely, And or Or.
  • And or Or elements can have one or many child elements as required.
  • And or Or elements can have other And or Or expression elements as child elements.

Happy Learning !!!

Register with us to get more updates

Sterling OMS Forum : View/Ask Questions

Join our channel for updates

Leave a Reply

Your email address will not be published. Required fields are marked *