# 2.12 Query Language

The SData query language is used to express filtering conditions with the where query parameter. Here are a few examples of where conditions:

``` http://www.example.com/sdata/myApp/myContract/-/salesOrders?where=billingAddress.countryCode eq 'UK'
and date ge @2008-01-01@
http://www.example.com/sdata/myApp/myContract/-/customers?where=left(name,1) between 'A' and 'N'
http://www.example.com/sdata/myApp/myContract/-/customers?where=name like '%BANK%'
http://www.example.com/sdata/myApp/myContract/-/customers?where=creditLimit - balance ge 1000.0```

## Operators

The query language supports the following operators:

Class Operator Meaning Basic Priority value Example
Member x.y member access 1 billingAddress.country
Unary -x unary minus 2 - discount
not x negation 2 not disabled
Multiplicative x mul y multiplication 3 price mul 1.07
x div y division 3 price div 2
x mod y modulus 3 index mod 2
x - y substraction 4 price - discount
Comparison x eq y equal Yes 5 countryCode eq 'GB'
x ne y not equal Yes 5 countryCode ne 'GB'
x lt y less than Yes 5 price lt 100.0
x le y less than or equal Yes 5 price le 100.0
x gt y greater than Yes 5 price gt 100.0
x ge y greater than or equal Yes 5 price ge 100.0
x between y and z between 5 price between 100.0 and 500.0
x in (y, z) contained in 5 countryCode in ('GB', 'US')
x like y like 5 name like '%BANK%'
Logical x and y logical and Yes 6 countryCode eq 'GB' and amount gt 1000.0
x or y logical or Yes 7 countryCode eq 'GB' or countryCode eq 'US'

The operators with the lowest priority value evaluate first. Within a given class, the operators associate from left to right, except for unary operators that associate right to left.

The operators flagged with Basic = Yes in the table are the minimum that all SData providers support. Advanced SData providers should support the whole table. See conformance levels below.

## Parentheses

Parentheses can be used to override the priority value or association rules. The following table gives some examples:

Expression Parsed as Value
2 mul 5 + 3 mul 2 (2 mul 5) + (3 mul 2) 16
2 mul (5 + 3) mul 2 (2 mul (5 + 3)) mul 2 32
1 eq 1 or 1 eq 2 and 1 eq 3 (1 eq 1) or ((1 eq 2) and (1 eq 3)) true
(1 eq 1 or 1 eq 2) and 1 eq 3 ((1 eq 1) or (1 eq 2)) and (1 eq 3) false

## Literals

Expressions can contain literals - explicit values for basic types. The following table describes the syntax and gives examples:

17 integer
17.0 decimal Decimal separator must be a dot.
'GB' string
"GB" string
"Maxim's" string Single quote does not need to be specially marked inside double quotes and vice versa.
'Maxim''s' string Quoting character can be specially marked by doubling it.
@2008-05-19@ date RFC 3339 "full date" format.
@2008-05-19T18:41:00@ timestamp Interpreted as local time. It  assumes consumer and provider are in same timezone.
@2008-05-19T18:41:00+02:00@ timestamp Time in GMT+2 timezone.
@2008-05-19T16:41:00Z@ timestamp GMT time.

## Functions

The SData query language also supports functions. The following table gives a list of the functions that you may use to build queries (firstName=”John” and lastName=”Doe” in the example):

