The Sage Sales Management 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’