Defining complex queries

Complex queries help to narrow a detailed listing that is obtained as output from an API. To generate the wanted output, you can pass queries by using the 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, by using the complex query operators, And or Or.

Complex queries are supported for the following APIs:
  • deletePricelistAssignmentList
  • deletePricingRuleAssignmentList
  • getAttributeAllowedValueList
  • getClassificationPurposeList
  • getCustomerContactList
  • getExceptionList
  • getInventoryReservationList
  • getItemList
  • getOrderLineList
  • getOrderList
  • getOrganizationList
  • getSearchIndexTriggerList
  • getShipmentList
  • getShipmentLineList

For more information, see the Javadoc. For more information about the valid database columns, see Entity Relationship Diagrams (ERDs).

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.

Example: adding complex queries in the 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 that are specified in the input XML such as item attributes, aliases, category. 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" QryType="EQ"/> 
            <Exp Name="ProductLine" Value="Expert" QryType="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 that is considered in this example. However, you can include any or all 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 you include complex queries:

  • 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. For example, in getShipmentList API you cannot use ChainedFromOrderHeaderKey and ShipmentLineNo in the same query, since the former belongs to YFS_ORDER_LINE table and the latter is an attribute of the YFS_SHIPMENT_LINE table.
  • 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.

The values for the QryType attributes vary depending on the data type of the field. The following table lists the supported query type values that are used by list APIs for each data type.

DataType field Supported Query Type Values
Char/VarChar2
  • EQ - Equal to
  • FLIKE - Starts with
  • LIKE - Contains
  • GT - Greater than
  • LT - Less than
  • NE - Not equal to
  • VOID - Void
  • NOTVOID - Not void
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.
  • 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.

This example can be interpreted as the following logical expression:

( YFS_ITEM.ORGANIZATION_CODE =  'ARGOS'   ) AND ( YFS_ITEM.ITEM_GROUP_CODE =  'PROD'   ) 
AND ( YFS_ITEM.KIT_CODE <>  'BUNDLE'   ) AND  (  (   YFS_ITEM.PRODUCT_LINE =  'DO'   )  
OR  ( YFS_ITEM.PRODUCT_LINE =  'Expert'   )  )

These examples explain how to include complex queries to achieve wanted results from your database by using the mentioned APIs.