Defining complex queries

Edit online

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:
  • deletePricelistAssignmentList
  • deletePricingRuleAssignmentList
  • getAttributeAllowedValueList
  • getClassificationPurposeList
  • getCustomerContactList
  • getExceptionList
  • getInventoryReservationList
  • getItemList
  • getOrderLineList
  • getOrderList
  • getOrganizationList
  • getSearchIndexTriggerList
  • getShipmentList
  • getShipmentLineList
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 application Javadoc. For more information on valid database columns, see the application 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:

  • 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 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
  • 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:

(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.