DQS OData v4 Support
OData defines a system of querystring options that can help construct complicated queries to get the resource you want. As an architecture that's built on top of the current features of the Web, DQS support OData v4 query strings. OData (Open Data Protocol) is an OASIS Standard that defines best practice for building and consuming RESTful APIs.
When constructing your query, for example Nominal Accounts, the collection is specified in the heading of the first set of curly brackets. Query operations such as filter, orderby, skip and top can be specified as querystring options. The names of all system query options are comma separated. Each query option is proceeded with a colon, then the query option value as a string or integer
For example, to page a result set the skip and top querystring parameters could be included as follows:
| { NominalAccounts(TradingEntityIds: ["qa"], skip: 50, top: 20) { NominalAccountId } } |
The request returns items 51 to 70 in the list of nominal accounts.
Odata Parameters
The full list of querystring parameters are:
| Parameter | Purpose | Example |
| ,filter* | The filter system query option restricts the set of items returned. | Return all NominalAccounts whose DetailDescription does not contain the word ‘HEADER’ ,filter: "substringof(‘HEADER’, DetailDescription) eq false" |
| ,orderby | Specifies an expression for determining what values are used to order the returned collection. | OrderBy values in the DetailDescription field in descending order. ,orderby: "DetailDescription desc" |
| ,skip | Returns a subset of entries (starting with Entry N+1). | Return items from entity 51 onwards. ,skip: 50 |
| ,top | Returns only the first N items of the set | Return the first 20 ,top: 20 |
* See below for more details on this very powerful filter.
The real power of these filters come when combining the above parameters. Note that combinations are separated with a comma. For example: return all NominalAccounts who's DetailDescription does not contain 'Cost' and have a PrivilegeLevel greater than 5, in descending order and only return items 301 to 400.
| { NominalAccounts(TradingEntityIds: ["qa"], filter: "contains(DetailDescription, 'Cost') eq false and PrivilegeLevel gt 5", orderby: "DetailDescription desc", skip: 300, top: 100) { NominalAccountId, DetailDescription } } |
Instantiating a Query
If you wish to filter on a property of an object within the query, for example, return all Departments with a NominalGroup which equals 'ABC Group' within the NominalCategory object, you would express it as below
| { Departments(TradingEntityIds : ["qa"], Filter: "NominalCategory/NominalGroup eq 'ABC Group'") { DepartmentId NominalCategory{ NominalCategoryId NominalGroup } } } |
Instantiating a Property within a Collection in a Query
If you wish to filter on a property of a collection within the query, for example, return all PhysicalTrades with a CostCode which equals 'DEL' within the PhysicalTradeCost collection, you would express it as below
| { PhysicalTradeBases(TradingEntityIds : ["qa"], Filter: "PhysicalTradeCosts/any(s: s/CostCode eq 'ABC')") { PhysicalTradeId PhysicalTradeCosts{ CostCode InvoicedValue } } } |
The ,filter Parameter
The Heading with a ,filter query option identifies a subset of the Entries from the collection of entries identified in the query body. The subset is determined by selecting only the entries that satisfy the predicate expression specified by the query option.
The expression language that is used in ,filter operators supports references to properties and literals. The literal values can be strings enclosed in single quotes, numbers and Boolean values (true or false) or any of the additional literal representations shown in the Abstract Type System section.
The operators supported in the expression language are shown in the following table.
| Operator | Description | Example |
| Local Operators | ||
| Eq | Equal | ,filter: "UserAccountId eq ‘JT’" |
| Ne | Not equal | ,filter: "UserAccountId ne ‘JT’" |
| Gt | Greater than | ,filter: "PrivilegeLevel gt 5" |
| Ge | Greater than or equal | ,filter: "PrivilegeLevel ge 6" |
| Lt | Less than | ,filter: "PrivilegeLevel lt 9" |
| Le | Less than or equal | ,filter: "PrivilegeLevel le 8" |
| And | Logical and | ,filter: "PrivilegeLevel le 9 and PrivilegeLevel gt 5" |
| Or | Logical or | ,filter: "PrivilegeLevel le 4 or PrivilegeLevel gt 8" |
| Not | Logical negation | ,filter: "not endswith(ClientAccountId, ‘P’)" |
| Arithmetic Operators | ||
| Add | Addition | ,filter: "ContractQuantity add 20 gt 100" |
| Sub | Subtraction | ,filter: "ContractQuantity sub 20 lt 100" |
| Mul | Multiplication | ,filter: "ContractQuantity mul 2 gt 2000" |
| Div | Division | ,filter: "ContractQuantity div 2 gt 100" |
| Mod | Modulo | ,filter: "ContractQuantity mod 2 eq 0" |
| Grouping Operators | ||
| () | Precedence grouping | ,filter: "(ContractQuantity sub 50) gt 100" |
In addition to operators, a set of functions are also defined for use with the filter query string operator. The following table lists the available functions. NOTE: ISNULL and COALESCE operators are not defined. Instead, there is a null literal which can be used in comparisons.
| Function | Example |
| String Functions | |
| bool contains(string po, string p1) | ,filter: "contains(ClientAccountId, 'DAVID') eq true" |
| string po bool(string p1, string p2) | ,filter: "TraderCode in ('WB', 'JT')" |
| bool endswith(string p0, string p1) | ,filter: "endswith(ClientAccountId, '.T') eq true" |
| bool startswith(string p0, string p1) | ,filter: "startswith(ClientAccountId, 'API') eq true" |
| int length(string p0) | ,filter: "length(ClientAccountId) eq 6" |
| int indexof(string p0, string p1) | ,filter: "indexof(CompanyName, 'lfreds') eq 1" |
| string replace(string p0, string find, string replace) | ,filter: "replace(CompanyName, ' ', '') eq 'AlfredsFutterkiste'" |
| string substring(string p0, int pos) | ,filter: "substring(CompanyName, 1) eq 'lfreds Futterkiste'" |
| string substring(string p0, int pos, int length) | ,filter: "substring(CompanyName, 1, 2) eq 'lf'" |
| string tolower(string p0) | ,filter: "tolower(CompanyName) eq 'alfreds futterkiste'" |
| string toupper(string p0) | ,filter: "toupper(CompanyName) eq 'ALFREDS FUTTERKISTE'" |
| string trim(string p0) | ,filter: "trim(CompanyName) eq 'Alfreds Futterkiste'" |
| string concat(string p0, string p1) | ,filter: "concat(concat(City, ', '), Country) eq 'Berlin, Germany'" |
| Date Functions | |
| DateTime | ,filter: "BirthDate eq 1948-12-08T00:00:00Z" |
| Date | , filter: "Date(BirthDate) eq 1977-01-27" |
| int day(DateTime p0) | ,filter: "day(BirthDate) eq 8" |
| int hour(DateTime p0) | ,filter: "hour(BirthDate) eq 0" |
| int minute(DateTime p0) | ,filter: "minute(BirthDate) eq 0" |
| int month(DateTime p0) | ,filter: "month(BirthDate) eq 12" |
| int second(DateTime p0) | ,filter: "second(BirthDate) eq 0" |
| int year(DateTime p0) | ,filter: "year(BirthDate) eq 1948" |
| Math Functions | |
| double round(double p0) | ,filter: "round(Freight) eq 32d" |
| decimal round(decimal p0) | ,filter: "round(Freight) eq 32" |
| double floor(double p0) | ,filter: "round(Freight) eq 32d" |
| decimal floor(decimal p0) | ,filter: "floor(Freight) eq 32" |
| double ceiling(double p0) | ,filter: "ceiling(Freight) eq 33d" |
| decimal ceiling(decimal p0) | ,filter: "floor(Freight) eq 33" |
| Type Functions | |
| bool IsOf(type p0) | ,filter: "isof('NorthwindModel.Order')" |
| bool IsOf(expression p0, type p1) | ,filter: "isof(ShipCountry, 'Edm.String')" |