UnityJDBC - Syntax Features

Summary

Function Syntax Description
#AS# AS AS is used for attribute and table aliasing.
#CROSSJOIN# JOIN Does database support a cross-product.
#DATE_DELIMITER# ' The symbol used as the date constant delimiter.
#DELIMIT_CHAR# " Character for delimiting identifiers.
#DUPLICATE_FIELD# Does the database support duplicate fields in a SELECT clause.
#EQUIJOIN# JOIN Does database support a one-attribute equi-join.
#EQUIJOIN-N# JOIN Does database support a N-attribute equi-join.
#FIELD_LIST# FIELD LIST Does database support a field list in SELECT clause.
#FULLOUTERJOIN# FULL OUTER JOIN Does database support a one attribute full-outer join.
#FULLOUTERJOIN-N# FULL OUTER JOIN Does database support a N attribute full-outer join.
#GROUPBY# GROUP BY Does database support GROUP BY.
#HAVING# HAVING Does database support HAVING clause.
#JOIN# JOIN Does database supports some form of join or cross-product.
#LEFTOUTERJOIN# LEFT OUTER JOIN Does database support a one attribute left-outer join.
#LEFTOUTERJOIN-N# LEFT OUTER JOIN Does database support a N attribute left-outer join.
#LIMIT# LIMIT expr Support for LIMIT expr clause.
#LIMIT_OFFSET# LIMIT expr1 OFFSET expr2 Support for LIMIT expr1 OFFSET expr2 clause.
#NONEQUIJOIN# JOIN Does database support a non-equi-join.
#ORDERBY# ORDER BY Does database support ORDER BY.
#RIGHTOUTERJOIN# RIGHT OUTER JOIN Does database support a one attribute right-outer join.
#RIGHTOUTERJOIN-N# RIGHT OUTER JOIN Does database support a N attribute right-outer join.
#SUBQUERY# Does database support subqueries.
#WHERE# WHERE Does database support any form of WHERE clause.
CASE CASE WHEN cond1 THEN expr1 . . . ELSE elseExpr END CASE WHEN THEN END Syntax
INTERVAL INTERVAL dateExpr intervalConstant Support for intervals.

Detailed Function Descriptions

#AS#

Syntax: AS

AS is used for attribute and table aliasing. Some systems require AS always be present where it is optional for others. By default, UnityJDBC does not use AS.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#CROSSJOIN#

Syntax: JOIN

Does database support a cross-product.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#DATE_DELIMITER#

Syntax: '

