Annex II. Forcemanager Query Language
The FM Query language is a subset of the SQL ANSI 92 language (The most common SQL language). The formal specification is described below.
Reserved Words
- AND
- BY
- IS
- NULL
- ASC
- DESC
- LIKE
- OR
- BETWEEN
- IN
- NOT
- ORDER
Operators
| Operator | Description |
|---|---|
| = | Equal |
| <>, != | Not equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| BETWEEN | Between an inclusive range |
| LIKE | Search for a pattern |
| IN | To specify multiple possible values for a column |
| () | Grouping |
Syntax
Select-Stm :
Where-Clause [Order-Clause]
Where-Clause :
Expression
Order-Clause :
ORDER BY Order-List
Order-List :
Id Order-Type, Order-List
Id Order-Type
Order-Type :
ASC
DESC
Expression :
And-Exp OR Expression
And-Exp
And-Exp :
Not-Exp AND And-Exp
Not-Exp
Not-Exp :
NOT Pred-Exp
Pred-Exp
Pred-Exp :
Add-Exp BETWEEN Add-Exp AND Add-Exp
Add-Exp NOT BETWEEN Add-Exp AND Add-Exp
Value IS NOT NULL
Value IS NULL
Add-Exp LIKE StringLiteral
Add-Exp IN Tuple
Add-Exp = Add-Exp
Add-Exp <> Add-Exp
Add-Exp != Add-Exp
Add-Exp > Add-Exp
Add-Exp >= Add-Exp
Add-Exp < Add-Exp
Add-Exp <= Add-Exp
Add-Exp
Add-Exp :
Add-Exp + Mult-Exp
Add-Exp – Mult-Exp
Mult-Exp
Mult-Exp :
Mult-Exp * Negate-Exp
Mult-Exp / Negate-Exp
Negate-Exp
Negate-Exp :
– Value
Value
Value :
Tuple
Id
IntegerLiteral
RealLiteral
StringLiteral
Tuple :
( Select-Stm )
( Expr-List )
Expr-List :
Expression , Expr-List
Expression
Examples:
Here are shown some examples:
- First Name=’Maria’ AND ext_Id IN (1, 2, 6)
- Modified_data BETWEEN ‘2014-05-26T16:30:00’ AND ‘2014-06-26T12:30:00’
- ORDER BY id
- PostalCode IS NULL OR ( PostalCode IS NOT NULL AND Enabled=1)
- Country_Name Like ‘U%’ AND Country_Name <> ‘Ukraine’ AND Z External_Text = ‘UK-001’