NCache 4.6 - Online Documentation

Object Query Language (OQL) Syntax

 
This feature is not available in NCache Express and Professional edition.
 
NCache supports SQL-like language called Object Query Language (OQL). A rich set of operators are provided by NCache according to required query criteria to fetch the desired result set from cache. 
 
OQL is not case sensitive.
 
The following operator categories are supported in NCache queries:
 
Category
Operator
<Query>
= , == , != , <> , < , > , <=, >=, IN, LIKE , NOT LIKE
Logical Operators
AND , OR , NOT
Aggregate Functions
Sum, Count, AVG, Min, Max
Miscellaneous
DateTime.Now , DateTime ("any date time compatible string")
 
Syntax of the language is explained with the help of examples below:
 
Basic Query Operators (<Query>)
 
Operator
Description
Use (Example)
= OR ==
Equals to
"SELECT Product WHERE this.ProductID == ?"
!= OR <>
Not equals to
"SELECT Product WHERE this.ProductID <> ?"
<
Less than
"SELECT Product WHERE this.ProductID < ?"
>
Greater than
"SELECT Product WHERE this.ProductID > ?"
<=
Less than equal to
"SELECT Product WHERE this.ProductID <= ?"
>=
Greater than equal to
"SELECT Product WHERE this.ProductID >= ?"
IN
In a range
"SELECT Product this.UnitsAvailable IN (?,?,?,?))"
LIKE
Mostly used for wild card base searching.
Use "*" for one or many occurrence of character.
Use "." for  single occurrence of character.
"SELECT Product WHERE this.ProductName LIKE ? AND this.Category LIKE ?"
NOT LIKE
Not like pattern
"SELECT Product WHERE this.ProductID NOT LIKE ?"
 
Logical Query Operators
 
Operator
Description
Use (Example)
AND
Retrieve merged result set for two or more conditions.
"SELECT Product WHERE this.ProductName LIKE ? AND this.Category LIKE ?"
OR
Retrieve result set which fulfills any one condition.
"SELECT Product WHERE this.ProductName NOT LIKE ? OR this.Category NOT LIKE ?";
NOT
Retrieve result set which does not match the given criteria.
"SELECT Product WHERE this.ProductID NOT LIKE ?"
 
Aggregate Function
 
Operator
Description
Use (Example)
Sum
Calculate sum of result set of specified criteria.
"SELECT SUM(Product.UnitsAvailable) WHERE this.ProductID <= 10"
Count
Calculates count of the result set for a specified criteria.
"SELECT count(Product) WHERE this.ProductID < ?"
Avg
Calculates average of the result set for specified criteria.
"SELECT AVG(Product.UnitsAvailable) WHERE this.ProductID < ?"
Min
Returns minimum of the result set for specified criteria.
"SELECT MIN(Product.UnitsAvailable) WHERE this.Category = ?"
Max
Returns maximum of the result set for specified criteria.
"SELECT MAX(Product.UnitsAvailable) WHERE this.Category = ?
 
Miscellaneous
 
Operator
Description
Use (Example)
GROUP BY
Group data based on an aggregate function.
SELECT this.Category, COUNT(Product) WHERE this.ProductID > ? GROUP BY this.Category
ORDER BY
Sort the result set based on criteria in descending or ascending order.
SELECT Product WHERE this.Category = ? ORDER BY this.Category
DateTime.Now
Retrieve result set with respect to current date time.
"SELECT Order WHERE this.OrderDate = ?"
DateTime ("any date time compatible string")
Retrieve result set with respect to specified date time.
"SELECT Order WHERE this.OrderDate = ?"
 
 
See Also