The symbol used as the date constant delimiter. It is the single quote (') for most systems.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#DELIMIT_CHAR#

Syntax: "

Character for delimiting identifiers. It is double quotes (") in most systems. For example, "my field with spaces" or for reserved words used as identifiers such as "from".

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#DUPLICATE_FIELD#

Syntax:

Does the database support duplicate fields in a SELECT clause.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#EQUIJOIN#

Syntax: JOIN

Does database support a one-attribute equi-join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#EQUIJOIN-N#

Syntax: JOIN

Does database support a N-attribute equi-join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#FIELD_LIST#

Syntax: FIELD LIST

Does database support a field list in SELECT clause.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#FULLOUTERJOIN#

Syntax: FULL OUTER JOIN

Does database support a one attribute full-outer join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#FULLOUTERJOIN-N#

Syntax: FULL OUTER JOIN

Does database support a N attribute full-outer join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#GROUPBY#

Syntax: GROUP BY

Does database support GROUP BY.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#HAVING#

Syntax: HAVING

Does database support HAVING clause.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#JOIN#

Syntax: JOIN

Does database supports any form of join or cross-product.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#LEFTOUTERJOIN#

Syntax: LEFT OUTER JOIN

Does database support a one attribute left-outer join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#LEFTOUTERJOIN-N#

Syntax: LEFT OUTER JOIN

Does database support a N attribute left-outer join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#LIMIT#

Syntax: LIMIT expr

The LIMIT expr clause returns the first records given by the value of expr. For example, LIMIT 20 returns the first 20 records. Some database uses the TOP N clause or the OVER() clause and window functions to achieve the same effect.

Example

SELECT n_nationkey FROM Nation N LIMIT 20

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#LIMIT_OFFSET#

Syntax: LIMIT expr1 OFFSET expr2

The LIMIT expr1 OFFSET expr2 clause returns the next expr1 records starting after record expr2. For example, LIMIT 20 OFFSET 5 would return records 6 to 25. Record numbers start at 1. The TOP N clause can no longer be used. Some databases support window functions and the OVER() clause, or it is possible to use row number support and nested subqueries to achieve this affect. If a database support TOP N but not LIMIT/OFFSET, the query is translated into LIMIT expr1+expr2 to limit the data returned from the server, and the offset is done internally in UnityJDBC.

Standard Example

SELECT N.n_nationkey FROM Nation N LIMIT 20 OFFSET 5

Ordering Example with LIMIT/OFFSET

SELECT N.n_nationkey FROM Nation N LIMIT 20 OFFSET 5 ORDER BY N.n_name ASC

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#NONEQUIJOIN#

Syntax: JOIN

Does database support a non-equi-join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#ORDERBY#

Syntax: ORDER BY

Does database support ORDER BY.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#RIGHTOUTERJOIN#

Syntax: RIGHT OUTER JOIN

Does database support a one attribute right-outer join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#RIGHTOUTERJOIN-N#

Syntax: RIGHT OUTER JOIN

Does database support a N attribute right-outer join.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#SUBQUERY#

Syntax:

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

#WHERE#

Syntax: WHERE

Does database support any form of WHERE clause.

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

CASE

Syntax: CASE WHEN cond1 THEN expr1 . . . ELSE elseExpr END

Support for CASE WHEN THEN END syntax.

Integer (SWITCH) Version

SELECT r_regionkey, CASE r_regionkey WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END AS val FROM Region R

General Expression Version

SELECT r_regionkey, CASE WHEN r_regionkey<=2 THEN 'le<=2' WHEN r_regionkey = 3 THEN 'three' WHEN r_regionkey > 4 THEN ''+r_regionkey END AS val FROM Region R

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

INTERVAL

Syntax: INTERVAL dateExpr intervalConstant

Interval support. First parameter is a number or string representing the interval. The second parameter (if given) is the interval type. Example:

INTERVAL '03:00:00' HOUR_SECOND

Supported interval constants

Interval ConstantExample
YEARINTERVAL 3 YEAR
QUARTERINTERVAL (2+1) MONTH
MONTHINTERVAL 2 QUARTER
WEEKINTERVAL 5 WEEK
DAYINTERVAL 29 DAY
HOURINTERVAL 14 HOUR
MINUTEINTERVAL expr MINUTE
SECONDINTERVAL 24 SECOND
MICROSECONDINTERVAL 10 MICROSECOND
YEAR_MONTHINTERVAL '1-2' YEAR_MONTH
DAY_HOURINTERVAL '1 2:3' DAY_HOUR
DAY_MINUTEINTERVAL '1 2:3:40' DAY_MINUTE
DAY_MICROSECONDINTERVAL '3 4:5:20.6' DAY_MICROSECOND
HOUR_MINUTEINTERVAL '3:30' HOUR_MINUTE
HOUR_SECONDINTERVAL '1:30:00' HOUR_SECOND
HOUR_MICROSECONDINTERVAL '1:30:00.5' HOUR_MICROSECOND
MINUTE_MICROSECONDINTERVAL '1:3.5' MINUTE_MICROSECOND
SECOND_MICROSECONDINTERVAL '3.5' SECOND_MICROSECOND

SQL Server Access Oracle PostgreSQL MySQL Sybase HSQLDB

More functions