EQL Reference

1. About EQL

EQL (Entity Query Language) is a query language for searching Entity data stored in iPLAss. It is similar to SELECT statement in SQL language. You could consider Entity and Property as Table and Column in SQL respectively.

The EQL samples is as follow.

SELECT oid, name, mail FROM mtp.auth.User WHERE accountId='scott' ORDER BY name

It is also possible to retrieve value by joining related Entity defined as Reference Property.

SELECT oid, name, groups.name (1)
    FROM mtp.auth.User WHERE groups.code='G01'

1	You could retrieve and specify values of Referenced Entity by means of separating it by .(dot), like [Reference Property name].[Property name of Referenced Entity].

Aggregate functions can be used as with SQL.

SELECT job, AVG(sal) FROM Employee GROUP BY job

1.1. Differences from standard SQL

The grammar of EQL is almost equivalent to SELECT statement in standard SQL, there are some differences as follows.

About joining between Entities

It is impossible to join Entities between that there is no Reference relationship defined. As for Entity with Reference relationship defined, it is not necessary to describe JOIN clause.
SQL JOIN Example
```
SELECT job, ename, Department.dname FROM Employee
    LEFT OUTER JOIN Department
    ON Employee.deptno=Department.deptno
```
EQL JOIN Example
```
SELECT job, ename, department.dname FROM Employee (1)
```
1 Referenced Entity must be defined in advance, with a Reference Property named department.

The join(reference) type is always LEFT OUTER JOIN based on the Entity specified in From clause, while it could be specified (OUTER JOIN, INNER JOIN, CROSS JOIN) in SQL.

