Expressions and Literals

This page covers the following subtopics:

Value expressions are defined by the following syntax:

value-expression := <character-expression> | <number-expression> | <datetime-expression> | <interval-expression> | <boolean-expression> | <case_expression>

Character (String) Expressions

Character expressions are defined by the following syntax:

character-expression := <character-literal>
                 | <character-expression> || <character-expression>
                         | <character-function> ( <parameters> )


character-literal  := <quote> { <character> }* <quote>

string-literal     := <quote> { <character> }* <quote>

character-function   :=   CAST | COALESCE | CURRENT_PATH
                         | FIRST_VALUE  | INITCAP | LAST_VALUE
                         | LOWER | MAX | MIN | NULLIF
                         | OVERLAY | SUBSTRING| SYSTEM_USER
                         | TRIM | UPPER
                         | <user-defined-function>

Note that SQLstream streaming SQL supports Unicode character literals, such as u&‘foo’. As in the use of regular literals, you can escape single quotes in these, such as u&‘can'’t’. Unlike regular literals, you can include Unicode escapes: e.g., u&'\0009' is a string consisting only of a tab character. You can escape a \ with another , such as u&‘back\\slash’. SQLstream also supports alternate escape characters, such as u&'!0009!!' uescape ‘!’ is a tab character.

Numeric Expressions

Numeric expressions are defined by the following syntax:

number-expression := <number-literal>
                         | <number-unary-oper> <number-expression>
                         | <number-expression> <number-operator> <number-expression>
                         | <number-function> [ ( <parameters> ) ]

number-literal :=   <UNSIGNED_INTEGER_LITERAL> | <DECIMAL_NUMERIC_LITERAL>
                          | <APPROX_NUMERIC_LITERAL> |  
--Note: An <APPROX_NUMERIC_LITERAL> is a number in scientific notation, such as with an
--exponent, such as 1e2 or -1.5E-6.
number-unary-oper := + | -
number-operator      :=   + | - | / | *
number-function      :=   ABS | AVG | CAST | CEIL
                         | CEILING | CHAR_LENGTH
                         | CHARACTER_LENGTH | COALESCE
                         | COUNT | EXP | EXTRACT
                         | FIRST_VALUE
                         | FLOOR | LAST_VALUE
                         | LN | LOG10
                         | MAX | MIN  | MOD
                         | NULLIF
                         | POSITION | POWER
                         | SUM| <user-defined-function>
                         --Note: An <APPROX_NUMERIC_LITERAL> is a number in scientific notation, such as with an
                         --exponent, such as 1e2 or -1.5E-6.
                         number-unary-oper := + | -                         
                         number-operator      :=   + | - | / | *                         
                         number-function      :=   ABS | AVG | CAST | CEIL                         
                                                  | CEILING | CHAR_LENGTH                         
                                                  | CHARACTER_LENGTH | COALESCE                         
                                                  | COUNT | EXP | EXTRACT                         
                                                  | FIRST_VALUE                         
                                                  | FLOOR | LAST_VALUE                         
                                                  | LN | LOG10                         
                                                  | MAX | MIN  | MOD                         
                                                  | NULLIF                         
                                                  | POSITION | POWER                         
                                                  | SUM | <user-defined-function>

Date / Time Expressions

date / Time expressions are defined by the following syntax:

datetime-expression := <datetime-literal> | ROWTIME
                         | <datetime-expression> [ + | - ] <number-expression>
                         | <datetime-function> [ ( <parameters> ) ]

datetime-literal    :=  <left_brace> { <character-literal> } *  <right_brace>
                         |  <DATE> { <character-literal> } *
                         |  <TIME> { <character-literal> } *
                         |  <TIMESTAMP> { <character-literal> } *

datetime-function   :=   CAST | CEIL | CEILING
                         | CURRENT_DATE | CURRENT_ROW_TIMESTAMP
                         | CURRENT_ROW_TIMESTAMP
                         | FIRST_VALUE| FLOOR
                         | LAST_VALUE | LOCALTIME
                         | LOCALTIMESTAMP | MAX | MIN
                         | NULLIF | ROWTIME
                         | <user-defined-function>

<time unit>         :=  YEAR | MONTH | DAY | HOUR | MINUTE | SECOND

Note: Unadorned ROWTIME (not qualified with a stream name) refers to the rowtime pseudo-column of the result relation and can only be specified in the SELECT list or the ORDER BY clause.

Interval Expressions

Interval expressions are defined by the following syntax:

INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE } [ (leading_precision) ]
| SECOND [ (leading_precision [, fractional_seconds_precision ]) ]
}
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]