Class Function Description Example Result
String Functions concat(_str1_, _str2_, ...) Combines strings concat(firstName, " ", lastName) John Doe
left(_str_, _len_) Returns leftmost _len_ characters from _str_ Returns _str_ if _str_ has less than _len_ characters left(firstName, 1) J
right(_str_, _len_) Returns rightmost _len_ characters from _str_ Returns _str_ if _str_ has less than _len_ characters right(firstName, 3) ohn
substring(_str_, _start_, _len_) Returns substring starting at index _start_ and containing _len_ characters _start_ is 1-based (like in SQL) substring(firstName, 3, 2) hn
lower(_str_) Converts _str_ to lower case lower(firstName) john
upper(_str_) Converts _str_ to upper case upper(firstName) JOHN
replace(_str_, _pat_, _repl_) Replaces occurences of _pat_ by _repl_ in _str_ replace(firstName, "oh", "ea") Jean
length(_str_) Returns the length of str length(firstName) 4
locate(_pat_, _str_) Returns the index of the first occurence of _pat_ in _str _Result is 1-based (like in SQL) locate("oh", firstName) 2
trim(str) Removes leading and trailing spaces from _str_ trim("  hello  world  ") hello world
ascii(_str_) Returns ascii code from leftmost character of _str_ ascii(firstName) 74
char(_code_) Converts ascii code to single char string char(74) J
Numeric Functions abs(_x_) Returns the absolute value of _x_ abs(-3) 3
sign(_x_) Returns the sign of _x_ as -1, 0 or 1 sign(-3) -1
round(_x_[, _d_]) Rounds _x_ to _d_ decimals Rounds to nearest integer if _d_ is not specified round(2.576, 2)  2.58
trunc(_x_[, _d_]) Truncates _x_ to _d_ decimals Truncates to integer if _d_ is not specified trunc(2.576, 2) 2.57
floor(x) Returns largest integer <= _x_ floor(2.576) 2
ceil(_x_) Returns smallest integer >= _x_ ceil(2.576) 3
pow(_x_, _y_) Return _x_ to the power of _y_ pow(5, 3) 125
Date Functions currentDate() currentTime() currentTimestamp() Returns the current date/time/timestamp currentDate @2008-05-21@
year(_dt_) month(_dt_) day(_dt_) hour(_dt_) minute(_dt_) second(_dt_) millisecond(dt) tzHour(_dt_) tzMinute(_dt_) Extract specified component from _dt_. _dt_ may be a date, time or _timestamp_ year(currentDate()) 2008

## Protocol Filter Variables

The following protocol filter variables may be used in a query:

Variable Type Description
\$uuid UUID the UUID of the resource (the value of its sdata:uuid attribute)
\$key string the internal key of the resource (the value of its sdata:key attribute)
\$published datetime the creation timestamp of the resource (the value of the <published> element of its Atom entry)
\$updated datetime the last modification timestamp of the resource (the value of the <updated> element of its Atom entry)
\$title string the title of the resource (the value of the <title> element of its Atom entry)

More generally, any sdata:xxx attribute and any <xxx> Atom entry element may be used in a query, as \$xxx. The table above only gives the most commonly used.

A given provider MAY only support a subset of these variables, or none of them. If a provider supports some of these variables, it MUST advertise the list of variables that it supports with an sme:protocolFilters attribute its resource kind schema elements. For example, if a provider supports \$uuid and \$updated on the salesOrder resource kind, its schema must contain:

`<xs:element name="salesOrder" type="salesOrder--type" ... sme:protocolFilters="\$uuid,\$updated" ...>`

SData imposes some rules on queries that contain both protocol filter variables and normal resource properties. Such queries must be written as Q1 and Q2 where Q1 only contains protocol filter variables and Q2 only contains normal resource properties. So, the following query is valid:

`\$updated gt @2011-01-07T09:32:07@ and totalAmount gt 1000.00`

But the following is not valid:

`\$updated gt @2011-01-07T09:32:07@ or totalAmount gt 1000.00`

Protocol filter variables can be used in where parameters as well as in selectors. Here are two examples:

```http://www.example.com/sdata/myApp/myContract/-/salesOrders?where=\$updated gt @2011-01-07T09:32:07
http://www.example.com/sdata/myApp/myContract/-/salesOrders(\$uuid eq '58b0-...')```

The first URL returns a feed containing all the sales orders modified since the specified timestamp. The second one returns a single entry containing the sales order with the specified UUID.

## Conformance levels

SData defines three conformance levels for the query language, as defined in the following table:

`http://www.example.com/sdata/myApp/myContract/-/customers('C001')/salesOrders?where=date ge @2008-01-01@`