Defining complex queries

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.

For example, you can query the getItemList API based on the unit of measure, item group code or any parameters provided in the API definition, using the complex query operators, And or Or.

Complex queries are supported for the following APIs:
Note: Only item, organization, order, order line, shipment and shipment line entities are supported for performing complex queries. The attributes for complex query must map directly to valid database columns of these entities and should be within the same XML element.
Note: Use of complex query in conjunction with case insensitive search for a column is not supported. However, it can be achieved by using the shadow column in Name Attribute of the Exp element of complex query. For example, if the shadow column for ORGANIZATION_CODE in the YFS_ORGANIZATION table is ORGANIZATION_CODE_LC, then the getOrganizationList API can be called with the following input to perform a case insensitive search on OrganizationCode whose value is either org1 or org2.
<Organization OrganizationCode="org1" OrganizationCodeQryType="LIKE">
	<ComplexQuery Operator="OR">
		<Or>
			<Exp Name="OrganizationCodeLc" QryType="LIKE" Value="org2"/>
		</Or>
	</ComplexQuery>
</Organization>

For more information about these APIs, see the Javadocs. For more information on valid database columns, see the Sterling Selling and Fulfillment Foundation ERDs.

Example: adding complex queries in getItemList API

Consider the following scenario for adding complex queries to the getItemList API.

The getItemList API returns a list of items based on the selection criteria specified in the input XML such as item attributes, aliases, category, and so on. You can create complex queries in the getItemList input XML as shown in the following example:

<Item OrganizationCode="ARGOS" ItemGroupCode="PROD" > 
  <PrimaryInformation KitCode="BUNDLE" KitCodeQryType="NE">
  </PrimaryInformation> 
    <ComplexQuery Operator="AND"> 
       <And> 
          <Or> 
            <Exp Name="ProductLine" Value="DO" ProductLineQryType="EQ"/> 
            <Exp Name="ProductLine" Value="Expert" ProductLineQryType="EQ"/>     
          </Or> 
        </And> 
    </ComplexQuery> 
</Item>

OrganizationCode and ItemGroupCode are the two attributes of the <Item> element and KitCode and KitCodeQryType are the attribute of the <PrimaryInformation> element considered in this example. However you can include any or all of the attributes in the getItemList API. All the attributes in the API are interpreted with an implied And along with the complex query operator.

Apply the following rules when including complex queries:

The values for the QryType attributes vary depending on the datatype of the field. The following table lists the supported query type values used by List APIs for each datatype.

Field DataType Supported Query Type Values
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.
Note: These two query types are used when the column or attribute is set to Nullable in the entity XML.

This example can be interpreted as the following logical expression:

(OrganizationCode="DEFAULT" AND ItemGroupCode="PS") AND 
((PricingQuantityStrategy="IQTY") OR ( ( UnitOfMeasure = "EACH"
OR UnitOfMeasure="HR" ) AND ( ManufacturerName = "XYZ") ))

By following the above example you can include complex queries to achieve desired results from your database using the above mentioned APIs.