interval-expression := <interval-literal>
                     | <interval-function>
interval-literal   :=  <INTERVAL> ( <MINUS> | <PLUS> ) <QUOTED_STRING> <IntervalQualifier>
IntervalQualifier  :=  <DAY> [ (  <UNSIGNED_INTEGER_LITERAL> ) ]
                       | <DAY>  [ (  <UNSIGNED_INTEGER_LITERAL> )  ] <TO>
                                 { <HOUR> | <MINUTE> | <SECOND> [ ( <UNSIGNED_INTEGER_LITERAL> ) ] }
                       | <HOUR> [ (  <UNSIGNED_INTEGER_LITERAL> ) ]
                       | <HOUR> [ (  <UNSIGNED_INTEGER_LITERAL> ) ] <TO>
                                   { <MINUTE> | <SECOND> [ <UNSIGNED_INTEGER_LITERAL> ] }
                       | <MINUTE> [ ( <UNSIGNED_INTEGER_LITERAL> )  ]
                       | <MINUTE> [ ( <UNSIGNED_INTEGER_LITERAL> )  ] <TO>
                                    <SECOND> [ ( <UNSIGNED_INTEGER_LITERAL> ) ]
                       | <SECOND> ( <UNSIGNED_INTEGER_LITERAL> ) ]

interval-function   := ABS  | CAST | FIRST_VALUE
                       | LAST_VALUE | MAX | MIN
                       | NULLIF| <user-defined-function>

Boolean Expressions

Boolean expressions are defined by the following syntax:

boolean-expression := <boolean-literal>
                         | <boolean-expression> <boolean-operator> <boolean-expression>
                         | <boolean-unary-oper> <boolean-expression>
                                | <boolean-function> ( <parameters> )
                                | ( <boolean-expression> )

boolean-literal    :=  TRUE | FALSE
boolean-operator   :=  AND | OR
boolean-unary-oper :=  NOT
boolean-function   :=   CAST | FIRST_VALUE | LAST_VALUE
                                | NULLIF | <user-defined-function>

CASE expression

The CASE expression enables you to specify a set of discrete test expressions and a specific return-value (expression) for each such test. Each test expression is specified in a WHEN clause; each return-value expression is specified in the corresponding THEN clause. Multiple such WHEN-THEN pairs can be specified.

CASE Syntax Chart

If you specify a comparison-test-expression before the first WHEN clause, then each expression in a WHEN clause is compared to that comparison-test-expression. The first one to match the comparison-test-expression causes the return-value from its corresponding THEN clause to be returned. If no WHEN clause expression matches the comparison-test-expression, the return-value is null unless an ELSE clause is specified, in which case the return-value in that ELSE clause is returned.

If you do not specify a comparison-test-expression before the first WHEN clause, then each expression in a WHEN clause is evaluated (left to right) and the first one to be true causes the return-value from its corresponding THEN clause to be returned. If no WHEN clause expression is true, the return-value is null unless an ELSE clause is specified, in which case the return-value in that ELSE clause is returned.

VALUES

VALUES uses expressions to calculate one or more row values, and is often used within a larger command. When creating more than one row, the VALUES clause must specify the same number of elements for every row. The resulting table-columns data-types are derived from the explicit or inferred types of the expressions appearing in that column. VALUES is allowed syntactically wherever SELECT is permitted. See also the discussion of VALUES as an operator, in the topic Query.

Syntax

VALUES ( expression [,...] ) [,...]
[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [,...] ]

VALUES is a SQL operator, on a par with SELECT and UNION, enabling the following types of actions:
 - You can write VALUES (1), (2) to return two rows each with a single anonymous column.
 - You can write VALUES (1, 'a'), (2, 'b') to return two rows of two columns.
 - You can name the columns using AS, as in the following example:

SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(x, y);

The most important use of VALUES is in an INSERT statement, to insert a single row:

INSERT INTO emps (empno, name, deptno, gender)
VALUES (107, 'Jane Costa', 22, 'F');

However, you can also insert multiple rows:

INSERT INTO Trades (ticker, price, amount)
VALUES ('MSFT', 30.5, 1000),
('ORCL', 20.25, 2000);

When you use VALUES in the FROM clause of a SELECT statement, the entire VALUES clause must be enclosed in parentheses, consistent with the fact that it operates as a query, not a table expression. See additional examples in the topic Relations in this guide.

Note: Using INSERT with streams engages some additional considerations as to rowtimes, pumps, and INSERT EXPEDITED. See the topic INSERT in this guide.