Also, you should use REFER clause if you want to add item other than the key (oid is the key in Entity) to EQL join condition, while you could specify item other than the key in SQL join condition.
SQL JOIN Example(When specifying item other that the key in join condition)
```
SELECT job, ename, Department.dname FROM Employee
    LEFT OUTER JOIN Department ON Employee.deptno=Department.deptno
    AND Department.dname = 'SALES'
```
EQL JOIN Example(When specifying item other that the key in join condition）
```
SELECT job, ename, department.dname FROM Employee
  REFER department ON department.dname = 'SALES'
```
Subquery in FROM clause is not supported

In EQL, subquery can not be specified in FROM clause.
Set operators is not supported

In EQL, set operator (UNION etc.) is not supported.
Alias is not supported

In EQL, alias name can not be assigned to Entity name, Property name.
Entity name, Property name are case-sensitive

In EQL, Entity name, Property name are case-sensitive. But EQL expression (SELECT, FROM etc.) is case-insensitive.
* (asterisk) expression is not supported

In EQL, wildcard character * (asterisk) can not be specified in SELECT clause. And also * can not be specified in aggregate function, e.g. COUNT. In the case of counting the number of rows,
```
SELECT COUNT(*) FROM Employee
```
is not correct,
```
SELECT COUNT() FROM Employee
```
Do not use * in expression.
Describe join condition with ON clause in correlated subquery

In EQL, in the correlation subquery, the join condition with the outer query is described in the ON clause.

1.2. How to issue EQL

There are two ways to execute EQL. Execute EQL by generating it from String, or using classes that represent EQL.

Issue Query from EQL String

After creating an EQL statement with a String, generate Query instance and issue it via EntityManager. Here is sample code.

import org.iplass.mtp.ManagerLocator;
import org.iplass.mtp.entity.*;
import org.iplass.mtp.entity.query.*;

:
:

//Generate Query instance from EQL String
Query q = new Query("SELECT oid, name, mail FROM mtp.auth.User WHERE accountId='scott' ORDER BY name");

//Issue query via EntityManager
EntityManager em = ManagerLocator.manager(EntityManager.class);
SearchResult<Entity> result = em.searchEntity(q);

In addition, by using PreparedQuery, it is also possible to generate Query instance by passing variables to an EQL template prepared in advance. Here is sample code.

import org.iplass.mtp.ManagerLocator;
import org.iplass.mtp.entity.*;
import org.iplass.mtp.entity.query.*;

:
:

//Generate EQL template in advance
PreparedQuery queryTemplate = new PreparedQuery("SELECT oid, name, mail FROM mtp.auth.User WHERE accountId='${aid}' and name='$s{inputVal}' ORDER BY name");(1)

:
:

//Generate Query instance from PreparedQuery, set a value for parameter aid.
Map<String, Object> bindings = new HashMap<>();
bindings.put("aid", "scott");
bindings.put("inputVal", userInputValue);
Query q = queryTemplate.query(bindings);

//Issue query by using EntityManager
EntityManager em = ManagerLocator.manager(EntityManager.class);
SearchResult<Entity> result = em.searchEntity(q);

1	You could define parameter like ${parameter name}, replace it with actual value when generating Query. And escape processing can be done by using $s{ … } or $sl{ … } of GroovyTemplate.

Issue Query from classes that represent EQL

Construct Query by using classes that represent EQL, issue query via EntityManager. Classes that represent EQL are under the package, org.iplass.mtp.entity.query.

Here is sample code.

import org.iplass.mtp.ManagerLocator;
import org.iplass.mtp.entity.*;
import org.iplass.mtp.entity.query.*;

:
:

Query q = new Query()
        .select("oid", "name", "mail")
        .from("mtp.auth.User")
        .where(new Equals("accountId", "scott"))
        .order(new SortSpec("name", SortType.ASC));

EntityManager em = ManagerLocator.manager(EntityManager.class);
SearchResult<Entity> result = em.searchEntity(q);

2. Component Element

The component element for constructing EQL are described as follows. EQL syntax is described by BNF (Backus-Naur form) together.

Description of expression elements that extend BNF.

Extend the following expression elements for BNF.

[ ] = Optional element
{ } = Grouped element
* = repeat 0 or more times
+ = repeat 1 or more times
"string" = represent a explicit string(not a BNF formula or Symbol)
""description"" = description of actual specified value

2.1. Query

Element that represents an entire EQL statement. Represent the query for Entity. Class for constructing Query is under the package, org.iplass.mtp.entity.query.

EQL Expression Example

SELECT job, ename, department.dname FROM Employee WHERE ename='john'

SELECT job, AVG(sal) FROM Employee GROUP BY job

SELECT job, ename, department.dname FROM Employee
    REFER department ON department.dname = 'SALES'

SELECT /*+ no_index(job) native('ORDERED USE_NL_WITH_INDEX(...)') */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000 ORDER BY ename
    LIMIT 100

Query Class Usage Example

Query q = new Query()
        .select("job", "ename", "department.dname")
        .from("Employee")
        .where(new Equals("ename", "john"));

Query q = new Query()
        .select("job", new Avg("sal"))
        .from("Employee")
        .groupBy("job");

Query q = new Query()
        .select(
                new EntityField("job"),
                new EntityField("ename"),
                new EntityField("department.dname"))
        .from("Employee")
        .refer("department", new Equals("department.dname", "SALES"));

Query q = new Query()
        .select("job", "ename", "department.dname")
        .hint(new NoIndexHint("job"))
        .hint(new NativeHint("ORDERED USE_NL_WITH_INDEX(...)"))
        .from("Employee")
        .where(new And().eq("job", "SALESMAN").gt("sal", 1000L))
        .order(new SortSpec("ename", SortType.ASC))
        .limit(100);

Syntax

<query> ::=: <select clause> <from clause> [<refer clause> {,<refer clause>}*] [<where clause>] [<group by clause>] [<having clause>] [<order by clause>] [<limit clause>]
<select clause> ::=: SELECT <hint comment> [DISTINCT] "<value expression>" {,"<value expression>"}*
"<hint comment>" ::=: "/*+" <hint expression>+ "*/"
<hint expression> ::=: ""hint expression""
<from clause> ::=: FROM ""Entity definition name""
"<refer clause>" ::=: REFER <reference> [ON "<condition>"] [AS OF NOW | "UPDATE TIME" | "<value expression>"]
<where clause> ::=: WHERE "<condition>"
<group by clause> ::=: GROUP BY "<value expression>" {,"<value expression>"}* [ROLLUP | CUBE]
<having clause> ::=: HAVING "<condition>"
<order by clause> ::=: ORDER BY <sort spec> {,<sort spec>}*
<sort spec> ::=: "<value expression>" [ASC | DESC] [NULLS FIRST | NULLS LAST]
"<limit clause>" ::=: LIMIT ""number"" [OFFSET ""start offset""]
"<subquery>" ::=: (<query> [ON "<condition>"])

About Hint Comment

By specifying <hint comment>, it is possible to tune search process, such as by means of using an index during Entity search, or DB native hint comment, etc.. Surround <hint comment> with /*+ */, place it immediately after SELECT. You can separate multiple hint expressions by spaces.

EQL supports the following hint expressions.

Hint Description

Hint	Description
bind	A hint for EQL to specify that literal values are set using binding parameter(PreparedStatement in JDBC) when query is actually issued to DB. `SELECT /+ bind / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000 ORDER BY ename LIMIT 100` In the above case, 'SALESMAN'、1000、100 are executed as binding parameters. By assigning no_bind hint to the literal value to be bound, it is possible to exclude the literal value from being bound. `SELECT /+ bind / job, ename, department.dname FROM Employee WHERE job = /+ no_bind /'SALESMAN' AND sal > 1000 ORDER BY ename LIMIT 100` In the above case, 1000、100 are executed as binding variables.
cache	A hint for caching EQL execution result. Cache Scope and Cache Expiration Time (seconds) can by specified with argument. Cache scope can be set to one of the following. TRANSACTION Cache is only available in the same transaction. GLOBAL Shared cache. It is possible to specify expiration time (seconds) in GLOBAL. If not specified, expiration time is infinite (However, if the expiration time in settings of CacheStore used by backend is set, it will be the limit). When using GLOBAL, you need to set EQL hint after enabling queryCache in Entity definition. GLOBAL_KEEP Shared cache. It is possible to specify expiration time (seconds) in GLOBAL. If not specified, expiration time is infinite (However, if the expiration time in settings of CacheStore used by backend is set, it will be the limit). When using GLOBAL_KEEP, you need to set EQL hint after enabling queryCache in Entity definition. The difference between GLOBAL and GLOBAL_KEEP is as follows: in the case of GLOBAL, the cache is immediately destroyed when the cached Entity data is updated, while in the case of GLOBAL_KEEP, the cache is kept at the value before the update. GLOBAL_RELOAD Shared cache that is automatically reloaded periodically. Reload interval (seconds) must be specified. When using GLOBAL_RELOAD, you need to set EQL hint after enabling queryCache in Entity definition. In the case of GLOBAL_RELOAD, the cache is automatically reloaded at regular intervals on the back end. If Entity data is updated while cached, the value will not be reflected in the cache until it is reloaded. It can be specified as below. Scope：TRANSACTION, `select /+ cache(transaction) / ename from Employee` Scope：GLOBAL、Cache Expiration Time：infinite, `select /+ cache / ename from Employee` Scope：GLOBAL、Cache Expiration Time：10 minutes, `select /+ cache(600) / ename from Employee` Scope：GLOBAL_KEEP、Cache Expiration Time：infinite, `select /+ cache(keep) / ename from Employee` Scope：GLOBAL_KEEP、Cache Expiration Time：10 minutes, `select /+ cache(keep, 600) / ename from Employee` Scope：GLOBAL_RELOAD、Reload Interval：10 minutes, `select /+ cache(reload, 600) / ename from Employee`
fetch_size	A hint for specifying fetch size（Statement#setFetchSize(int) in JDBC）when issuing query to DB. `SELECT /+ fetch_size(100) / job, ename FROM Employee`
index	Explicitly specify to use the Property that specified in argument as an INDEX. The Property specified as INDEX should be set to INDEX, UNIQUE INDEX in Entity definition. `SELECT /+ index(sal) / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000` It is possible to separate multiple properties by , (comma). (However, it depends on DB to choose which INDEX to use actually.) `SELECT /+ index(sal, department.loc) / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000 AND department.loc in('CHICAGO', 'BOSTON')` It depends on DB to choose which Property INDEX to use when neither index hint nor no_index hint is specified.
native	It is possible to specify hint to be assigned to DB native SQL statement (if DB is RDB) that converted from EQL. Specify native hint as a string argument. `SELECT /+ native('ORDERED USE_NL_WITH_INDEX(...)') / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000 AND department.loc in('CHICAGO', 'BOSTON')` If you want to specify a hint clause for a table (Such as index hint in MySQL etc.), specify the table name as the first argument. `SELECT /+ native(q0, 'FORCE_INDEX(...)') / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000 AND department.loc in('CHICAGO', 'BOSTON')`
no_bind	A hint for EQL to specify that binding variable (PrepareStatement in JDBC) not be used when actually issuing a query to the DB. `SELECT /+ no_bind / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000 ORDER BY ename LIMIT 100` For the sake of specifying a specific EQL to execute without binding when issuing EQL if alwaysBind is set to true, according to the setting of RdbAdapterService.
no_index	Exclude Property specified in argument from using as an INDEX alternatives. `SELECT /+ no_index(sal) / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000` It is possible to specify multiple properties separated by , (comma). `SELECT /+ index(job, department.loc) / job, ename, department.dname FROM Employee WHERE job = 'SALESMAN' AND sal > 1000 AND department.loc in('CHICAGO', 'BOSTON')` It depends on DB to choose which Property INDEX to use when neither index hint nor no_index hint is specified.
read_only	If you want to issue the EQL as a read-only transaction, this hint clause can be used. However, if the resultMode of SearchOption in the search call is `ResultMode.STREAM`, it will not be issued as a read-only transaction, but will be issued within the calling transaction. `SELECT /+ read_only / job, ename, department.dname FROM Employee`
suppress_warnings	The hint as an annotation to suppress the output of the EQL warning log. For instance, when executing EQL, the warning log will complain about the INDEX property in search condition not being used. However, this warning is unnecessary when the amount of Entity data is small. For such conditions, the user can suppress these kind of warnings with this hint item. `SELECT /+ suppress_warnings / job, ename, department.dname FROM Employee`
timeout	specify the timeout of a query (Statement#setQueryTimeout(int) in JDBC). It depends on DB exactly how many seconds to disconnect. `SELECT /+ timeout(60) / job, ename, department.dname FROM Employee`

bind

A hint for EQL to specify that literal values are set using binding parameter(PreparedStatement in JDBC) when query is actually issued to DB.

SELECT /*+ bind */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000 ORDER BY ename
    LIMIT 100

In the above case, 'SALESMAN'、1000、100 are executed as binding parameters.

By assigning no_bind hint to the literal value to be bound, it is possible to exclude the literal value from being bound.

SELECT /*+ bind */
    job, ename, department.dname FROM Employee
    WHERE job = /*+ no_bind */'SALESMAN' AND sal > 1000 ORDER BY ename
    LIMIT 100

In the above case, 1000、100 are executed as binding variables.

cache

A hint for caching EQL execution result. Cache Scope and Cache Expiration Time (seconds) can by specified with argument.

Cache scope can be set to one of the following.

TRANSACTION: Cache is only available in the same transaction.
GLOBAL: Shared cache.
It is possible to specify expiration time (seconds) in GLOBAL. If not specified, expiration time is infinite (However, if the expiration time in settings of CacheStore used by backend is set, it will be the limit). When using GLOBAL, you need to set EQL hint after enabling queryCache in Entity definition.
GLOBAL_KEEP: Shared cache.
It is possible to specify expiration time (seconds) in GLOBAL. If not specified, expiration time is infinite (However, if the expiration time in settings of CacheStore used by backend is set, it will be the limit). When using GLOBAL_KEEP, you need to set EQL hint after enabling queryCache in Entity definition.
The difference between GLOBAL and GLOBAL_KEEP is as follows:
in the case of GLOBAL, the cache is immediately destroyed when the cached Entity data is updated, while in the case of GLOBAL_KEEP, the cache is kept at the value before the update.
GLOBAL_RELOAD: Shared cache that is automatically reloaded periodically.
Reload interval (seconds) must be specified. When using GLOBAL_RELOAD, you need to set EQL hint after enabling queryCache in Entity definition.
In the case of GLOBAL_RELOAD, the cache is automatically reloaded at regular intervals on the back end. If Entity data is updated while cached, the value will not be reflected in the cache until it is reloaded.

It can be specified as below.

Scope：TRANSACTION,

 select /*+ cache(transaction) */ ename from Employee

Scope：GLOBAL、Cache Expiration Time：infinite,

 select /*+ cache */ ename from Employee

Scope：GLOBAL、Cache Expiration Time：10 minutes,

 select /*+ cache(600) */ ename from Employee

Scope：GLOBAL_KEEP、Cache Expiration Time：infinite,

 select /*+ cache(keep) */ ename from Employee

Scope：GLOBAL_KEEP、Cache Expiration Time：10 minutes,

 select /*+ cache(keep, 600) */ ename from Employee

Scope：GLOBAL_RELOAD、Reload Interval：10 minutes,

 select /*+ cache(reload, 600) */ ename from Employee

fetch_size

A hint for specifying fetch size（Statement#setFetchSize(int) in JDBC）when issuing query to DB.

SELECT /*+ fetch_size(100) */ job, ename FROM Employee

index

Explicitly specify to use the Property that specified in argument as an INDEX. The Property specified as INDEX should be set to INDEX, UNIQUE INDEX in Entity definition.

SELECT /*+ index(sal) */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000

It is possible to separate multiple properties by , (comma). (However, it depends on DB to choose which INDEX to use actually.)

SELECT /*+ index(sal, department.loc) */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000
    AND department.loc in('CHICAGO', 'BOSTON')

It depends on DB to choose which Property INDEX to use when neither index hint nor no_index hint is specified.

native

It is possible to specify hint to be assigned to DB native SQL statement (if DB is RDB) that converted from EQL. Specify native hint as a string argument.

SELECT /*+ native('ORDERED USE_NL_WITH_INDEX(...)') */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000
    AND department.loc in('CHICAGO', 'BOSTON')

If you want to specify a hint clause for a table (Such as index hint in MySQL etc.), specify the table name as the first argument.

SELECT /*+ native(q0, 'FORCE_INDEX(...)') */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000
    AND department.loc in('CHICAGO', 'BOSTON')

no_bind

A hint for EQL to specify that binding variable (PrepareStatement in JDBC) not be used when actually issuing a query to the DB.

SELECT /*+ no_bind */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000 ORDER BY ename
    LIMIT 100

For the sake of specifying a specific EQL to execute without binding when issuing EQL if alwaysBind is set to true, according to the setting of RdbAdapterService.

no_index

Exclude Property specified in argument from using as an INDEX alternatives.

SELECT /*+ no_index(sal) */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000

It is possible to specify multiple properties separated by , (comma).

SELECT /*+ index(job, department.loc) */
    job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000
    AND department.loc in('CHICAGO', 'BOSTON')

It depends on DB to choose which Property INDEX to use when neither index hint nor no_index hint is specified.

read_only

If you want to issue the EQL as a read-only transaction, this hint clause can be used.

However, if the resultMode of SearchOption in the search call is ResultMode.STREAM, it will not be issued as a read-only transaction, but will be issued within the calling transaction.

SELECT /*+ read_only */
    job, ename, department.dname FROM Employee

suppress_warnings

The hint as an annotation to suppress the output of the EQL warning log. For instance, when executing EQL, the warning log will complain about the INDEX property in search condition not being used. However, this warning is unnecessary when the amount of Entity data is small. For such conditions, the user can suppress these kind of warnings with this hint item.

SELECT /*+ suppress_warnings */
    job, ename, department.dname FROM Employee

timeout

specify the timeout of a query (Statement#setQueryTimeout(int) in JDBC). It depends on DB exactly how many seconds to disconnect.

SELECT /*+ timeout(60) */
    job, ename, department.dname FROM Employee

And also, it is possible to define hint clause in external property file and load it from that file by specifying key name. The path of property file is defined in QueryService.

Especially when you want to use native hint clause directly, or optimal hint clauses may differ depend on different environment. In this situation, it will be able to manager different hint clauses for each environment.

Property file description example

 hint1 = native(q0t0, 'FORCE INDEX(obj_store__USER_ISTR_index_3)')
 hint2 = native(q0, 'FORCE INDEX(obj_store_ISTR_index_1)')
 :

Specify external hint clause with @hint("key name").
In the environment that above property file is defined.

 select /*+ @hint(hint1) */ a, b, c.x, d.x, d.name from SampleEntity where c.x='hoge' and a=1 and b=15

When above EQL is issued, it will be converted to

select /*+ native(q0t0, 'FORCE INDEX(obj_store__USER_ISTR_index_3)') */ a, b, c.x, d.x, d.name from SampleEntity where c.x='hoge' and a=1 and b=15

It is possible to get hint class instance by Hint.externalHint(String) when using Query class.

Query q = new Query()
        .select("a", "b", "c.x", "d.x", "d.name")
        .hint(Hint.externalHint("hint1"))
        .from("SampleEntity")
        .where(new And().eq("c.x", "hoge").eq("a", 1L));

About REFER Clause

Basically it is not necessary to describe JOIN clause explicitly in EQL like in SQL. You can specify Reference Property with separating by . (dot), that defined in Entity definition in advance.

For example, Employee Entity and Department Entity are defined, with a Reference Property named department in Employee that referencing Department Entity.

SELECT job, ename, department.dname FROM Employee
    WHERE job = 'SALESMAN' AND sal > 1000
    AND department.loc in('CHICAGO', 'BOSTON')

Also, it is possible to specify Property named dname in Department by means of specifying department.dename. The oid defined in each Entity are used as join key in join condition in the above case. Use Refer clause if you want to specify other Property in addition to oid in join condition.

SELECT job, ename, department.dname FROM Employee
    REFER department ON department.deptno < 100
    WHERE job = 'SALESMAN' AND sal > 1000
    AND department.loc in('CHICAGO', 'BOSTON')

In the above case, in addition to oid, department.deptno < 100 is applied as a join condition.

Also, there are some options that could be specified in Refer clause when joining with reference target.

AS OF clause

AS OF is used to specify which version is used to join with Referenced Entity, when Versioning is enabled for the Entity.

AS OF NOW

Get the latest version at the moment.

SELECT job, ename, department.dname FROM Employee
    REFER department AS OF NOW
    WHERE job = 'SALESMAN' AND sal > 1000

AS OF UPDATE TIME

Get version at the time of inserting or updating.

SELECT job, ename, department.dname FROM Employee
    REFER department AS OF UPDATE TIME
    WHERE job = 'SALESMAN' AND sal > 1000

AS OF <value expression>

Specify a value expression that explicitly specifies version. Specify version number for Numberbase/Statebase Versioning.
```
SELECT job, ename, department.dname FROM Employee
    REFER department AS OF 3
    WHERE job = 'SALESMAN' AND sal > 1000
```
For Timebase/SimpleTimebase, specify the date and time to indicate the time. For example, if there is a hire date item named hiredate,
```
SELECT job, ename, department.dname FROM Employee
    REFER department AS OF hiredate
    WHERE job = 'SALESMAN' AND sal > 1000
```
can be specified like above.

About Limit Clause

Specify the number of lines for retrieving search results.

SELECT job, ename, department.dname FROM Employee
    LIMIT 100

Specify OFFSET for the start position. If 0 is specified, it starts from the beginning (same as unspecified).

SELECT job, ename, department.dname FROM Employee
    LIMIT 100 OFFSET 1000

About Subquery

Subquery is possible to be used in the IN condition. It is not supported in FROM clause.

Also, Subquery returns a single value can be used as Scalar subquery. Scalar subquery could be used as such as SELECT item, comparison item in search condition etc. because it is defined as ValueExpression. Please refer to the description in "Scalar subquery".

2.2. Value Expression

An element that represents a single value. Specifically, literal value, Entity Property, function, operation results etc. Classes consist of Value Expression are under the package org.iplass.mtp.entity.query.value.

EQL Expression Example

-123

name

(10 + sals) * 0.2

YEAR(dateTimeValue)

Query Class Usage Example

Literal val = new Literal(-123);

EntityField val = new EntityField("name");

//(10 + sals) * 0.2
ValueExpression val = new Term(
        new ParenValue(
                new Polynomial(new Literal(10)).add(new EntityField("sals"))))
        .mul(new Literal(0.2));

Function val = new Function("YEAR", new EntityField("dateTimeValue"));

Syntax

<value expression> ::=: <polynomial> | <term> | <minus sign> | <paren value>
<polynomial> ::=: <value expression> {"+" | - <value expression>}*
<term> ::=: <value expression> {"*" | / <value expression>}*
<minus sign> ::=: - <paren value>
<paren value> ::=: <primary value> | ( <value expression> )
<primary value> ::=: <aggregate> | <array value> | <case> | <entity field> | <function> | <cast> | <literal> | <scalar subquery> | <window function>
"<aggregate>" ::=: {AVG | MAX | MEDIAN | MIN | MODE | STDDEV_POP | STDDEV_SAMP | SUM | VAR_POP | VAR_SAMP ( <value expression> )} | {COUNT( [DISTINCT] [<value expression>] )} | {LISTAGG( [DISTINCT] <value expression> [,<string literal>]) [<within group spec>]}
<within group spec> ::=: WITHIN GROUP( ORDER BY <sort spec> {,<sort spec>}*)
<array value> ::=: "ARRAY[" <value expression> {,<value expression>}* "]"
"<case>" ::=: CASE WHEN "<condition>" THEN <value expression> {WHEN "<condition>" THEN <value expression>}* [ELSE <value expression>] END
"<entity field>" ::=: <property name> | <reference>.<property name> | <correlated entity field>
<reference> ::=: <reference name>{.<reference name>}*
<correlated entity field> ::=: .+{THIS | <entity field> | <reference>}
※correlated entity field could be used only in ON clause of subquery.
"<function>" ::=: {<function name>()} | {<function name>( <value expression> {,<value expression>}* )}
<function name> ::=: REPLACE | UPPER | LOWER | CONCAT | SUBSTR | INSTR | CHAR_LENGTH | MOD | SQRT | POWER | ABS | CEIL | FLOOR | ROUND | TRUNCATE | SIN | COS | TAN | ASIN | ACOS | ATAN | ATAN2 | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | DATE_ADD | DATE_DIFF | CURRENT_DATE | CURRENT_TIME | CURRENT_DATETIME | LOCALTIME
"<cast>" ::=: CAST( <value expression> AS <data type> )
<data type> ::=: BOOLEAN | STRING | INTEGER | FLOAT | DECIMAL | DATETIME | DATE | TIME | SELECT
"<scalar subquery>" ::=: "<subquery>"
"<window function>" ::=: <window function type> OVER( [<window partition by clause>] [<window order by clause>] )
<window function type> ::=: {ROW_NUMBER | RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST ()} | <aggregate>
<window partition by clause> ::=: PARTITION BY <value expression> {,<value expression>}*
<window order by clause> ::=: ORDER BY <sort spec> {,<sort spec>}*
"<literal>" ::=: <boolean literal> | <string literal> | <integer literal> | <float literal> | <decimal literal> | <datetime literal> | <date literal> | <time literal> | <select value literal> | NULL
<boolean literal> ::=: TRUE | FALSE
<string literal> ::=: '""string""'
<integer literal> ::=: ""integer value""[i|I]
<float literal> ::=: ""value of floating-point number""[f|F]
<decimal literal> ::=: ""value of fixed-point number""{g|G}
<datetime literal> ::=: '""datetime format as yyyy-MM-dd HH:mm:ss.SSS""'{m|M}
<date literal> ::=: '""date format as yyyy-MM-dd""'{d|D}
<time literal> ::=: '""time format as HH:mm:ss""'{t|T}
<select value literal> ::=: '""value of SelectValue""'{s|S}
<entity name> ::=: ""definition name of Entity""
<property name> ::=: ""definition name of Entity Property""
<reference name> ::=: ""definition name of Entity Reference Property""

Entity Field

<entity field> is an element that represents the property value defined in the Entity. Specifies the property name of the Entity definition.

name

In addition, you can obtain and specify the value of the property of the referenced entity by specifying it in the format [reference property name].[property name of the referenced entity] separated by dots.

groups.name

LongText type and Binary type properties can only be specified as <select clause> items. If used as a condition in <where clause> or <having clause>, or if an operation or function is applied, the behavior is undefined.

When using correlated subqueries, It is possible to specify the join condition with the outer query in the <correlated entity field> format. When specifying the <entity field> of the outer subquery, add a .(dot) to the beginning. <correlated entity field> is only available in ON clause of subquery. See "Scalar Subquery" for details.

When generating by using classes represent EQL, use org.iplass.mtp.entity.query.value.primary.EntityField.

EntityField prop = new EntityField("intProp");

EntityField referenceEntityProp = new EntityField("refProp.strProp");

EntityField correlateProp = new EntityField(".this");

Literal

<literal> is a element that represents literal value. Literal value can be used in EQL as follows.

Type Java type Description

Type	Java type	Description
boolean	java.lang.Boolean	Represents bool value. true or false in EQL expression. Also, it is case-insensitive. EQL Expression Example `true` `FALSE`
string	java.lang.String	Represents string. String is surrounded with ' (single quota) in EQL expression. If you want to use ' in string, escape by using two single quotas as ''. EQL Expression Example `'abc test string'` `'I''m a cat.'`
integer	java.lang.Long	Represents value of integer number. Treated as Long value in java. It is possible to explicitly indicate integer number by specifying I or i as a suffix of value expression in EQL expression. EQL Expression Example `-54` `1234I` `10i`
float	java.lang.Double	Represents value of floating-point number. Treated as Double value in java. It is possible to explicitly indicate floating-point number by specifying F or f as a suffix of value expression in EQL expression. Also supports exponential expression. EQL Expression Example `10.5` `1234.123F` `0.12f` `1.5e-5`
decimal	java.math.BigDecimal	Represents value of fixed-point number. Specify G or g as a suffix of value expression in EQL expression. EQL Expression Example `1234.123G` `10001g`
datetime	java.sql.Timestamp	Represents date and time with millisecond precision. Specify a string in the format 'yyyy-MM-dd HH:mm:ss.SSS' or 'yyyy-MM-dd HH:mm:ss.SSSXXX' if timezone is specified, and M or m as a suffix in EQL expression. If there is no timezone specified, timezone that set in Tenant will be determined. EQL Expression Example `'2011-11-15 16:03:01.000'M` `'2010-01-30 01:25:01.200'm` `'2010-01-30 01:25:01.200+09:00'M`
date	java.sql.Date	Represents date. Specify a string in the format 'yyyy-MM-dd' and D or d as a suffix in EQL expression. EQL Expression Example `'2012-12-11'D` `'2011-11-15'd`
time	java.sql.Time	Represents time. Specify a string in the format 'HH:mm:ss' and T or t as a suffix in EQL expression. EQL Expression Example `'03:01:00'T` `'18:24:15't`
select	org.iplass.mtp. entity.SelectValue	Represents SelectValue. Specify value of SelectValue as a string and S or s as a suffix in EQL expression. EQL Expression Example `'A01'S` `'1's`
null	null	Represents null value. Represents null in EQL expression. Also, it is case-insensitive. Depending on backend DB, there may be no distinction between null value and ''(empty string). EQL Expression Example `null` `NULL`

boolean

java.lang.Boolean

Represents bool value. true or false in EQL expression. Also, it is case-insensitive.

EQL Expression Example

true

FALSE

string

java.lang.String

Represents string. String is surrounded with ' (single quota) in EQL expression. If you want to use ' in string, escape by using two single quotas as ''.

EQL Expression Example

'abc test string'

'I''m a cat.'

integer

java.lang.Long

Represents value of integer number. Treated as Long value in java. It is possible to explicitly indicate integer number by specifying I or i as a suffix of value expression in EQL expression.

EQL Expression Example

-54

1234I

10i

float

java.lang.Double

Represents value of floating-point number. Treated as Double value in java. It is possible to explicitly indicate floating-point number by specifying F or f as a suffix of value expression in EQL expression. Also supports exponential expression.

EQL Expression Example

10.5

1234.123F

0.12f

1.5e-5

decimal

java.math.BigDecimal

Represents value of fixed-point number. Specify G or g as a suffix of value expression in EQL expression.

EQL Expression Example

1234.123G

10001g

datetime

java.sql.Timestamp

Represents date and time with millisecond precision. Specify a string in the format 'yyyy-MM-dd HH:mm:ss.SSS' or 'yyyy-MM-dd HH:mm:ss.SSSXXX' if timezone is specified, and M or m as a suffix in EQL expression. If there is no timezone specified, timezone that set in Tenant will be determined.

EQL Expression Example

'2011-11-15 16:03:01.000'M

'2010-01-30 01:25:01.200'm

'2010-01-30 01:25:01.200+09:00'M

date

java.sql.Date

Represents date. Specify a string in the format 'yyyy-MM-dd' and D or d as a suffix in EQL expression.

EQL Expression Example

'2012-12-11'D

'2011-11-15'd

time

java.sql.Time

Represents time. Specify a string in the format 'HH:mm:ss' and T or t as a suffix in EQL expression.

EQL Expression Example

'03:01:00'T

'18:24:15't

select

org.iplass.mtp.
entity.SelectValue

Represents SelectValue. Specify value of SelectValue as a string and S or s as a suffix in EQL expression.

EQL Expression Example

'A01'S

'1's

null

Represents null value. Represents null in EQL expression. Also, it is case-insensitive. Depending on backend DB, there may be no distinction between null value and ''(empty string).

EQL Expression Example

null

NULL

Function

<function> is a element that represents function targeting a single value (a single line).

When generating by using classes represent EQL, use org.iplass.mtp.entity.query.value.primary.Function.

Function val = new Function("YEAR", new EntityField("dateTimeValue"));(1)

//SELECT name, YEAR(dateTimeValue), DATE_ADD(dateTimeValue, 3, 'DAY')
//    FROM someEntity WHERE ABS(someNumProp) > 3
Query q = new Query()
        .select(
                new EntityField("name"),
                new Function("YEAR", new EntityField("dateTimeValue")),(1)
                new Function("DATE_ADD",
                        new EntityField("dateTimeValue"),
                        new Literal(3L),  new Literal("DAY")))(1)
        .from("someEntity")
        .where(new Greater(new Function("ABS",
                new EntityField("someNumProp")), new Literal(3L)));(1)

1	Specify function name with the first argument, arguments after the first one with the arguments of the function.

Description of functions and arguments can be represent in EQL are as follows.

Function Type of return value Description

REPLACE

string

REPLACE(target, from, to)

Replace string represented by from with the string represented by to within the string of target.

EQL Expression Example

REPLACE(strProperty, '-', '_')

UPPER

string

UPPER(target)

Convert string represented by target to upper case.

EQL Expression Example

UPPER(strProperty)

LOWER

string

LOWER(target)

Convert string represented by target to lower case.

EQL Expression Example

LOWER(strProperty)

CONCAT

string

CONCAT(target1, target2)

Concatenate string represented by target with the one represented by target2.

EQL Expression Example

CONCAT('abc', strProp)

SUBSTR

string

SUBSTR(target, beginIndex, length)
SUBSTR(target, beginIndex)

Returns a string that is a substring of the string represented by target. The beginIndex is 1 if you want to start from the beginning.
If length is omitted, it ends at the end of target.
Also, beginIndex could be negative number. If a negative number is specified, the index from the end is specified.

EQL Expression Example

SUBSTR(strProp, 3, 3)

SUBSTR(strProp, 3)

SUBSTR(strProp, -3)(1)

1	The example of returning 3 characters from the end.

INSTR

integer

INSTR(target, matchStr)

Returns the index within the string represented by target of the first occurrence of the string represented by matchStr. Return value is 1 if matches from the beginning.

EQL Expression Example

INSTR('abcdef', 'cd')

CHAR_LENGTH

integer

CHAR_LENGTH(target)

Returns the length of the string represented by target.

EQL Expression Example

CHAR_LENGTH(strProp)

If the property type is LongText, the correct length may not be returned.

Function Return type Description

MOD

integer / float / decimal

MOD(num1, num2)

Computes the remainder (the remainder of dividing num1 by num2). Return type depends on the type of number arguments.

EQL Expression Example

MOD(numProp, 3)

SQRT

float

SQRT(num)

Computes the square root。

EQL Expression Example

SQRT(numProp)

POWER

integer / float / decimal

POWER(base, exp)

Computes the power (exponentiation). Return type depends on the type of number arguments.

EQL Expression Example

POWER(numProp, 2)

ABS

integer / float / decimal

ABS(num)

Computes the absolute value. Return type depends on the type of number arguments.

EQL Expression Example

ABS(numProp)

CEIL

integer

CEIL(num)

Rounds up to the nearest integer.

EQL Expression Example

CEIL(12.4)

FLOOR

integer

FLOOR(num)

Rounds down the nearest integer.
Be careful if num is negative. Different from TRUNCATE(), return the largest integer less than num. When FLOOR(-13.5), -14 is returned.

EQL Expression Example

FLOOR(12.4)

ROUND

integer / decimal

ROUND(num, decimalPlace)
ROUND(num)

Rounds off num to the decimal places specified with decimalPlace.(However, if num is a float type, banker’s rounding is performed).
decimalPlace can be a negative number. For example, if -1 is specified, it will be rounded off to the nearest 10.
If decimalPlace is omitted, it is rounded off as an integer. The return type differs depending on the value specified with decimalPlace. If decimalPlace is 0 or less, it is returned as integer type. Otherwise it will be returned as decimal type.

EQL Expression Example

ROUND(numProp, 3)

ROUND(25.123)

ROUND(125, -1)(1)

1	Rounded off to the nearest 10, 130 is returned.

TRUNCATE

integer / decimal

TRUNCATE(num, decimalPlace)
TRUNCATE(num)

Rounds down num to the decimal places specified with decimalPlace. decimalPlace can be a negative number. For example, if -1 is specified, it will be rounded down to the nearest 10.
If decimalPlace is omitted, it is rounded as an integer. The return type differs depending on the value specified with decimalPlace. If decimalPlace is 0 or less, it is returned as integer type. Otherwise it will be returned as decimal type. Unlike FLOOR(), num is rounded down toward 0. When TRUNCATE (-13.5) is computed, -13 is returned.

EQL Expression Example

TRUNCATE(numProp, 3)

TRUNCATE(25.123)

TRUNCATE(125, -1)(1)

1	Truncated to the nearest 10, 120 is returned.

SIN

float

SIN(num)

Computes the sine.

EQL Expression Example

SIN(numProp)

COS

float

COS(num)

Computes the cosine.

EQL Expression Example

COS(numProp)

TAN

float

TAN(num)

Computes the tangent.

EQL Expression Example

TAN(numProp)

ASIN

float

ASIN(num)

Computes the arcsine.

EQL Expression Example

ASIN(numProp)

ACOS

float

ACOS(num)

Computes the arccosine.

EQL Expression Example

ACOS(numProp)

ATAN

float

ATAN(num)

Computes the arctangent.

EQL Expression Example

ATAN(numProp)

ATAN2

float

ATAN2(num1, num2)

Computes the arctangent of num1 / num2.

EQL Expression Example

ATAN2(num1Prop, num2Prop)

Function Return type Description

YEAR

integer

YEAR(datetime)

Gets the year of the date type or datetime type specified with datatime.

EQL Expression Example

YEAR(dateProp)

MONTH

integer

MONTH(datetime)

Gets the month (1~12) of the date type or datetime type specified with datetime.

EQL Expression Example

MONTH('2013-12-29'D)

DAY

integer

DAY(datetime)

Gets the day (1~31) of the date type or datetime type specified with datetime.

EQL Expression Example

DAY('1975-11-15 16:03:01.000'M)

HOUR

integer

HOUR(datetime)

Gets the hour (0~23) of time type, date type or datetime type specified with datetime.

EQL Expression Example

HOUR(timeProp)

MINUTE

integer

MINUTE(datetime)

Gets the minute (0~59) of time type, date type or datetime type specified with datetime.

EQL Expression Example

MINUTE('23:41:00'T)

SECOND

integer

SECOND(datetime)

Gets the second (0~59) of time type, date type or datetime type specified with datetime.

EQL Expression Example

SECOND('1975-11-15 16:03:01.000'M)

DATE_ADD

datetime

DATE_ADD(datetime, addVal, unit)

Adds the value specified with addVal to data type, datetime type, time type specified with datetime. The unit to add is specified by unit. unit can be 'YEAR','MONTH','DAY','HOUR','MINUTE','SECOND'.

EQL Expression Example

DATE_ADD(dateProp, 3, 'DAY')

DATE_DIFF

integer

DATE_DIFF(unit, datetime1, datetime2) Gets the difference between datetime1 and datetime2 in units of unit. If datetime1 is larger, the result is a negative value. unit can be 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND'.

EQL Expression Example

DATE_DIFF('YEAR', '2012-01-01'D, dateProp)

CURRENT_DATE

date

CURRENT_DATE()

Gets current date (year / month / day).

EQL Expression Example

CURRENT_DATE()

CURRENT_TIME

time

CURRENT_TIME()

Gets the current time.

EQL Expression Example

CURRENT_TIME()

CURRENT_DATETIME

datetime

CURRENT_DATETIME()

Gets the current date and time.

EQL Expression Example

CURRENT_DATETIME()

LOCALTIME

datetime

LOCALTIME(datetime)

Converts datetime of the argument to the datetime specified in locale time set in Tenant.

EQL Expression Example

LOCALTIME(CURRENT_DATETIME())

For SQL Server, this is supported since version 2016.

Cast

<cast> is a function that casts a value.

CAST( value AS dataType )

Convert the value of value to the type specified with dateType. <literal> value could be converted to are listed as follows.

BOOLEAN
STRING
INTEGER
FLOAT
DECIMAL
DATETIME
DATE
TIME
SELECT

EQL Expression Example

CAST(10.5 AS STRING)

When generating by using classes represent EQL, use org.iplass.mtp.entity.query.value.primary.Cast.

Cast castVal = new Cast(new EntityField("intProp"), PropertyDefinitionType.STRING);

Aggregate Function

<aggregate> is a element that represents aggregate function to aggregate multiple rows. Aggregate functions can also control aggregation units by using <group by clause> in query.

When generating by using classes represent EQL, use classes under the package org.iplass.mtp.entity.query.value.aggregate.

Avg avg = new Avg("propValue");

//SELECT category, COUNT(), AVG(amount) FROM someEntity GROUP BY category
Query q = new Query()
        .select(
                "category",
                new Count(),
                new Avg("amount"))
        .from("someEntity")
        .groupBy("category");

Description of aggregation functions can be represented and those arguments in EQL are as follows.

Function Return type Description

AVG

float

AVG(num)

Calculates the average by aggregating the numbers represented with num.

EQL Expression Example

AVG(numProperty)

COUNT

integer

COUNT(target)
COUNT(DISTINCT target)
COUNT()

Counts the number of the rows where value of target is not null.
When distinct is specified before target, count without duplicated rows.
Also, if target is not specified, count the number of the rows including null values.

EQL Expression Example

COUNT(someProp)

COUNT(DISTINCT someProp)

COUNT()

LISTAGG

string

LISTAGG(target)
LISTAGG(target, 'separatorChar')
LISTAGG(DISTINCT target)
LISTAGG(DISTINCT target, 'separatorChar')
LISTAGG(DISTINCT target, 'separatorChar') WITHIN GROUP(ORDER BY sortSpec)

Concatenate the target values as a string.
When distinct is specified before target, it will be concatenated without duplicates.
If separatorChar is specified, the string will be concatenated with its separator. If not specified, the string is concatenated with , .
The order of concatenation can be specified with the WITHIN GROUP clause.

The DISTINCT specification and the WITHIN GROUP clause can be omitted in Oracle 19c and later.
In SQL Server, it is available in SQL Server 2017 or later. However, DISTINCT is not supported. Also, there is a restriction on specifying with the WITHIN GROUP clause.

EQL Expression Example

LISTAGG(someProp)

LISTAGG(someProp, ':')

LISTAGG(DISTINCT someProp)

LISTAGG(someProp) WITHIN GROUP(ORDER BY someProp)

LISTAGG(someProp, ':') WITHIN GROUP(ORDER BY sortProp DESC NULLS LAST)

MAX

integer / float / decimal

MAX(num)

Aggregates values specified with num and get the maximum one. Return type depends on the type of number argument.

EQL Expression Example

MAX(numProperty)

MEDIAN

integer / float / decimal

MEDIAN(num)

Aggregates values specified with num and get the median. Return type depends on the type of number argument.

This is not supported by MYSQL.

EQL Expression Example

MEDIAN(numProperty)

MIN

integer / float / decimal

MIN(num)

Aggregates values specified with num and get the minimum one. Return type depends on the type of number argument.

EQL Expression Example

MIN(numProperty)

MODE

integer / float / decimal

MODE(num)

Aggregates values specified with num and get the mode. Return type depends on the type of number argument.

This is not supported by MYSQL.

EQL Expression Example

MODE(numProperty)

STDDEV_POP

float

STDDEV_POP(num)

Aggregates values specified with num and calculates the population standard deviation.

EQL Expression Example

STDDEV_POP(numProperty)

STDDEV_SAMP

float

STDDEV_SAMP(num)

Aggregates values specified with num and calculates the sample standard deviation.

EQL Expression Example

STDDEV_SAMP(numProperty)

SUM

integer / float / decimal

SUM(num)

Aggregates values specified with num and gets the sum of values. Return type depends on the type of number argument.

EQL Expression Example

SUM(numProperty)

VAR_POP

float

VAR_POP(num)

Aggregates values specified with num and calculates the population variance.

EQL Expression Example

VAR_POP(numProperty)

VAR_SAMP

float

VAR_SAMP(num)

Aggregates values specified with num and calculates the sample variance.

EQL Expression Example

VAR_SAMP(numProperty)

Window Function

<window function> is an element that represents a function that can apply an aggregate function to the fields that cut from the result set, similar to the Window function in SQL.

When specifying the aggregation range and order, use the OVER clause (PARTITION BY and ORDER BY).

Here are Entity data as follows,

empNo	dept	amount
1	A	10
2	A	20
3	B	50
4	B	10
5	C	30

The following EQL example outputs the sum of amount in units of deptNo for each row.

SELECT dept, SUM(amount) OVER(PARTITION BY dept) FROM sample

Output are as follows.

dept	SUM(amount) OVER( … )
A	30
A	30
B	60
B	60
C	30

The following EQL sample output the sum of amount in units of deptNo in order of empNo for each row.

SELECT dept, empNo, SUM(amount) OVER(PARTITION BY dept ORDER BY empNo) FROM sample

Output are as follows.

dept	empNo	SUM(amount) OVER( … )
A	1	10
A	2	30
B	3	50
B	4	60
C	5	30

Window function is not supported natively before MySQL5.7. If you want to use window function in the DB before MySQL5.7, it is possible by using [.eeonly]#Function Emulation. However, with the emulated function, all search results are temporarily expanded in memory, so you should be careful with it. Settings of StoreService is required if you want to enable the Windows function emulation.

When generating by using classes represent EQL, use classes under the package org.iplass.mtp.entity.query.value.window.

WindowAggregate cumulativeSum = new WindowAggregate(new Sum("amount"))
        .partitionBy("category")
        .orderBy(new WindowSortSpec("month", SortType.ASC));

//SELECT category, month,
//    SUM(amount) OVER(PARTITION BY category ORDER BY month ASC),
//    RANK() OVER(ORDER BY amount DESC) FROM someEntity
Query q = new Query().select(
        "category",
        "month",
        new WindowAggregate(new Sum("amount")).partitionBy("category")
                .orderBy(new WindowSortSpec("month", SortType.ASC)),
        new Rank().orderBy(new WindowSortSpec("amount", SortType.DESC)))
        .from("someEntity");

Description of Window functions that can be represent in EQL and those arguments are as follows.

Function Return type Description

Function	Return type	Description
AVG	float	AVG(num) OVER( … ) Calculates the average of the values specified with num. PARTITION BY and ORDER BY can be specified optionally in OVER (…). When ORDER BY is specified, the cumulative average (moving average) is calculated. EQL Expression Example `AVG(numProperty) OVER(ORDER BY someProp)`
COUNT	integer	COUNT(target) OVER( … ) COUNT() OVER( … ) Counts the number of rows where the value of target is not null. If target is not specified, count the number of rows including null value. PARTITION BY and ORDER BY can be specified optionally in OVER (…). If ORDER BY is specified, the cumulative frequency is calculated. EQL Expression Example `COUNT(someProp) OVER(PARTITION BY dept ORDER BY month)` `COUNT() OVER(PARTITION BY dept,year)`
MAX	integer / float / decimal	MAX(num) OVER( … ) Gets the maximum value of the values represents by num. Return type depends on the argument type. PARTITION BY and ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative maximum is calculated. EQL Expression Example `MAX(numProperty) OVER(PARTITION BY dept)`
MEDIAN	integer / float / decimal	MEDIAN(num) OVER( … ) Get the median of values represented by num. PARTITION BY can be specified optionally in OVER ( … ). Return type depends on the type of argument value. EQL Expression Example `MEDIAN(numProperty) OVER(PARTITION BY dept)`
MIN	integer / float / decimal	MIN(num) OVER( … ) Gets the minimum value of the values specified with num. Return type depends on the type of argument value. PARTITION BY, ORDER BY can be specified optionally in OVER (…). If ORDER BY is specified, the cumulative minimum is calculated. EQL Expression Example `MIN(numProperty) OVER(ORDER BY year)`
STDDEV_POP	float	STDDEV_POP(num) OVER( … ) Aggregates values specified with num and calculates the population standard deviation. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative population standard deviation is calculated. EQL Expression Example `STDDEV_POP(numProperty) OVER(PARTITION BY year)`
STDDEV_SAMP	float	STDDEV_SAMP(num) OVER( … ) Aggregates values specified with num and calculates the sample standard deviation. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative sample standard deviation is calculated. EQL Expression Example `STDDEV_SAMP(numProperty)`
SUM	integer / float / decimal	SUM(num) OVER( … ) Aggregates values specified with num and calculates the sum of values. Return type depends on the type of argument value. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, cumulative value is calculated. EQL Expression Example `SUM(numProperty) OVER(PARTITION BY year ORDER BY month)`
VAR_POP	float	VAR_POP(num) OVER( … ) Aggregates values specified with num and calculates the population variance. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative population variance is calculated. EQL Expression Example `VAR_POP(numProperty) OVER(PARTITION BY dept)`
VAR_SAMP	float	VAR_SAMP(num) OVER( … ) Aggregates values specified with num and calculates the sample variance. PARTITION BY, ORDER BY can be specified optionally in ORDER ( … ). If ORDER BY is specified, the cumulative sample variance is calculated. EQL Expression Example `VAR_SAMP(numProperty) OVER(PARTITION BY dept)`
RANK	integer	RANK() OVER( … ) Assigns rank values according to values specified by ORDER BY. If there are two rows with the same rank value, rank values are not consecutive. For example, there are two rows with rank value 1, the next rank value will be 3. ORDER BY must be specified in OVER ( … ). PARTITION BY is optional. EQL Expression Example `RANK() OVER(ORDER BY numProp)`
DENSE_RANK	integer	DENSE_RANK() OVER( … ) Assigns rank values according to values specified by ORDER BY. If there are two rows with the same rank value, whether rank values are consecutive or not depends on RANK() function. For example, there are two rows with rank value 1, the next rank value will be 2. ORDER BY must be specified in ORDER ( … ). PARTITION BY is optional. EQL Expression Example `DENSE_RANK() OVER(ORDER BY numProp)`
PERCENT_RANK	float	PERCENT_RANK() OVER( … ) Assigns percent rank values according to values specified by ORDER BY. The return value of PERCENT_RANK is range from 0 to 1. The first line must be 0. ORDER BY must be specified in OVER ( … ). PARTITION BY is optional. EQL Expression Example `PERCENT_RANK() OVER(ORDER BY numProp)`
CUME_DIST	float	CUME_DIST() OVER( … ) Gets the cumulative distribution according to values specified by ORDER BY. The return value of CUME_DIST is range from 0 to 1. However, 0 is not included. ORDER BY is must be specified in OVER ( … ). PARTITION BY is optional. EQL Expression Example `CUME_DIST() OVER(ORDER BY numProp)`
ROW_NUMBER	integer	ROW_NUMBER() OVER( … ) Gets line numbers according to values specified by ORDER BY. The value of the first line is 1. ORDER BY must be specified in OVER ( … ). PARTITION BY is optional. EQL Expression Example `ROW_NUMBER() OVER(ORDER BY dateProp)`

AVG

float

AVG(num) OVER( … )

Calculates the average of the values specified with num.
PARTITION BY and ORDER BY can be specified optionally in OVER (…). When ORDER BY is specified, the cumulative average (moving average) is calculated.

EQL Expression Example

AVG(numProperty) OVER(ORDER BY someProp)

COUNT

integer

COUNT(target) OVER( … )
COUNT() OVER( … )

Counts the number of rows where the value of target is not null.
If target is not specified, count the number of rows including null value. PARTITION BY and ORDER BY can be specified optionally in OVER (…). If ORDER BY is specified, the cumulative frequency is calculated.

EQL Expression Example

COUNT(someProp) OVER(PARTITION BY dept ORDER BY month)

COUNT() OVER(PARTITION BY dept,year)

MAX

integer / float / decimal

MAX(num) OVER( … )

Gets the maximum value of the values represents by num. Return type depends on the argument type. PARTITION BY and ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative maximum is calculated.

EQL Expression Example

MAX(numProperty) OVER(PARTITION BY dept)

MEDIAN

integer / float / decimal

MEDIAN(num) OVER( … )

Get the median of values represented by num. PARTITION BY can be specified optionally in OVER ( … ). Return type depends on the type of argument value.

EQL Expression Example

MEDIAN(numProperty) OVER(PARTITION BY dept)

MIN

integer / float / decimal

MIN(num) OVER( … )

Gets the minimum value of the values specified with num. Return type depends on the type of argument value. PARTITION BY, ORDER BY can be specified optionally in OVER (…). If ORDER BY is specified, the cumulative minimum is calculated.

EQL Expression Example

MIN(numProperty) OVER(ORDER BY year)

STDDEV_POP

float

STDDEV_POP(num) OVER( … )

Aggregates values specified with num and calculates the population standard deviation. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative population standard deviation is calculated.

EQL Expression Example

STDDEV_POP(numProperty) OVER(PARTITION BY year)

STDDEV_SAMP

float

STDDEV_SAMP(num) OVER( … )

Aggregates values specified with num and calculates the sample standard deviation. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative sample standard deviation is calculated.

EQL Expression Example

STDDEV_SAMP(numProperty)

SUM

integer / float / decimal

SUM(num) OVER( … )

Aggregates values specified with num and calculates the sum of values. Return type depends on the type of argument value. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, cumulative value is calculated.

EQL Expression Example

SUM(numProperty) OVER(PARTITION BY year ORDER BY month)

VAR_POP

float

VAR_POP(num) OVER( … )

Aggregates values specified with num and calculates the population variance. PARTITION BY, ORDER BY can be specified optionally in OVER ( … ). If ORDER BY is specified, the cumulative population variance is calculated.

EQL Expression Example

VAR_POP(numProperty) OVER(PARTITION BY dept)

VAR_SAMP

float

VAR_SAMP(num) OVER( … )

Aggregates values specified with num and calculates the sample variance. PARTITION BY, ORDER BY can be specified optionally in ORDER ( … ). If ORDER BY is specified, the cumulative sample variance is calculated.

EQL Expression Example

VAR_SAMP(numProperty) OVER(PARTITION BY dept)

RANK

integer

RANK() OVER( … )

Assigns rank values according to values specified by ORDER BY. If there are two rows with the same rank value, rank values are not consecutive. For example, there are two rows with rank value 1, the next rank value will be 3. ORDER BY must be specified in OVER ( … ). PARTITION BY is optional.

EQL Expression Example

RANK() OVER(ORDER BY numProp)

DENSE_RANK

integer

DENSE_RANK() OVER( … )

Assigns rank values according to values specified by ORDER BY. If there are two rows with the same rank value, whether rank values are consecutive or not depends on RANK() function. For example, there are two rows with rank value 1, the next rank value will be 2. ORDER BY must be specified in ORDER ( … ). PARTITION BY is optional.

EQL Expression Example

DENSE_RANK() OVER(ORDER BY numProp)

PERCENT_RANK

float

PERCENT_RANK() OVER( … )

Assigns percent rank values according to values specified by ORDER BY. The return value of PERCENT_RANK is range from 0 to 1. The first line must be 0. ORDER BY must be specified in OVER ( … ). PARTITION BY is optional.

EQL Expression Example

PERCENT_RANK() OVER(ORDER BY numProp)

CUME_DIST

float

CUME_DIST() OVER( … )

Gets the cumulative distribution according to values specified by ORDER BY. The return value of CUME_DIST is range from 0 to 1. However, 0 is not included. ORDER BY is must be specified in OVER ( … ). PARTITION BY is optional.

EQL Expression Example

CUME_DIST() OVER(ORDER BY numProp)

ROW_NUMBER

integer

ROW_NUMBER() OVER( … )

Gets line numbers according to values specified by ORDER BY. The value of the first line is 1. ORDER BY must be specified in OVER ( … ). PARTITION BY is optional.

EQL Expression Example

ROW_NUMBER() OVER(ORDER BY dateProp)

CASE Expression

<case> is a ValueExpression that can describe conditional branch.

CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2 …
ELSE valueDefault END

If condition1 matches, return value1. If condition2 matches, return value2. If all conditions do not match, return valueDefault.

EQL Expression Example

CASE WHEN prop=1 THEN 'ONE' WHEN prop=2 THEN 'TWO' ELSE 'THREE OR MORE' END

CASE WHEN prop IS NULL THEN 0 ELSE prop END

Generate by using classes represent EQL. Use classes under the package org.iplass.mtp.entity.query.value.controlflow.

Case caseStatement = new Case().when(new Equals("prop", 1L), new Literal("ONE"))
        .when(new Equals("prop", 2L), new Literal("TWO"))
        .elseClause(new Literal("THREE OR MORE"));

Scalar Subquery

<scalar subquery> is a subquery that returns a single value. ScalarSubQuery can be used as ValueExpression, conditional expression, SELECT item. Also, ScalarSubQuery can be used with ON clause as correlated subquery. Correlated subquery executes for each row of outer Query with join condition in ON clause.

( SELECT value FROM entityName WHERE condition … ON .outerJoinItem =innerJoinItem )

ScalarSubQuery is recognized by surrounding Query with ( ). When executing correlated subquery, assign .(dot) to the start of the outer join item in ON clause. It is possible to specify Reference Property in ON clause. Also, "THIS" can be used to represent a reference to itself.

EQL Expression Example

SELECT propA, (SELECT MAX(propX) FROM SomeEntity) FROM SomeEntity

SELECT propA, (SELECT SUM(propX) FROM CorrelatedEntity
    ON .refToCorrelatedEntity.oid=oid)(1)
    FROM SomeEntity

1	In this example, the join condition is the Reference Property refToCorrelatedEntity defined in SomeEntity in outer Query and the oid of CorrelatedEntity itself in the inner Query.

SELECT propA, (SELECT SUM(propX) FROM CorrelatedEntity
    ON .refToCorrelatedEntity=THIS)(1)
    FROM SomeEntity

1	Joining by Reference Property. In this example, the join condition is refToCorrelatedEntity defined in SomeEntity in outer Query and the oid of CorrelatedEntity itself in the inner Query.

When generating by using classes represent EQL, use classes under the package org.iplass.mtp.entity.query.value.subquery.

Query q = new Query().select("propA", new ScalarSubQuery(
        new Query().select(new Sum("propX")).from("CorrelatedEntity"),
                new On(".refToCorrelatedEntity.oid", "oid")))
        .from("SomeEntity");

2.3. Condition

Elements represent condition statement. Specifically, elements that represent =(equal) operator, IN clause, and conditions like AND, OR, NOT represent combination of them. Use classes under the package org.iplass.mtp.entity.query.condition to construct Condition.

EQL Expression Example

propA=123

name LIKE 'abc%' CI

propX > 30 AND (propY IN ('1', '2', '3') OR propY IS NULL)

Query Class Usage Example

Equals eq = new Equals("propA", 123);

Like like = new Like("name", "abc", MatchPattern.PREFIX, CaseType.CI);

//propX > 30 AND (propY IN ('1', '2', '3') OR propY IS NULL)
Condition condition = new And().gt("propX", 30).and(
        new Paren(new Or().in("propY", "1", "2", "3").isNull("propY")));

Syntax

<condition> ::=: <and> | <or> | <not> | <paren>
<and> ::=: <condition> {AND <condition>}*
<or> ::=: <condition> {OR <condition>}*
<not> ::=: NOT <paren>
<paren> ::=: <predicate> | ( <condition> )
<predicate> ::=: <comparison predicate> | <between> | <contains> | <in> | <is not null> | <is null> | <like>
<comparison predicate> ::=: "<value expression>" <comparison operator> "<value expression>"
<comparison operator> ::=: = | > | >= | < | ⇐ | !=
<between> ::=: "<value expression>" BETWEEN "<value expression>" AND "<value expression>"
"<contains>" ::=: CONTAINS('""full text search search condition expression""')
"<in>" ::=: <simple in> | <row value list in> | <subquery in>
<simple in> ::=: "<value expression>" IN ( "<value expression>" {,"<value expression>"}* )
<row value list in> ::=: ( "<value expression>" {,"<value expression>"}\* ) IN ( <row value list> {,<row value list>}* )
<row value list> ::=: ( "<value expression>" {,"<value expression>"}* )
<subquery in> ::=: ("<value expression>" {,"<value expression>"}* ) IN "<subquery>"
<is not null> ::=: "<value expression>" IS NOT NULL
<is null> ::=: "<value expression>" IS NULL
"<like>" ::=: "<value expression>" LIKE '""match pattern string""' [CS | CI]

Contains Conditional Statement

<contains> is a conditional statement for executing full-text search to Entity. Lucene query string could be specified as argument of Contains conditional statement.

Because full-text search is performed by using Lucene, when using Contains clause, it is necessary to prepare the operating environment for Lucene with settings of FulltextSearchService.

CONTAINS('LuceneQueryExpression')

If CONTAINS clause is included in EQL, works as follows.

LuceneQueryExpression specified in CONTAINS clause is issued for Lucene, matching oid are returned by Lucene.
CONTAINS is converted to IN clause that specifies oid.
Eg. CONTAINS('abc') → oid IN ('12942', '1115', '32107' … )
※At this time, the maximum number specified in oid depends on the setting of FulltextSearchService.
Converted EQL is issued to RDB.

EQL Expression Example

CONTAINS('abc')

CONTAINS('abc*')

CONTAINS('"abc" AND "apache"')

When generating by using classes represent EQL, use classes under the package org.iplass.mtp.entity.query.condition.predicate.Contains.

Contains cnt = new Contains("abc*");

In Conditional Statement

<in> is a statement that determines whether matches subquery or the multiple values specified directly.

valueExp IN (val1, val2, … )

valueExp IN (SELECT field FROM entityName)

In Oracle, when specifying values directly in IN clause, the maximum number can be specified is 1000 by default. To relax restriction, you can enable enableInPartitioning of RdbAdapterService.

It is possible to construct IN conditions for multiple items using <row value list> expression.

(valueExp1, valueExp2) IN ((val11, val21), (val21, val22), … )

(valueExp1, valueExp2) IN (SELECT field1, field2 FROM entityName)

EQL Expression Example

propA IN('a', 'b', 'c')

propA IN(SELECT propX FROM EntityA)

(propA, propB) IN(('a', 1),('a',2),('b',2))

(propA, propB) IN(SELECT propX, propY FROM EntityA)

When generating by using classes represent EQL, use class org.iplass.mtp.entity.query.condition.predicate.In.

In in = new In("propA", "a", "b", "c");

In in = new In("propA", new Query().select("propX").from("EntityA"));

Like Conditional Statement

<like> is a conditional statement that determines whether pattern string matches. % or _ can be specified as wildcard. Escapes with \ when treating % or _ as a normal character. \ is escaped by using \\. CS and CI clauses specify whether matches in Case Sensitive and Case Insensitive.

valueExp LIKE 'patternExpression' [CS | CI]

EQL Expression Example

propA LIKE 'abc%'

propA LIKE 'let''s go%' CI

propA LIKE '\\100\_000%'

When generating by using classes represent EQL, use class org.iplass.mtp.entity.query.condition.predicate.Like.

When specifying pattern string directly, be careful with %, _, \, as they are not escaped automatically. Using the constructor with MatchPattern specified is recommended.

Like like = new Like("name", "abc%");

//When specifying pattern string directly from user input value, escaping is required.
Like like = new Like("name", StringUtil.escapeEqlForLike(userInputValue) + "%");

//When using the constructor with MatchPattern specified, escaping is done in the constructor.
Like like3 = new Like("name", userInputValue, MatchPattern.PREFIX, CaseType.CI);