Expressions

Identifiers, literals, and functions can be combined into expressions. Expressions can be used almost anywhere in a query: SELECT, FROM (if specifying join criteria), WHERE, GROUP BY, HAVING, or ORDER BY.

The Data Virtuality Server supports the following types of expressions:

• Column Identifiers
• Literals
• Scalar Functions
• Arrays
• Aggregate Functions
• Window Functions
• Case and Searched Case
• Scalar Subqueries
• Parameter References

Column Identifiers

Column identifiers are used to specify the output columns in SELECT statements, the columns and their values for INSERT and UPDATE statements, and criteria used in WHERE and FROM clauses. They are also used in GROUP BY, HAVING, and ORDER BY clauses. The syntax for column identifiers has been defined in the Identifiers section above.

Literals

Literal values represent fixed values. These can be any of the 'standard' data types.

Syntax Rules:

• Integer values will be assigned an integral data type big enough to hold the value (integer, long, or biginteger).
• Floating point values will always be parsed as a double.
• The keyword 'null' represents an absent or unknown value and is inherently untyped. In many cases, a null literal value will be assigned an implied type based on context. For example, in the function '5 + null', the null value will be assigned the type 'integer' to match the type of the value '5'. A null literal used in the SELECT clause of a query with no implied context will be assigned to type 'string'. 

Some examples of simple literal values

• 'abc'
• Escaped single quote: 'isn' 't true'
• 5
• Scientific notation: 37.75e01
• Exact numeric type BigDecimal: 100.0
• TRUE
• FALSE
• Unicode character: '\u0027'
• Binary: X'0F0A'

Date/Time Literals can use JDBC Escaped Literal Syntax or the ANSI keyword syntax:

Using Unicode characters

Specifying any Unicode characters (with codes 0-65535) in SQL strings is possible. Generally, if you try to include special characters in the usual way using a backslash, it will be taken literally by DV. To use special characters, you should use an 'E' letter before the first apostrophe character bounding a string constant.

The syntax supports the following codes: \b, \t, \n, \f, \r and the standard way to specify a Unicode character: \uXXXX, where X is a hexadecimal digit.

Examples:

SELECT 'some\ntext'

SELECT E'some\ntext'

SELECT E'danke sch\u00f6n'

Arrays

An array is an ordered list of elements of a single data type. Each element has its own index value by which it can be accessed.

Defining Arrays

General Array Syntax

(expr, expr ... [,])
ARRAY(expr, ...)

Empty Arrays

()
(,)
ARRAY()

Single Element Arrays

(expr,) 
ARRAY(expr)

Using Arrays

Here are some things to keep in mind when working with arrays:

• In an SQL statement, arrays use 1-based indexes, i.e. the first element of an array must be referenced as ARRAY[1];
• For the parser to recognize a single element expression as an array with parentheses, a trailing comma is necessary;
• If all of the elements of the array have the same type, the array will have a matching base type. If the element types are different, the base type of the array will be object.

And here's how to define and use an array:

BEGIN

// Defining an array
DECLARE OBJECT favourite_birds = ARRAY('seagull', 'eagle', 'nightingale', 'sparrow');

// Using the array
SELECT favourite_birds[2]

END

// Output
eagle

Case and Searched Case

The Data Virtuality Server supports two forms of the CASE expression, which allows conditional logic in a scalar expression. The supported forms are the following:

CASE <expr> ( WHEN <expr> THEN <expr>)+ [ELSE expr] END

CASE ( WHEN <criteria> THEN <expr>)+ [ELSE expr] END 

Each form allows for an output based on conditional logic. The first form starts with an initial expression, evaluates WHEN expressions until the values match, and outputs the THEN expression. If no WHEN is matched, the ELSE expression is output. If no WHEN is matched and no ELSE is specified, a null literal value is output. The second form (the searched case expression) searches the WHEN clauses, which specify arbitrary criteria to evaluate. If any criteria evaluate to TRUE, the THEN expression is evaluated and output. If no WHEN is true, the ELSE is evaluated or NULL is output if none exists.

Scalar Subqueries

Subqueries can be used to produce a single scalar value in the SELECT, WHERE, or HAVING clauses only. A scalar subquery must have a single column in the SELECT clause and should return either 0 or 1 row. If no rows are returned, null will be returned as the scalar subquery value.

Parameter References

Parameters are specified using the '?' symbol. Parameters may only be used with PreparedStatement or CallableStatements in JDBC. Each parameter is linked to a value specified by a 1-based index in the JDBC API.