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.
<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.
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 |
|
Number |
|
Date |
|
Date-Time |
|
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.