Агрегатные функции |
ДА |
SELECT AVG(o.quantity) FROM app$Order o
|
НЕТ: агрегатные функции со скалярными выражениями (особенность EclipseLink) |
SELECT AVG(o.quantity)/2.0 FROM app$Order o
SELECT AVG(o.quantity * o.price) FROM app$Order o
|
ALL, ANY, SOME |
ДА |
SELECT emp FROM app$Employee emp WHERE emp.salary > ALL (SELECT m.salary FROM app$Manager m WHERE m.department = emp.department)
|
Арифметические функции (INDEX, SIZE, ABS, SQRT, MOD) |
ДА |
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 |
ДА |
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
|
НЕТ: CASE в UPDATE-запросе |
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
|
Функции даты и времени (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP) |
ДА |
SELECT e FROM app$Order e WHERE e.date = CURRENT_DATE
|
Функции EclipseLink (CAST, REGEXP, EXTRACT) |
ДА |
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)
|
НЕТ: CAST в запросе GROUP BY |
SELECT e FROM app$Order e WHERE e.amount > 100 GROUP BY CAST(e.orderDate date)
|
Операторы типов сущности |
ДА: тип сущности передаётся как параметр |
SELECT e FROM app$Employee e WHERE TYPE(e) IN (:empType1, :empType2)
|
НЕТ: прямая ссылка на сущность |
SELECT e FROM app$Employee e WHERE TYPE(e) IN (app$Exempt, app$Contractor)
|
Вызов функций |
ДА: результат с операторами сравнения |
SELECT u FROM sec$User u WHERE function('DAYOFMONTH', u.createTs) = 1
|
НЕТ: прямое использование результата функции |
SELECT u FROM sec$User u WHERE function('hasRoles', u.createdBy, u.login)
|
IN |
ДА |
SELECT e FROM Employee e, IN(e.projects) p WHERE p.budget > 1000000
|
IS EMPTY для коллекций |
ДА |
SELECT e FROM Employee e WHERE e.projects IS EMPTY
|
KEY/VALUE |
НЕТ |
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
|
Литералы |
ДА |
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
|
НЕТ: литералы даты и времени |
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 |
ДА: для полей и запросов |
SELECT d FROM app$Department d WHERE (select e from app$Employee e where e.id = :eParam) MEMBER OF e.employees
|
НЕТ: для литералов |
SELECT e FROM app$Employee e WHERE 'write code' MEMBER OF e.codes
|
NEW в SELECT |
ДА |
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 |
ДА |
SELECT NULLIF(emp.salary, 10) FROM app$Employee emp
SELECT COALESCE(emp.salary, emp.salaryOld, 10) FROM app$Employee emp
|
NULLS FIRST, NULLS LAST в order by |
ДА |
SELECT h FROM sec$GroupHierarchy h ORDER BY h.level DESC NULLS FIRST
|
Строковые функции (CONCAT, SUBSTRING, TRIM, LOWER, UPPER, LENGTH, LOCATE) |
ДА |
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'
|
НЕТ: TRIM не поддерживается с trim char |
SELECT x FROM app$Magazine x WHERE TRIM(TRAILING 'J' FROM x.title) = 'D'
|
Вложенные запросы |
ДА |
SELECT goodCustomer FROM app$Customer goodCustomer WHERE goodCustomer.balanceOwed < (SELECT AVG(c.balanceOwed) FROM app$Customer c)
|
НЕТ: path-выражения вместо имени сущности в FROM подзапроса |
SELECT c FROM app$Customer c WHERE (SELECT AVG(o.price) FROM c.orders o) > 100
|
TREAT |
ДА |
SELECT e FROM app$Employee e JOIN TREAT(e.projects AS app$LargeProject) p WHERE p.budget > 1000000
|
НЕТ: TREAT в WHERE-выражениях |
SELECT e FROM Employee e JOIN e.projects p WHERE TREAT(p as LargeProject).budget > 1000000
|
Edit on GitHub