FUNCTION

Use FUNCTION (formerly FUNC) to call database specific functions from JPQL

Usage

You can use FUNCTION to call database functions that are not supported directly in JPQL and to call user or library specific functions.

Note:

FUNCTION is database specific – it does not translate the function call in any way to support different databases as other JPQL functions do.

Use FUNCTION to call functions with normal syntax. Functions that require special syntax cannot be called with FUNCTION. Instead, use OPERATOR

Examples

Example 3-6 shows how to use this JPQL extension.

Example 3-6 Using FUNCTION EQL

SELECT p FROM Phone p WHERE FUNCTION('TO_NUMBER', e.areaCode) > 613
 
SELECT FUNCTION('YEAR', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year

Example 3-7 shows how to use FUNCTION with Oracle Spatial queries

Example 3-7 Using FUNCTION EQL Oracle Spatial examples

SELECT a FROM Asset a, Geography geo WHERE geo.id = :id AND a.id IN :id_list AND FUNCTION('ST_INTERSECTS', a.geometry, geo.geometry) = 'TRUE'

SELECT s FROM SimpleSpatial s WHERE FUNCTION('MDSYS.SDO_RELATE', s.jGeometry, :otherGeometry, :params) = 'TRUE' ORDER BY s.id ASC

See Also

For more information, see: