Aggregate Functions

Supported

SELECT AVG(o.quantity) FROM app_Order o

Not supported: aggregate functions with scalar expression (EclipseLink feature)

SELECT AVG(o.quantity)/2.0 FROM app_Order o

SELECT AVG(o.quantity * o.price) FROM app_Order o

ALL, ANY, SOME

Supported

SELECT emp FROM app_Employee emp WHERE emp.salary > ALL (SELECT m.salary FROM app_Manager m WHERE m.department = emp.department)

Arithmetic Functions (INDEX, SIZE, ABS, SQRT, MOD)

Supported

SELECT w.name FROM app_Course c JOIN c.studentWaitlist w WHERE c.name = 'Calculus' AND INDEX(w) = 0

SELECT w.name FROM app_Course c WHERE c.name = 'Calculus' AND SIZE(c.studentWaitlist) = 1

SELECT w.name FROM app_Course c WHERE c.name = 'Calculus' AND ABS(c.time) = 10

SELECT w.name FROM app_Course c WHERE c.name = 'Calculus' AND SQRT(c.time) = 10.5

SELECT w.name FROM app_Course c WHERE c.name = 'Calculus' AND MOD(c.time, c.time1) = 2

CASE Expressions in UPDATE query

Supported

SELECT e.name, f.name, CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum ' WHEN f.annualMiles > 25000 THEN 'Gold ' ELSE '' END, 'Frequent Flyer') FROM app_Employee e JOIN e.frequentFlierPlan f

Not supported: CASE in UPDATE query

UPDATE app_Employee e SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1 WHEN 2 THEN e.salary * 1.05 ELSE e.salary * 1.01 END

Date Functions (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)

Supported

SELECT e FROM app_Order e WHERE e.date = CURRENT_DATE

EclipseLink Functions (CAST, REGEXP, EXTRACT)

Supported

SELECT EXTRACT(YEAR FROM e.createTs) FROM app_MyEntity e WHERE EXTRACT(YEAR FROM e.createTs) > 2012

SELECT e FROM app_MyEntity e WHERE e.name REGEXP '.*'

SELECT CAST(e.number text) FROM app_MyEntity e WHERE e.path LIKE CAST(:ds$myEntityDs.id text)

Not supported: CAST in GROUP BY clause

SELECT e FROM app_Order e WHERE e.amount > 100 GROUP BY CAST(e.orderDate date)

Entity Type Expression

Supported: entity type passed as a parameter

SELECT e FROM app_Employee e WHERE TYPE(e) IN (:empType1, :empType2)

Not supported: direct link to an entity type

SELECT e FROM app_Employee e WHERE TYPE(e) IN (app_Exempt, app_Contractor)

Function Invocation

Supported: function result in comparison clauses

SELECT u FROM sec$User u WHERE function('DAYOFMONTH', u.createTs) = 1

Not supported: function result as is

SELECT u FROM sec$User u WHERE function('hasRoles', u.createdBy, u.login)

IN

Supported

SELECT e FROM Employee e, IN(e.projects) p WHERE p.budget > 1000000

IS EMPTY collection

Supported

SELECT e FROM Employee e WHERE e.projects IS EMPTY

KEY/VALUE

Not supported

SELECT v.location.street, KEY(i).title, VALUE(i) FROM app_VideoStore v JOIN v.videoInventory i WHERE v.location.zipcode = '94301' AND VALUE(i) > 0

Literals

Supported

SELECT e FROM app_Employee e WHERE e.name = 'Bob'

SELECT e FROM app_Employee e WHERE e.id = 1234

SELECT e FROM app_Employee e WHERE e.id = 1234L

SELECT s FROM app_Stat s WHERE s.ratio > 3.14F

SELECT s FROM app_Stat s WHERE s.ratio > 3.14e32D

SELECT e FROM app_Employee e WHERE e.active = TRUE

Not supported: date and time literals

SELECT e FROM app_Employee e WHERE e.startDate = {d'2012-01-03'}

SELECT e FROM app_Employee e WHERE e.startTime = {t'09:00:00'}

SELECT e FROM app_Employee e WHERE e.version = {ts'2012-01-03 09:00:00.000000001'}

MEMBER OF

Supported: fields or query results

SELECT d FROM app_Department d WHERE (select e from app_Employee e where e.id = :eParam) MEMBER OF e.employees

Not supported: literals

SELECT e FROM app_Employee e WHERE 'write code' MEMBER OF e.codes

NEW in SELECT

Supported

SELECT NEW com.acme.example.CustomerDetails(c.id, c.status, o.count) FROM app_Customer c JOIN c.orders o WHERE o.count > 100

NULLIF/COALESCE

Supported

SELECT NULLIF(emp.salary, 10) FROM app_Employee emp

SELECT COALESCE(emp.salary, emp.salaryOld, 10) FROM app_Employee emp

NULLS FIRST, NULLS LAST in order by

Supported

SELECT h FROM sec$GroupHierarchy h ORDER BY h.level DESC NULLS FIRST

String Functions (CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE)

Supported

SELECT x FROM app_Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'

SELECT x FROM app_Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'

SELECT x FROM app_Magazine x WHERE LOWER(x.title) = 'd'

SELECT x FROM app_Magazine x WHERE UPPER(x.title) = 'D'

SELECT x FROM app_Magazine x WHERE LENGTH(x.title) = 10

SELECT x FROM app_Magazine x WHERE LOCATE('A', x.title, 4) = 6

SELECT x FROM app_Magazine x WHERE TRIM(TRAILING FROM x.title) = 'D'

Not supported: TRIM with trim char

SELECT x FROM app_Magazine x WHERE TRIM(TRAILING 'J' FROM x.title) = 'D'

Subquery

Supported

SELECT goodCustomer FROM app_Customer goodCustomer WHERE goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) FROM app_Customer c)

Not supported: path expression instead of entity name in subquery’s FROM

SELECT c FROM app_Customer c WHERE (SELECT AVG(o.price) FROM c.orders o) > 100

TREAT

Supported

SELECT e FROM app_Employee e JOIN TREAT(e.projects AS app_LargeProject) p WHERE p.budget > 1000000

Not supported: TREAT in WHERE clauses

SELECT e FROM Employee e JOIN e.projects p WHERE TREAT(p as LargeProject).budget > 1000000