[top]
[prev]
[next]

Documentation Top
Global Index
Reference Manual
TOC Index
Grammar Index
Developer’s Guide
TOC Index
Tuning Cloudscape
TOC Index
|
SQL-J Expressions
Syntax for many statements and expressions includes the term Expression, or a term for a specific kind of expression such as Method Invocation or TableSubquery. Expressions are allowed in these specified places within statements. Some locations allow only a specific type of expression or one with a specific property. Table 1-9, "Table of Expressions", lists all the possible SQL-J expressions and indicates where they are allowed.
If not otherwise specified, an expression is permitted anywhere the word Expression appears in the syntax. This includes:
Of course, many other statements include these elements as building blocks, and so allow expressions as part of these elements.
Table 1-9 Table of Expressions
Expression Type |
Explanation |
General expressions
|
All expressions that might result in a value of any type.
|
Column reference
Allowed in SelectExpressions, UPDATE statements, and the WHERE clauses of data manipulation statements.
|
A ColumnName that references the value of the column made visible to the expression containing the Column reference.
You must qualify the ColumnName by the table name or correlation name if it is ambiguous.
The qualifier of a ColumnName must be the correlation name, if a correlation name is given to a table that is in a FROM clause. The table name is no longer visible as a ColumnName qualifier once it has been aliased by a correlation name.
|
Literal
|
Most built-in data types typically have literals associated with them (as shown in Data Types).
|
NULL
Allowed in CAST expressions or in INSERT VALUES lists and UPDATE SET clauses. Using it in a CAST expression gives it a specific data type.
|
NULL is an untyped literal representing the unknown value.
|
Dynamic parameter
Allowed anywhere in an expression where the data type can be easily deduced. See Dynamic Parameters.
|
A dynamic parameter is a parameter to an SQL statement for which the value is not specified when the statement is created. Instead, the statement has a question mark (?) as a placeholder for each dynamic parameter. See Dynamic Parameters.
Dynamic parameters are permitted only in prepared statements. You must specify values for them before the prepared statement is executed. The values specified must match the types expected.
|
CAST expression
|
Lets you specify the type of NULL or of a dynamic parameter or convert a value to another type. See CAST.
|
scalar subquery
|
Subquery that returns a single row with a single column. See ScalarSubquery.
|
table subquery
Allowed as a tableExpression in a FROM clause and with EXISTS, IN, and quantified comparisons.
|
Subquery that returns more than one column and more than one row. See TableSubquery.
|
Method invocation
|
Invocation of a method associated with a Java class or instance of a class. See Method Invocation.
|
Field access
|
Access of the public field of a Java class or instance of a class. See Field Access.
|
Conditional expression
|
A conditional expression chooses an expression to evaluate based on a boolean test. See Conditional (?:).
|
User-Defined Aggregate
|
Performs a user-defined aggregation and can return any type. See User-Defined Aggregate.
|
Boolean expressions
|
Expressions that result in boolean values. Most general expressions can result in boolean values.
Boolean expressions commonly used in a WHERE clause are made of operands operated on by SQL-J operators. See Table 1-10, "SQL-J Boolean Operators".
|
Numeric expressions
|
Expressions that result in numeric values. Most of the general expressions can result in numeric values.
Numeric values have one of the following types:
TINYINT, SMALLINT, INTEGER, -LONGINT, REAL, DOUBLE PRECISION, DECIMAL.
|
+, -, *, /, unary + and - expressions
|
+, -, *, /, unary + and -
Evaluate the expected math operation on the operands. If both operands are the same type, the result type is not promoted, so the division operator on integers results in an integer that is the truncation of the actual numeric result. When types are mixed, they are promoted as described in Data Types.
Unary + is a noop (i.e., +4 is the same as 4). Unary - is the same as multiplying the value by -1, effectively changing its sign.
|
AVG
|
Returns the average of a set of numeric values. AVG
|
SUM
|
Returns the sum of a set of numeric values. SUM
|
CHAR_LENGTH, CHARACTER_LENGTH
|
Returns the number of characters in a character or bit string. See CHAR_LENGTH, CHARACTER_LENGTH.
|
LOWER
|
See LOWER.
|
OCTET_LENGTH
|
Returns the number of octets in a character string or bit string. See OCTET_LENGTH.
|
BIT_LENGTH
|
Returns the number of bits in a character string or bit string. See BIT_LENGTH.
|
EXTRACT
|
Extracts DATE, TIME, or TIMESTAMP field as an integer value from a date/time data type. See EXTRACT.
|
COUNT
|
Returns the count of a set of values. See COUNT, COUNT(*).
|
Character expressions
|
Expressions that result in a CHAR or VARCHAR value. Most general expressions can result in a CHAR or VARCHAR value.
|
A CHAR or VARCHAR value that uses wildcards.
Used in a LIKE pattern.
|
The wildcards % and _ make a character string a pattern against which the LIKE operator can look for a match.
See "LIKE" in Table 1-10.
|
Concatenation expression
|
In a concatenation expression, the concatenation operator, "||", concatenates its right operand to the end of its left operand. Operates on character and bit strings. See Concatenation.
|
Built-in string functions
|
The built-in string functions act on a String and return a string. See LTRIM, LOWER, RTRIM, SUBSTRING, SUBSTR, TRIM, and UPPER
|
USER functions
|
User functions return information about the current user as a String. See CURRENT_USER, SESSION_USER, and USER
|
Date/time expressions
|
A date/time expression results in a DATE, TIME, or TIMESTAMP value. Most of the general expressions can result in a date/time value.
|
CURRENT_DATE
|
Returns the current date. See CURRENT_DATE.
|
CURRENT_TIME
|
Returns the current time. See CURRENT_TIME.
|
CURRENT_TIMESTAMP
|
Returns the current timestamp. See CURRENT_TIMESTAMP.
|
Java expressions
|
A Java expression is an expression that operates on a Java object.
Some Java expressions also return Java objects.
|
Method invocation
|
See Method Invocation.
|
Field access
|
See Field Access.
|
INSTANCEOF expression
|
See INSTANCEOF Expression.
|
NEW expression
|
See NEW.
|
GETCURRENTCONNECTION()
|
See GETCURRENTCONNECTION().
|
Expression Precedence
Precedence of operations from highest to lowest is:
- (), ?, Literal (including sign), NULL, ColumnReference, ScalarSubquery, CAST, NEW, method invocation on columns
- CLASS, -> (field access), EXTRACT, CHAR_LENGTH, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, BIT_LENGTH, OCTET_LENGTH, and other built-ins
- method invocation (except on columns)
- unary + and -
- *, /, || (concatenation)
- binary + and -
- comparisons, Quantified comparisons, EXISTS, IN, IS NULL, LIKE, BETWEEN, INSTANCEOF, IS
- NOT
- AND
- OR
- ?: (Conditional)
You can explicitly specify precedence by placing expressions within parentheses. An expression within parentheses is evaluated before any operations outside the parentheses are applied to it.
Example
(3+4)*9
(age < 16 OR age > 65) AND employed = TRUE
|