Sterling OMS First Complex Query

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.

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