Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

6.4. SQL Extensions

The hierarchical database adds several features to its support of the standard JCR-SQL and JCR-SQL2 grammars. These extensions include support for:
  1. Additional join types with FULL OUTER JOIN and CROSS JOIN
  2. UNION , INTERSECT , and EXCEPT set operations
  3. Non-correlated subqueries in the WHERE clause; multiple subqueries can be used in a single query, and they can even be nested
  4. Removing duplicate rows with SELECT DISTINCT ...
  5. Limit the number of rows returned with LIMITcount
  6. Skip initial rows with OFFSETnumber
  7. Constrain the depth of a node with DEPTH(selectorName)
  8. Constrain the path of a node with PATH(selectorName)
  9. Constrain the references from a node with REFERENCE(selectorName.property) and REFERENCE(selectorName)
  10. Ranges of criteria values using BETWEENlowerANDupper and optionally specifying whether to exclude the lower and/or upper values
  11. Set criteria to specify multiple criteria values using IN and NOT IN
  12. Use simple arithmetic in criteria and ORDER BY clauses, such as SCORE(type1)*3 + SCORE(type2)
  13. Use pseudo-columns to include the path, score, node name, node local name, and node depth in result columns or in criteria
More detail of the particular extensions can be found in the JCR-SQL2 grammar.
Use these extensions within your JCR-SQL or JCR-SQL2 query expressions strings, and use the standard JCR API to obtain a Query : 
// Obtain the query manager for the session via the workspace ...
javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();

// Create a query object ...
String language = ...
String expression = ...  // USE THE EXTENSIONS HERE
javax.jcr.query.Query query = queryManager.createQuery(expression,language);

// And use the query ...