Google Git
Sign in
apache / trafodion / be44bef2b74c20c12b84723b561dbe5ca965dd04 / . / docs / sql_reference / src / asciidoc / _chapters / sql_functions_and_expressions.adoc
blob: f84eec08b5e06056f082c761f7dbd7a0560fb438 [file] [log] [blame]
////
/**
* @@@ START COPYRIGHT @@@
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*
* @@@ END COPYRIGHT @@@
*/
////
[[sql_functions_and_expressions]]
= SQL Functions and Expressions
This section describes the syntax and semantics of specific functions
and expressions that you can use in {project-name} SQL statements. The
functions and expressions are categorized according to their
functionality.
[[standard_normalization]]
== Standard Normalization
For datetime functions, the definition of standard normalization is: If
the ending day of the resulting date is invalid, the day will be rounded
DOWN to the last day of the result month.
== Aggregate (Set) Functions
An aggregate (or set) function operates on a group or groups of rows
retrieved by the SELECT statement or the subquery in which the aggregate
function appears.
[cols="25%,75%"]
|===
| <<avg_function,AVG Function>> | Computes the average of a group of numbers derived from the evaluation
of the expression argument of the function.
| <<count_function,COUNT Function>> | Counts the number of rows that result from a query (by using
*) or the number of rows that contain a distinct value in the one-column
table derived from the expression argument of the function (optionally
distinct values).
| <<group_concat_function,GROUP_CONCAT Function>> | This function returns a string result with the concatenated non-NULL
values from a group.
| <<max_function,MAX/MAXIMUM Function>> | Determines a maximum value from the group of values derived from the
evaluation of the expression argument.
| <<min_function,MIN Function>> | Determines a minimum value from the group of values derived from the
evaluation of the expression argument.
| <<rollup_function,ROLLUP Function>> | Calculates multiple levels of subtotals aggregating from right to left through the
comma-separated list of columns, and provides a grand total.
| <<stddev_function,STDDEV Function>> | Computes the statistical standard deviation of a group of numbers
derived from the evaluation of the expression argument of the function.
The numbers can be weighted.
| <<sum_function,SUM Function>> | Computes the sum of a group of numbers derived from the evaluation of
the expression argument of the function.
"VARIANCE Function"
Computes the statistical variance of a group of numbers derived from the
evaluation of the expression argument of the function. The numbers can
be weighted.
|===
Columns and expressions can be arguments of an aggregate function. The
expressions cannot contain aggregate functions or subqueries.
An aggregate function can accept an argument specified as DISTINCT,
which eliminates duplicate values before the aggregate function is
applied. See <<distinct_aggregate_functions,DISTINCT Aggregate Functions>>.
If you include a GROUP BY clause in the SELECT statement, the columns
you refer to in the select list must be either grouping columns or
arguments of an aggregate function. If you do not include
a GROUP BY clause but you specify an aggregate function in the select
list, all rows of the SELECT result table form the one and only group.
See the individual entry for the function.
[[character_string_functions]]
== Character String Functions
These functions manipulate character strings and use a character value
expression as an argument or return a result of a character data type.
Character string functions treat each single-byte or multi-byte character
in an input string as one character, regardless of the byte length of
the character.
[cols="25%,75%"]
|===
| <<ascii_function,ASCII Function>> | Returns the ASCII code value of the first character of a character value
expression.
| <<char_function,CHAR Function>> | Returns the specified code value in a character set.
| <<char_length_function,CHAR_LENGTH Function>> | Returns the number of characters in a string. You can also use
CHARACTER_LENGTH.
| <<code_value_function,CODE_VALUE Function>> | Returns an unsigned integer that is the code point of the first
character in a character value expression that can be associated with
one of the supported character sets.
| <<concat_function,CONCAT Function>> | Returns the concatenation of two character value expressions as a string
value. You can also use the concatenation operator (\|\|).
| <<insert_function,INSERT Function>> | Returns a character string where a specified number of characters within
the character string have been deleted and then a second character
string has been inserted at a specified start position.
| <<lcase_function,LCASE Function>> | Down-shifts alphanumeric characters. You can also use LOWER.
| <<left_function,LEFT Function>> | Returns the leftmost specified number of characters from a character expression.
| <<locate_function,LOCATE Function>> | Returns the position of a specified substring within a character string.
You can also use POSITION.
| <<lower_function,LOWER Function>> | Down-shifts alphanumeric characters. You can also use LCASE.
| <<lpad_function,LPAD Function>> | Replaces the leftmost specified number of characters in a character
expression with a padding character.
| <<ltrim_function,LTRIM Function>> | Removes leading spaces from a character string.
| <<octet_length_function,OCTET_LENGTH Function>> | Returns the length of a character string in bytes.
| <<position_function,POSITION Function>> | Returns the position of a specified substring within a character string.
You can also use LOCATE.
| <<repeat_function,REPEAT Function>> | Returns a character string composed of the evaluation of a character
expression repeated a specified number of times.
| <<replace_function,REPLACE Function>> | Returns a character string where all occurrences of a specified
character string in the original string are replaced with another
character string.
| <<right_function,RIGHT Function>> | Returns the rightmost specified number of characters from a character
expression.
| <<rpad_function,RPAD Function>> | Replaces the rightmost specified number of characters in a character
expression with a padding character.
| <<rtrim_function,RTRIM Function>> | Removes trailing spaces from a character string.
| <<space_function,SPACE Function>> | Returns a character string consisting of a specified number of spaces.
| <<substring_function,SUBSTRING/SUBSTR Function>> | Extracts a substring from a character string.
| <<translate_function,TRANSLATE Function>> | Translates a character string from a source character set to a target
character set.
| <<trim_function,TRIM Function>> | Removes leading or trailing characters from a character string.
| <<ucase_function,UCASE Function>> | Up-shifts alphanumeric characters. You can also use UPSHIFT or UPPER.
| <<upper_function,UPPER Function>> | Up-shifts alphanumeric characters. You can also use UPSHIFT or UCASE.
| <<upshift_function,UPSHIFT Function>> | Up-shift alphanumeric characters. You can also use UPPER or UCASE.
|===
See the individual entry for the function.
[[datetime_functions]]
== Datetime Functions
These functions use either a datetime value expression as an argument or
return a result of datetime data type:
[caption="Table 7-3 "]
.Overview of DATETIME Functions
[cols="25%,50%,25%"]
|===
| *Name* | *Description* | *Return Data Type*
| <<add_months_function,ADD_MONTHS Function>> | Adds the integer number of months specified by _intr_expr_
to _datetime_expr_ and normalizes the result.
| DATE
| <<converttimestamp_function,CONVERTTIMESTAMP Function>> | Converts a Julian timestamp to a TIMESTAMP value.
| TIMESTAMP
| <<current_function,CURRENT Function>> | Returns the current timestamp. You can also use the
<<current_timestamp_function,CURRENT_TIMESTAMP Function>>.
| TIMESTAMP
| <<current_date_function,CURRENT_DATE Function>> | Returns the current date.
| DATE
| <<current_time_function,CURRENT_TIME Function>> | Returns the current time.
| TIME
| <<current_timestamp_function,CURRENT_TIMESTAMP Function>> | Returns the current timestamp. You can also use the <<current_function,CURRENT Function>>.
| TIMESTAMP
| <<date_add_function,DATE_ADD Function>> | Adds the interval specified by _interval_expression_
to _datetime_expr_.
| DATE
| <<date_part_function_of_an_interval,DATE_PART Function (of an Interval)>> | Extracts the datetime field specified by _text_ from the interval value
specified by interval and returns the result as an exact numeric.
| NUMERIC
| <<date_part_function_of_a_timestamp,DATE_PART Function (of a Timestamp)>> | Extracts the datetime field specified by _text_ from the datetime value
specified by timestamp and returns the result as an exact numeric.
| NUMERIC
| <<date_sub_function,DATE_SUB Function>> | Subtracts the specified _interval_expression_ from
_datetime_expr._
| DATE or TIMESTAMP
| <<date_trunc_function,DATE_TRUNC Function>> | Returns the date with the time portion of the day truncated.
| TIMESTAMP
| <<dateadd_function,DATEADD Function>> | Adds the interval specified by _datepart_ and _num_expr_
to _datetime_expr_.
| DATE or TIMESTAMP
| <<datediff_function,DATEDIFF Function>> | Returns the integer for the number of _datepart_ units of time
between _startdate_ and _enddate_.
| INTEGER
| <<dateformat_function,DATEFORMAT Function>> | Formats a datetime value for display purposes.
| CHAR
| <<day_function,DAY Function>> | Returns an integer in the range 1 through 31 that represents the
corresponding day of the month. You can also use DAYOFMONTH.
| INTEGER (from 1 to 31)
| <<dayname_function,DAYNAME Function>> | Returns the name of the day of the week from a date or timestamp
expression.
| CHAR
| <<dayofmonth_function,DAYOFMONTH Function>> | Returns an integer in the range 1 through 31 that represents the
corresponding day of the month. You can also use DAY.
| INTEGER (from 1 to 31)
| <<dayofweek_function,DAYOFWEEK Function>> | Returns an integer in the range 1 through 7 that represents the
corresponding day of the week.
| INTEGER (from 1 to 7)
| <<dayofyear_function,DAYOFYEAR Function>> | Returns an integer in the range 1 through 366 that represents the
corresponding day of the year.
| INTEGER (from 1 to 366)
| <<extract_function,EXTRACT Function>> | Returns a specified datetime field from a datetime value expression or
an interval value expression.
| NUMERIC
| <<hour_function,HOUR Function>> | Returns an integer in the range 0 through 23 that represents the
corresponding hour of the day.
| INTEGER (from 0 to 23)
| <<juliantimestamp_function,JULIANTIMESTAMP Function>> | Converts a datetime value to a Julian timestamp.
| TIMESTAMP
| <<minute_function,MINUTE Function>> | Returns an integer in the range 0 through 59 that represents the
corresponding minute of the hour.
| INTEGER (from 0 to 59)
| <<month_function,MONTH Function>> | Returns an integer in the range 1 through 12 that represents the
corresponding month of the year.
| INTEGER (from 1 to 12)
| <<monthname_function,MONTHNAME Function>> | Returns a character literal that is the name of the month of the year
(January, February, and so on).
| CHAR
| <<quarter_function,QUARTER Function>> | Returns an integer in the range 1 through 4 that represents the
corresponding quarter of the year.
| INTEGER (from 1 to 4)
| <<second_function,SECOND Function>> | Returns an integer in the range 0 through 59 that represents the
corresponding second of the minute.
| INTEGER (from 0 to 59)
| <<sysdate_function,SYSDATE Function>> | Retrieves the current date of the server rather than the session.
| DATE
| <<systimestamp_function,SYSTIMESTAMP Function>> | Retrieves the current date and time (including fractional seconds with six-digit precision) of the server rather than the session.
| TIMESTAMP
| <<timestampadd_function,TIMESTAMPADD Function>> | Adds the interval of time specified by _interval-ind_ and
_num_expr_ to _datetime_expr_.
| DATE or TIMESTAMP
| <<timestampdiff_function,TIMESTAMPDIFF Function>> | Returns the integer for the number of _interval-ind_units of time between _startdate_ and _enddate_.
| INTEGER
| <<to_char_function,TO_CHAR Function>> | Converts a datetime value to a character value.
| CHAR
| <<to_date_function,TO_DATE Function>> | Converts a character value to a date or timestamp value.
| DATE or TIMESTAMP
| <<to_time_function,TO_TIME Function>> | Converts a character value to a time value.
| TIME
| <<to_timestamp_function,TO_TIMESTAMP Function>> | Converts a character value to a timestamp value.
| TIMESTAMP
| <<week_function,WEEK Function>> | Returns an integer in the range 1 through 54 that represents the
corresponding week of the year.
| INTEGER (from 1 to 54)
| <<year_function,YEAR Function>> | Returns an integer that represents the year.
| INTEGER
|===
See the individual entry for the function.
[[lob_functions]]
== LOB Functions
Trafodion provides following LOB functions to process LOB data.
[cols="25%,75%"]
|===
| <<emptyblob_function,EMPTY_BLOB() Function>> | Creates a dummy LOB handle of type BLOB.
| <<emptyclob_function,EMPTY_CLOB() Function>> | Creates a dummy LOB handle of type CLOB.
| <<stringtolob_function,STRINGTOLOB Function>> | Converts a simple string literal into LOB format.
| <<filetolob_function,FILETOLOB Function>> | Converts data from a local linux/hdfs file into LOB format.
| <<buffertolob_function,BUFFERTOLOB_Function>> | Takes the address and size of an input buffer, and converts the contents of that buffer into LOB format.
| <<externaltolob_function,EXTERNALTOLOB_Function>> | Converts data from an external file into LOB format.
| LOBTOSTRING | Converts LOB data into a simple string literal.
| LOBTOFILE | Converts LOB data into a linux/hdfs file.
| LOBTOBUFFER | Converts LOB data into a buffer.
|===
[[mathematical_functions]]
== Mathematical Functions
Use these mathematical functions within an SQL numeric value expression:
[cols="25%,75%"]
|===
| <<abs_function,ABS Function>> | Returns the absolute value of a numeric value expression.
| <<acos_function,ACOS Function>> | Returns the arccosine of a numeric value expression as an angle expressed in radians.
| <<asin_function,ASIN Function>> | Returns the arcsine of a numeric value expression as an angle expressed in radians.
| <<atan_function,ATAN Function>> | Returns the arctangent of a numeric value expression as an angle expressed in radians.
| <<atan2_function,ATAN2 Function>> | Returns the arctangent of the x and y coordinates, specified by two numeric value expressions, as an angle expressed in radians.
| <<ceiling_function,CEILING Function>> | Returns the smallest integer greater than or equal to a numeric value expression.
| <<cos_function,COS Function>> | Returns the cosine of a numeric value expression, where the expression is an angle expressed in radians.
| <<crc32_function,CRC32 Function>> | Returns CRC32 checksum
| <<cosh_function,COSH Function>> | Returns the hyperbolic cosine of a numeric value expression, where the expression is an angle expressed in radians.
| <<degrees_function,DEGREES Function>> | Converts a numeric value expression expressed in radians to the number of degrees.
| <<exp_function,EXP Function>> | Returns the exponential value (to the base e) of a numeric value expression.
| <<floor_function,FLOOR Function>> | Returns the largest integer less than or equal to a numeric value expression.
| <<log_function,LOG Function>> | Returns the natural logarithm of a numeric value expression.
| <<log10_function,LOG10 Function>> | Returns the base 10 logarithm of a numeric value expression.
| <<mod_function,MOD Function>> | Returns the remainder (modulus) of an integer value expression divided by an integer value expression.
| <<nullifzero_function,NULLIFZERO Function>> | Returns the value of the operand unless it is zero, in which case it returns NULL.
| <<pi_function,PI Function>> | Returns the constant value of pi as a floating-point value.
| <<power_function,POWER Function>> | Returns the value of a numeric value expression raised to the power of an integer value expression. You can also use the exponential operator \*\*.
| <<radians_function,RADIANS Function>> | Converts a numeric value expression expressed in degrees to the number of radians.
| <<round_function,ROUND Function>> | Returns the value of _numeric_expr_ round to _num_ places to the right of the decimal point.
| <<sign_function,SIGN Function>> | Returns an indicator of the sign of a numeric value expression. If value is less than zero, returns -1 as the indicator. If value is zero,
returns 0. If value is greater than zero, returns 1.
| <<sin_function,SIN Function>> | Returns the sine of a numeric value expression, where the expression is an angle expressed in radians.
| <<sinh_function,SINH Function>> | Returns the hyperbolic sine of a numeric value expression, where the expression is an angle expressed in radians.
| <<sqrt_function,SQRT Function>> | Returns the square root of a numeric value expression.
| <<tan_function,TAN Function>> | Returns the tangent of a numeric value expression, where the expression is an angle expressed in radians.
| <<tanh_function,TANH Function>> | Returns the hyperbolic tangent of a numeric value expression, where the expression is an angle expressed in radians.
| <<zeroifnull_function,ZEROIFNULL Function>> | Returns the value of the operand unless it is NULL, in which case it returns zero.
|===
See the individual entry for the function.
[[encryption_functions]]
== Encryption Functions
Use these functions within an SQL value expression to do data encryption or hashing:
[cols="25%,75%"]
|===
| <<md5_function,MD5 Function>> | Returns MD5 checksum
| <<sha_function,SHA Function>> | Returns SHA-1 160-bit checksum
| <<sha2_function,SHA2 Function>> | Returns SHA-2 checksum
|===
[[sequence_functions]]
== Sequence Functions
Sequence functions operate on ordered rows of the intermediate result
table of a SELECT statement that includes a SEQUENCE BY clause. Sequence
functions are categorized generally as difference, moving, offset, or
running.
Some sequence functions, such as ROWS SINCE, require sequentially
examining every row in the history buffer until the result is computed.
Examining a large history buffer in this manner for a condition that has
not been true for many rows could be an expensive operation. In
addition, such operations may not be parallelized because the entire
sorted result set must be available to compute the result of the
sequence function.
[[difference_sequence_functions]]
=== Difference sequence functions
[cols="25%,75%"]
|===
| <<diff1_function,DIFF1 Function>> | Calculates differences between values of a column expression in the current row and previous rows.
| <<diff2_function,DIFF2 Function>> | Calculates differences between values of the result of DIFF1 of the current row and DIFF1 of previous rows.
|===
[[moving_sequence_functions]]
=== Moving sequence functions
[cols="25%,75%"]
|===
| <<movingcount_function,MOVINGCOUNT Function>> | Returns the number of non-null values of a column expression in the current window.
| <<movingmax_function,MOVINGMAX Function>> | Returns the maximum of non-null values of a column expression in the current window.
| <<movingmin_function,MOVINGMIN Function>> | Returns the minimum of non-null values of a column expression in the current window.
| <<movingstddev_function,MOVINGSTDDEV Function>> | Returns the standard deviation of non-null values of a column expression in the current window.
| <<movingsum_function,MOVINGSUM Function>> | Returns the sum of non-null values of a column expression in the current window.
| <<movingvariance_function,MOVINGVARIANCE Function>> | Returns the variance of non-null values of a column expression in the current window.
|===
Offset sequence function
=== Offset sequence function
[cols="25%,75%"]
|===
| <<offset_function,OFFSET Function>> | Retrieves columns from previous rows.
|===
<<<
[[running_sequence_functions]]
=== Running sequence functions
[cols="25%,75%"]
|===
| <<runningavg_function,RUNNINGAVG Function>> | Returns the average of non-null values of a column expression up to and including the current row.
| <<runningcount_function,RUNNINGCOUNT Function>> | Returns the number of rows up to and including the current row.
| <<runningmax_function,RUNNINGMAX Function>> | Returns the maximum of values of a column expression up to and including the current row.
| <<runningmin_function,RUNNINGMIN Function>> | Returns the minimum of values of a column expression up to and including the current row.
| <<runningrank_function,RUNNINGRANK Function>> | Returns the rank of the given value of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement.
| <<runningstddev_function,RUNNINGSTDDEV Function>> | Returns the standard deviation of non-null values of a column expression up to and including the current row.
| <<runningsum_function,RUNNINGSUM Function>> | Returns the sum of non-null values of a column expression up to and including the current row.
| <<runningvariance_function,RUNNINGVARIANCE Function>> | Returns the variance of non-null values of a column expression up to and including the current row.
|===
[[other_sequence_functions]]
=== Other sequence functions
[cols="25%,75%"]
|===
| <<lastnotnull_function,LASTNOTNULL Function>> | Returns the last non-null value for the specified column expression. If only null values have been returned, returns null.
| <<rows_since_function,ROWS SINCE Function>> | Returns the number of rows counted since the specified condition was last true.
| <<rows_since_changed_function,ROWS SINCE CHANGED Function>> | Returns the number of rows counted since the specified set of values last changed.
| <<this_function,THIS Function>> | Used in ROWS SINCE to distinguish between the value of the column in the current row and the value of the column in previous rows.
|===
See <<sequence_by_clause,SEQUENCE BY Clause>> and the individual entry for each function.
<<<
[[other_functions_and_expressions]]
== Other Functions and Expressions
Use these other functions and expressions in an SQL value expression:
[cols="25%,75%"]
|===
| <<authname_function,AUTHNAME Function>> | Returns the authorization name associated with the specified authorization ID number.
| <<bitand_function,BITAND Function>> | Performs 'and' operation on corresponding bits of the two operands.
| <<case_expression,CASE (Conditional) Expression>> | A conditional expression. The two forms of the CASE expression are simple and searched.
| <<cast_expression,CAST Expression>> | Converts a value from one data type to another data type that you specify.
| <<coalesce_function,COALESCE Function>> | Returns the value of the first expression in the list that does not have a NULL value or if all
the expressions have NULL values, the function returns a NULL value.
| <<converttohex_function,CONVERTTOHEX Function>> | Converts the specified value expression to hexadecimal for display purposes.
| <<current_user_function,CURRENT_USER Function>> | Returns the database user name of the current user who invoked the function.
| <<decode_function,DECODE Function>> | Compares _expr_ to each _test_expr_ value one by one in the order provided.
| <<explain_function,EXPLAIN Function>> | Generates a result table describing an access plan for a SELECT, INSERT, DELETE, or UPDATE statement.
| <<isnull_function,ISNULL Function>> | Returns the first argument if it is not null, otherwise it returns the second argument.
| <<is_ipv4_function, IS_IPV4 Function>> | Returns 1 if the argument is a valid IPv4 address specified as a string, 0 otherwise.
| <<is_ipv6_function, IS_IPV6 Function>> | Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise.
| <<inet_aton_function, INET_ATON Function>> | Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument.
| <<inet_ntoa_function, INET_NTOA Function>> | Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a nonbinary string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.
| <<nullif_function,NULLIF Function>> | Returns the value of the first operand if the two operands are not equal, otherwise it returns NULL.
| <<nvl_function,NVL Function>> | Returns the value of the first operand unless it is NULL, in which case it returns the value of the second operand.
| <<user_function,USER Function>> | Returns either the database user name of the current user who invoked the function or the database user name
associated with the specified user ID number.
|===
See the individual entry for the function.
<<<
[[abs_function]]
== ABS Function
The ABS function returns the absolute value of a numeric value
expression. ABS is a {project-name} SQL extension.
```
ABS (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the ABS function. The result is returned as an unsigned
numeric value if the precision of the argument is less than 10 or as a
LARGEINT if the precision of the argument is greater than or equal to
10. See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_abs]]
=== Examples of ABS
* This function returns the value 8:
+
```
ABS (-20 + 12)
```
<<<
[[acos_function]]
== ACOS Function
The ACOS function returns the arccosine of a numeric value expression as
an angle expressed in radians.
ACOS is a {project-name} SQL extension.
```
ACOS (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the ACOS function. The range for the value of the argument is
from -1 to +1. See <<numeric_value_expressions,Numeric Value_Expressions>>.
[[examples_of_acos]]
=== Examples of ACOS
* The ACOS function returns the value 3.49044274380724416E-001 or
approximately 0.3491 in radians (which is 20 degrees).
+
```
ACOS (0.9397)
```
* This function returns the value 0.3491. The function ACOS is the
inverse of the function COS.
+
```
ACOS (COS (0.3491))
```
<<<
[[add_months_function]]
=== ADD_MONTHS Function
The ADD_MONTHS function adds the integer number of months specified by
_int_expr_ to _datetime_expr_ and normalizes the result. ADD_MONTHS is a {project-name} SQL
extension.
```
ADD_MONTHS (datetimeexpr, intexpr [, int2 ])
```
* `_datetime_expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. The return value is the same type as the _datetime_expr._ See
<<datetime_value_expressions,Datetime Value Expressions>>.
* `_int_expr_`
+
is an SQL numeric value expression of data type SMALLINT or INTEGER that
specifies the number of months. See <<numeric_value_expressions,
Numeric Value Expressions>>.
* `_int2_`
+
is an unsigned integer constant. If _int2_ is omitted or is the literal
0, the normalization is the standard normalization. If _int2_ is the
literal 1, the normalization includes the standard normalization and if
the starting day (the day part of _datetime_expr_) is the last day of
the starting month, then the ending day (the day part of the result
value) is set to the last valid day of the result month. See
<<standard_normalization,Standard Normalization>>. See
<<numeric_value_expressions,Numeric Value Expressions>> .
<<<
[[examples_of_add_months]]
=== Examples of ADD_MONTHS
* This function returns the value DATE '2007-03-31':
+
```
ADD_MONTHS(DATE '2007-02-28', 1, 1)
```
* This function returns the value DATE '2007-03-28':
+
```
ADD_MONTHS(DATE '2007-02-28', 1, 0)
```
* This function returns the value DATE '2008-03-28':
+
```
ADD_MONTHS(DATE '2008-02-28', 1, 1)
```
* This function returns the timestamp '2009-02-28 00:00:00':
+
```
ADD_MONTHS(timestamp'2008-02-29 00:00:00',12,1)
```
<<<
[[ascii_function]]
== ASCII Function
The ASCII function returns the integer that is the ASCII code of the
first character in a character string expression associated with
the ISO88591 character set or the UTF8 character set.
ASCII is a {project-name} SQL extension.
```
ASCII (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression that specifies a string of
characters.
+
For more information, see <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_ascii]]
=== Considerations For ASCII
The ASCII function handles string literals and columns differently:
* For a string, the value of the character or *the value of each character* in the string cannot be greater than 255, or else it returns the following error:
+
```
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591
```
+
*Example*
+
```
SQL>SELECT ASCII('ÿ') FROM DUAL;
(EXPR)
----------
255
--- 1 row(s) selected.
```
+
```
SQL>SELECT ASCII('u') FROM DUAL;
(EXPR)
----------
117
--- 1 row(s) selected.
```
+
```
SQL>SELECT ASCII('ÿu') FROM DUAL;
(EXPR)
----------
255
--- 1 row(s) selected.
```
+
```
SQL>SELECT ASCII('昱') FROM DUAL;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591. [2018-05-07 06:01:55]
```
+
```
SQL>SELECT ASCII('ÿu昱') FROM DUAL;
*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591. [2018-05-08 08:21:34]
```
* For a column, the string expression(s) in this column can be only single-byte, or else it returns the following error:
+
```
*** ERROR[8428] The argument to function ASCII is not valid.
```
+
** For a string in a column with definition of UTF8, the value of the first byte in the string cannot be greater than 127.
** For a string in a column with definition of ISO88591, the value of the first byte in the string can be all characters in the ISO88591 character set.
+
*Example*
+
.UTF8 (with and without error) -- Table with One Row
[cols="10%,45%,45%"]
|===
| *Column Definition* | *UTF8* | *UTF8*
| *Table Structure*
|
`SQL>SHOWDDL t25;
CREATE TABLE TRAFODION.SEABASE.T25
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T25 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
|
`SQL>SHOWDDL t24;
CREATE TABLE TRAFODION.SEABASE.T24
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T24 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
| *Data*
|
`SQL>SELECT * FROM t25;
C1
----
昱u
--- 1 row(s) selected.`
|
`SQL>SELECT * FROM t24;
C1
----
u昱
--- 1 row(s) selected.`
| *Result*
|
`SQL>SELECT ASCII(c1) FROM t25;
***ERROR[8428] The argument to function ASCII is not valid. [2018-05-08 10:14:08]`
|
`SQL>SELECT ASCII(c1) FROM t24;
(EXPR)
----------
117
--- 1 row(s) selected.`
|===
+
.UTF8 (with and without error) -- Table with Two Rows
[cols="10%,45%,45%"]
|===
| *Column Definition* | *UTF8* | *UTF8*
| *Table Structure*
|
`SQL>SHOWDDL t26;
CREATE TABLE TRAFODION.SEABASE.T26
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T26 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
|
`SQL>SHOWDDL t27;
CREATE TABLE TRAFODION.SEABASE.T27
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T27 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
| *Data*
|
`SQL>SELECT * FROM t26;
C1
----
u昱
昱u
--- 2 row(s) selected.`
|
`SQL>SELECT * FROM t27;
C1
----
u昱
u昱
--- 2 row(s) selected.`
| *Result*
|
`SQL>SELECT ASCII(c1) FROM t26;
***ERROR[8428] The argument to function ASCII is not valid. [2018-05-08 10:29:03]`
|
`SQL>SELECT ASCII(c1) FROM t27;
(EXPR)
----------
117
117
--- 2 row(s) selected.`
|===
+
.Comparison between UTF8 and ISO88591 (with error) -- Table with One Row
[cols="10%,45%,45%"]
|===
| *Column Definition* | *UTF8* | *ISO99591*
| *Table Structure*
|
`SQL>SHOWDDL t11;
CREATE TABLE TRAFODION.SEABASE.T11
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T11 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
|
`SQL>SHOWDDL t12;
CREATE TABLE TRAFODION.SEABASE.T12
(
C1 CHAR(4) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T12 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
| *Data*
|
`SQL>SELECT * FROM t11;
C1
----
ÿu
--- 1 row(s) selected.`
|
`SQL>SELECT * FROM t12;
C1
----
ÿu
--- 1 row(s) selected.`
| *Result*
|
`SQL>SELECT ASCII(c1) FROM t1;
***ERROR[8428] The argument to function ASCII is not valid. [2018-05-07 02:13:42]`
|
`SQL>SELECT ASCII(c1) FROM t2;
(EXPR)
----------
255
--- 1 row(s) selected.`
|===
+
.Comparison between UTF8 and ISO88591 (without error) -- Table with One Row
[cols="10%,45%,45%"]
|===
| *Column Definition* | *UTF8* | *ISO99591*
| *Table Structure*
|
`SQL>SHOWDDL t17;
CREATE TABLE TRAFODION.SEABASE.T17
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T17 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
|
`SQL>SHOWDDL t18;
CREATE TABLE TRAFODION.SEABASE.T18
(
C1 CHAR(4) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T18 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
| *Data*
|
`SQL>SELECT * FROM t17;
C1
----
uÿ
--- 1 row(s) selected.`
|
`SQL>SELECT * FROM t18;
C1
----
uÿ
--- 1 row(s) selected.`
| *Result*
|
`SQL>SELECT ASCII(c1) FROM t17;
(EXPR)
----------
117
--- 1 row(s) selected.`
|
`SQL>SELECT ASCII(c1) FROM t18;
(EXPR)
----------
117
--- 1 row(s) selected.`
|===
+
.Comparison between UTF8 and ISO88591 (with error) -- Table with Two Rows
[cols="10%,45%,45%"]
|===
| *Column Definition* | *UTF8* | *ISO99591*
| *Table Structure*
|
`SQL>SHOWDDL t22;
CREATE TABLE TRAFODION.SEABASE.T22
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T22 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
|
`SQL>SHOWDDL t23;
CREATE TABLE TRAFODION.SEABASE.T23
(
C1 CHAR(4) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T23 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
| *Data*
|
`SQL>SELECT * FROM t22;
C1
----
uÿ
ÿu
--- 2 row(s) selected.`
|
`SQL>SELECT * FROM t23;
C1
----
uÿ
ÿu
--- 2 row(s) selected.`
| *Result*
|
`SQL>SELECT ASCII(c1) FROM t22;
*** ERROR[8428] The argument to function ASCII is not valid. [2018-05-08 10:01:29]`
|
`SQL>SELECT ASCII(c1) FROM t23;
(EXPR)
----------
117
255
--- 2 row(s) selected.`
|===
+
.Comparison between UTF8 and ISO88591 (without error) -- Table with Two Rows
[cols="10%,45%,45%"]
|===
| *Column Definition* | *UTF8* | *ISO99591*
| *Table Structure*
|
`SQL>SHOWDDL t20;
CREATE TABLE TRAFODION.SEABASE.T20
(
C1 CHAR(4 CHARS) CHARACTER SET UTF8 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
-- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T20 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
|
`SQL>SHOWDDL t21;
CREATE TABLE TRAFODION.SEABASE.T21
(
C1 CHAR(4) CHARACTER SET ISO88591 COLLATE
DEFAULT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- GRANT SELECT, INSERT, DELETE, UPDATE, REFERENCES ON TRAFODION.SEABASE.T21 TO DB__ROOT WITH GRANT OPTION;
--- SQL operation complete.`
| *Data*
|
`SQL>SELECT * FROM t20;
C1
----
uÿ
uÿ
--- 2 row(s) selected.`
|
`SQL>SELECT * FROM t21;
C1
----
uÿ
uÿ
--- 2 row(s) selected.`
| *Result*
|
`SQL>SELECT ASCII(c1) FROM t20;
(EXPR)
----------
117
117
--- 2 row(s) selected.`
|
`SQL>SELECT ASCII(c1) FROM t21;
(EXPR)
----------
117
117
--- 2 row(s) selected.`
|===
[[examples_of_ascii]]
=== Examples of ASCII
* Select the column JOBDESC and return the ASCII code of the first
character of the job description:
+
```
SELECT jobdesc, ASCII (jobdesc) FROM persnl.job;
JOBDESC (EXPR)
----------------- --------
MANAGER 77
PRODUCTION SUPV 80
ASSEMBLER 65
SALESREP 83
... ...
--- 10 row(s) selected.
```
<<<
[[asin_function]]
== ASIN Function
The ASIN function returns the arcsine of a numeric value expression as
an angle expressed in radians.
ASIN is a {project-name} SQL extension.
```
ASIN (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the ASIN function. The range for the value of the argument is
from -1 to +1. See <<numeric_value_expressions,Numeric Value Expressions>>.
[[considerations_for_ascii]]
=== Considerations for ASCII
For a string expression in the UTF8 character set, if the value of the
first byte in the string is greater than 127, {project-name} SQL returns this
error message:
```
ERROR[8428] The argument to function ASCII is not valid.
```
[[examples_of_ascii]]
=== Examples of ASCII
* Select the column JOBDESC and return the ASCII code of the first
character of the job description:
+
```
SELECT jobdesc, ASCII (jobdesc) FROM persnl.job;
JOBDESC (EXPR)
----------------- --------
MANAGER 77
PRODUCTION SUPV 80
ASSEMBLER 65
SALESREP 83
... ...
--- 10 row(s) selected.
```
<<<
[[asin_function]]
== ASIN Function
The ASIN function returns the arcsine of a numeric value expression as
an angle expressed in radians.
ASIN is a {project-name} SQL extension.
```
ASIN (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the ASIN function. The range for the value of the argument
is from -1 to +1. See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_asin]]
=== Examples of ASIN
* This function returns the value 3.49044414403046400e-001 or
approximately 0.3491 in radians (which is 20 degrees):
+
```
ASIN(0.3420)
```
* This function returns the value 0.3491. The function ASIN is the
inverse of the function SIN.
+
```
ASIN(SIN(0.3491))
```
<<<
[[atan_function]]
== ATAN Function
The ATAN function returns the arctangent of a numeric value expression
as an angle expressed in radians.
ATAN is a {project-name} SQL extension.
```
ATAN ( numeric-expression )
```
* `_numeric-expression _`
is an SQL numeric value expression that specifies the value for the
argument of the atan function. See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_atan]]
=== Examples of ATAN
* This function returns the value 8.72766423249958272E-001 or
approximately 0.8727 in radians (which is 50 degrees):
+
```
ATAN (1.192)
```
* This function returns the value 0.8727. The function ATAN is the
inverse of the function TAN.
+
```
ATAN (TAN (0.8727))
```
<<<
[[atan2_function]]
== ATAN2 Function
The ATAN2 function returns the arctangent of the x and y coordinates,
specified by two numeric value expressions, as an angle expressed in
radians.
ATAN2 is a {project-name} SQL extension.
```
ATAN2 (numeric-expression-x,numeric-expression-y)
```
* `_numeric-expression-x_, _numeric-expression-y_`
are SQL numeric value expressions that specify the value for the x and y
coordinate arguments of the ATAN2 function. See
<<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_atan2]]
=== Examples of ATAN2
* This function returns the value 2.66344329881899520E+000, or
approximately 2.6634:
+
```
ATAN2 (1.192,-2.3)
```
<<<
[[authname_function]]
== AUTHNAME Function
The AUTHNAME function returns the name of the authorization ID that is
associated with the specified authorization ID number.
```
AUTHNAME (auth-id)
```
* `_auth-id_`
+
is the 32-bit number associated with an authorization ID. See
<<authorization_ids,Authorization IDs>>.
The AUTHNAME function is similar to the <<user function,USER Function>>.
[[considerations_for_authname]]
=== Considerations for AUTHNAME
* This function can be specified only in the top level of a SELECT statement.
* The value returned is string data type VARCHAR(128) and is in ISO88591 encoding.
[[examples_of_authname]]
=== Examples of AUTHNAME
* This example shows the authorization name associated with the
authorization ID number, 33333:
+
```
>>SELECT AUTHNAME (33333) FROM (values(1)) x(a);
(EXPR)
-------------------------
DB ROOT
--- 1 row(s) selected.
```
<<<
[[avg_function]]
== AVG Function
AVG is an aggregate function that returns the average of a set of
numbers.
```
AVG ([ALL | DISTINCT] expression)
```
* `ALL | DISTINCT`
+
specifies whether duplicate values are included in the computation of
the AVG of the _expression_. The default option is ALL, which causes
duplicate values to be included. If you specify DISTINCT, duplicate
values are eliminated before the AVG function is applied.
* `_expression_`
+
specifies a numeric or interval value _expression_ that determines the
values to average. The _expression_ cannot contain an aggregate function
or a subquery. The DISTINCT clause specifies that the AVG function
operates on distinct values from the one-column table derived from the
evaluation of _expression_.
See <<numeric_value_expressions,Numeric Value Expressions>> and
<<interval_value_expressions,Interval Value Expressions>>.
[[considerations_for_avg]]
=== Considerations for AVG
[[data-type-of-the-result]]
==== Data Type of the Result
The data type of the result depends on the data type of the argument. If
the argument is an exact numeric type, the result is LARGEINT. If the
argument is an approximate numeric type, the result
is DOUBLE PRECISION. If the argument is INTERVAL data type, the result
is INTERVAL with the same precision as the argument.
The scale of the result is the same as the scale of the argument. If the
argument has no scale, the result is truncated.
[[operands-of-the-expression]]
==== Operands of the Expression
The expression includes columns from the rows of the SELECT result table but
cannot include an aggregate function. These expressions are valid:
```
AVG (SALARY)
AVG (SALARY * 1.1)
AVG (PARTCOST * QTY_ORDERED)
```
[[avg_nulls]]
==== Nulls
All nulls are eliminated before the function is applied to the set of
values. If the result table is empty, AVG returns NULL.
[[examples_of_avg]]
==== Examples of AVG
* Return the average value of the SALARY column:
+
```
SELECT AVG (salary) FROM persnl.employee;
(EXPR)
---------------------
49441.52
--- 1 row(s) selected.
```
* Return the average value of the set of unique SALARY values:
+
```
SELECT AVG(DISTINCT salary) AS Avg_Distinct_Salary FROM persnl.employee;
AVG_DISTINCT_SALARY
---------------------
53609.89
--- 1 row(s) selected.
```
* Return the average salary by department:
+
```
SELECT deptnum, AVG (salary) AS "AVERAGE SALARY"
FROM persnl.employee
WHERE deptnum < 3000 GROUP BY deptnum;
Dept/Num "AVERAGE SALARY"
-------- ---------------------
1000 52000.17
2000 50000.10
1500 41250.00
2500 37000.00
--- 4 row(s) selected.
```
<<<
[[bitand_function]]
== BITAND Function
The BITAND function performs an AND operation on corresponding bits of
the two operands. If both bits are 1, the result bit is 1. Otherwise the
result bit is 0.
```
BITAND (expression, expression)
```
* `_expression_`
+
The result data type is a binary number. Depending on the precision of
the operands, the data type of the result can either be an INT (32-bit
integer) or a LARGEINT (64-bit integer).
+
If the max precision of either operand is greater than 9, LARGEINT is
chosen (numbers with precision greater than 9 are represented by
LARGEINT). Otherwise, INT is chosen.
+
If both operands are unsigned, the result is unsigned. Otherwise, the
result is signed. Both operands are converted to the result data type
before performing the bit operation.
[[considerations_for_bitand]]
=== Considerations for BITAND
BITAND can be used anywhere in an SQL query where an expression could be
used. This includes SELECT lists, WHERE predicates, VALUES clauses, SET
statement, and so on.
This function returns a numeric data type and can be used in arithmetic
expressions.
Numeric operands can be positive or negative numbers. All numeric data
types are allowed with the exceptions listed in the
<<restrictions_for_bitand,Restrictions for BITAND>> section.
[[restrictions_for_bitand]]
==== Restrictions for BITAND
The following are BITAND restrictions:
* Must have two operands
* Operands must be binary or decimal exact numerics
* Operands must have scale of zero
* Operands cannot be floating point numbers
* Operands cannot be an extended precision numeric (the maximum precision of an extended numeric data type is 128)
[[examples_of_bitand]]
=== Examples of BITAND
```
>>select bitand(1,3) from (values(1)) x(a);
(EXPR)
--------------
1
--- 1 row(s) selected
>>select 1 & 3 from (values(1)) x(a);
(EXPR)
--------------
1
--- 1 row(s) selected
>>select bitand(1,3) + 0 from (values(1)) x(a);
(EXPR)
--------------
1
--- 1 row(s) selected
```
<<<
[[buffertolob_function]]
== BUFFERTOLOB Function
[[buffertolob_function_syntax]]
=== Syntax Descriptions of BUFFERTOLOB Function
The BUFFERTOLOB function takes the address and size of an input buffer, and converts the contents of that buffer into LOB. This function can be used in an INSERT or UPDATE statement.
BUFFERTOLOB function is a Trafodion SQL extension.
For more information, see http://trafodion.apache.org/docs/lob_guide/index.html[Trafodion SQL Large Objects Guide].
```
BUFFERTOLOB(LOCATION lob source buffer address, LENGTH lob length value)
```
* _lob source buffer address_
+
The int64 value of the user buffer address.
* _lob length value_
+
The int64 value of the user specified lob buffer.
[[buffertolob_function_examples]]
=== Examples of BUFFERTOLOB Function
* This example takes an int64 value as an input which is an address to a buffer and a size parameter. The buffer contents are converted to LOB format and stored in HDFS.
+
```
insert into tlob1 values (1, buffertolob(LOCATION 124647474, SIZE 2048));
```
* In the table tlob1, this example updates (overwrites) the c2 to the buffer location at 1254674 with 4000-byte length.
+
```
update tlob1 set c2=buffertolob(LOCATION 12546474, SIZE 4000);
```
<<<
[[case_expression]]
== CASE (Conditional) Expression
The CASE expression is a conditional expression with two forms: simple
and searched.
In a simple CASE expression, {project-name} SQL compares a value to a
sequence of values and sets the CASE expression to the value associated
with the first match &#8212; if a match exists. If no match exists, {project-name}
SQL returns the value specified in the ELSE clause (which can be null).
In a searched CASE expression, {project-name} SQL evaluates a sequence of
conditions and sets the CASE expression to the value associated with the
first condition that is true &#8212; if a true condition exists. If no true
condition exists, {project-name} SQL returns the value specified in the ELSE
clause (which can be null).
*Simple CASE is*:
```
CASE case-expression
WHEN expression-1 THEN {result-expression-1 | NULL}
WHEN expression-2 THEN {result-expression-2 | NULL}
...
WHEN expression-n THEN {result-expression-n | NULL}
[ELSE {result-expression | NULL}]
END
```
*Searched CASE is*:
```
CASE
WHEN _condition-1_ THEN {_result-expression-1_ | NULL}
WHEN _condition-2_ THEN {_result-expression-2_ | NULL}
...
WHEN _condition-n_ THEN {_result-expression-n_ | NULL}
[ELSE {_result-expression_ | NULL}]
END
```
* `_case-expression_`
+
specifies a value expression that is compared to the value expressions
in each WHEN clause of a simple CASE. The data type of each _expression_
in the WHEN clause must be comparable to the data type of
_case-expression_.
* `_expression-1_ &#8230; _expression-n_`
+
specifies a value associated with each _result-expression_. If the
value of an _expression_ in a WHEN clause matches the value of
_case-expression_, simple CASE returns the associated
_result-expression_ value. If no match exists, the CASE expression
returns the value expression specified in the ELSE clause, or NULL if
the ELSE value is not specified.
* `_result-expression-1_ &#8230; _result-expression-n_`
+
specifies the result value expression associated with each _expression_
in a WHEN clause of a simple CASE, or with each _condition_ in a WHEN
clause of a searched CASE. All of the _result-expressions_ must have
comparable data types, and at least one of the
_result-expressions_ must return non-null.
* `_result-expression_`
+
follows the ELSE keyword and specifies the value returned if none of the
expressions in the WHEN clause of a simple CASE are equal to the case
expression, or if none of the conditions in the WHEN clause of a
searched CASE are true. If the ELSE _result-expression_ clause is not
specified, CASE returns NULL. The data type of _result-expression_ must
be comparable to the other results.
* `_condition-1_ &#8230; _condition-n_`
specifies conditions to test for in a searched CASE. If a _condition_ is
true, the CASE expression returns the associated _result-expression_
value. If no _condition_ is true, the CASE expression returns the value
expression specified in the ELSE clause, or NULL if the ELSE value is
not specified.
[[considerations_for_case]]
=== Considerations for CASE
[[data_type_of_the_case_expression]]
==== Data Type of the CASE Expression
The data type of the result of the CASE expression depends on the data
types of the result expressions. If the results all have the same data
type, the CASE expression adopts that data type. If the results have
comparable but not identical data types, the CASE expression adopts the
data type of the union of the result expressions. This result data type
is determined in these ways.
[[character_data_type]]
==== Character Data Type
If any data type of the result expressions is variable-length character
string, the result data type is variable-length character string with
maximum length equal to the maximum length of the result expressions.
Otherwise, if none of the data types is variable-length character
string, the result data type is fixed-length character string with length
equal to the maximum of the lengths of the result expressions.
[[numeric_data_type]]
==== Numeric Data Type
If all of the data types of the result expressions are exact numeric,
the result data type is exact numeric with precision and scale equal to
the maximum of the precisions and scales of the result expressions.
For example, if _result-expression-1_ and _result-expression-2_ have
data type NUMERIC(5) and _result-expression-3_ has data type
NUMERIC(8,5), the result data type is NUMERIC(10,5).
If any data type of the result expressions is approximate numeric, the
result data type is approximate numeric with precision equal to the
maximum of the precisions of the result expressions.
[[datetime_data_type]]
==== Datetime Data Type
If the data type of the result expressions is datetime, the result data
type is the same datetime data type.
[[interval_data_type]]
==== Interval Data Type
If the data type of the result expressions is interval, the result data
type is the same interval data type (either year-month or day-time) with
the start field being the most significant of the start fields of the
result expressions and the end field being the least significant of the
end fields of the result expressions.
[[examples_of_case]]
=== Examples of CASE
* Use a simple CASE to decode JOBCODE and return NULL if JOBCODE does
not match any of the listed values:
+
```
SELECT
last_name
, first_name
, CASE jobcode
WHEN 100 THEN 'MANAGER'
WHEN 200 THEN 'PRODUCTION SUPV'
WHEN 250 THEN 'ASSEMBLER'
WHEN 300 THEN 'SALESREP'
WHEN 400 THEN 'SYSTEM ANALYST'
WHEN 420 THEN 'ENGINEER'
WHEN 450 THEN 'PROGRAMMER'
WHEN 500 THEN 'ACCOUNTANT'
WHEN 600 THEN 'ADMINISTRATOR ANALYST'
WHEN 900 THEN 'SECRETARY'
ELSE NULL
END
FROM persnl.employee;
LAST_NAME FIRST_NAME (EXPR)
-------------------- --------------- -----------------
GREEN ROGER MANAGER
HOWARD JERRY MANAGER
RAYMOND JANE MANAGER
...
CHOU JOHN SECRETARY
CONRAD MANFRED PROGRAMMER
HERMAN JIM SALESREP
CLARK LARRY ACCOUNTANT
HALL KATHRYN SYSTEM ANALYST
...
--- 62 row(s) selected.
```
* Use a searched CASE to return LAST_NAME, FIRST_NAME and a value based
on SALARY that depends on the value of DEPTNUM:
+
```
SELECT
last_name
, first_name
, deptnum
, CASE
WHEN deptnum = 9000 THEN salary * 1.10
WHEN deptnum = 1000 THEN salary * 1.12 ELSE salary
END
FROM persnl.employee;
LAST_NAME FIRST_NAME DEPTNUM (EXPR)
---------------- ------------ ------- -------------------
GREEN ROGER 9000 193050.0000
HOWARD JERRY 1000 153440.1120
RAYMOND JANE 3000 136000.0000
...
--- 62 row(s) selected.
```
<<<
[[cast_expression]]
== CAST Expression
The CAST expression converts data to the data type you specify.
```
CAST ({expression | NULL} AS data-type)
```
* `_expression_ | NULL`
+
specifies the operand to convert to the data type _data-type_.
+
If the operand is an _expression_, then _data-type_ depends on the
data type of _expression_ and follows the rules outlined in
<<valid_conversions_for_cast,Valid Conversions for CAST >>.
+
If the operand is NULL, or if the value of the _expression_ is null, the
result of CAST is NULL, regardless of the data type you specify.
* `_data-type_`
+
specifies a data type to associate with the operand of CAST. See
<<data_types,Data Types>>.
+
When casting data to a CHAR or VARCHAR data type, the resulting data
value is left justified. Otherwise, the resulting data value is right
justified. Further, when you are casting to a CHAR or VARCHAR data type,
you must specify the length of the target value.
[[considerations_for_cast]]
=== Considerations for CAST
* Fractional portions are discarded when you use CAST of a numeric value to an INTERVAL type.
* Depending on how your file is set up, using CAST might cause poor
query performance by preventing the optimizer from choosing the most
efficient plan and requiring the executor to perform a complete table or
index scan.
[[valid_conversions_for_cast]]
==== Valid Conversions for CAST
* An exact or approximate numeric value to any other numeric data type.
* An exact or approximate numeric value to any character string data type.
* An exact numeric value to either a single-field year-month or day-time interval such as INTERVAL DAY(2).
* A character string to any other data type, with one restriction:
The contents of the character string to be converted must be consistent
in meaning with the data type of the result. For example, if you are
converting to DATE, the contents of the character string must be 10
characters consisting of the year, a hyphen, the month, another hyphen,
and the day.
* A date value to a character string or to a TIMESTAMP ({project-name} SQL fills in the time part with 00:00:00.00).
* A time value to a character string or to a TIMESTAMP ({project-name} SQL fills in the date part with the current date).
* A timestamp value to a character string, a DATE, a TIME, or another TIMESTAMP with different fractional seconds precision.
* A year-month interval value to a character string, an exact numeric,
or to another year-month INTERVAL with a different start field precision.
* A day-time interval value to a character string, an exact numeric, or
to another day-time INTERVAL with a different start field precision.
[[examples_of_cast]]
=== Examples of CAST
* In this example, the fractional portion is discarded:
+
```
CAST (123.956 as INTERVAL DAY(18))
```
* This example returns the difference of two timestamps in minutes:
+
```
CAST((d.step_end - d.step_start) AS INTERVAL MINUTE)
```
* Suppose that your database includes a log file of user information.
This example converts the current timestamp to a character string and
concatenates the result to a character literal. Note the length must be
specified.
+
```
INSERT INTO stats.logfile (user_key, user_info)
VALUES (001, 'User JBrook, executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26)));
```
<<<
[[ceiling_function]]
== CEILING Function
The CEILING function returns the smallest integer, represented as a
FLOAT data type, greater than or equal to a numeric value expression.
CEILING is a {project-name} SQL extension.
```
CEILING (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the CEILING function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_ceiling]]
=== Examples of CEILING
* This function returns the integer value 3.00000000000000000E+000,
represented as a FLOAT data type:
+
```
CEILING (2.25)
```
<<<
[[char_function]]
=== CHAR Function
The CHAR function returns the character that has the specified code
value, which must be of exact numeric with scale 0.
CHAR is a {project-name} SQL extension.
```
CHAR(code-value, [,char-set-name])
```
* `_code-value_`
+
is a valid code value in the character set in use.
* `_char-set-name_`
+
can be ISO88591 or UTF8. The returned character will be associated with
the character set specified by _char-set-name_.
+
The default for _char-set-name_ is ISO88591.
[[considerations_for_char]]
=== Considerations for CHAR
* For the ISO88591 character set, the return type is VARCHAR(1).
* For the UTF8 character set, the return type is VARCHAR(1).
[[examples_of_char]]
=== Examples of CHAR
* Select the column CUSTNAME and return the ASCII code of the first
character of the customer name and its CHAR value:
+
```
SELECT custname, ASCII (custname), CHAR (ASCII (custname))
FROM sales.customer;
CUSTNAME (EXPR) ( EXPR)
------------------ ------- -------
CENTRAL UNIVERSITY 67 C
BROWN MEDICAL CO 66 B
STEVENS SUPPLY 83 S
PREMIER INSURANCE 80 P
... ... ...
--- 15 row(s) selected.
```
<<<
[[char_length_function]]
== CHAR_LENGTH Function
The CHAR_LENGTH function returns the number of characters in a string.
You can also use CHARACTER_LENGTH. Every character, including multi-byte
characters, counts as one character.
```
CHAR[ACTER]_LENGTH (string-value-expression)
```
* `_string-value-expression_`
+
specifies the string value expression for which to return the length in
characters. {project-name} SQL returns the result as a two-byte signed
integer with a scale of zero. If _string-value-expression_ is null,
{project-name} SQL returns a length of
null. See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_char_length]]
=== Considerations for CHAR_LENGTH
[[char_and_varchar_operands]]
==== CHAR and VARCHAR Operands
For a column declared as fixed CHAR, {project-name} SQL returns the maximum
length of that column. For a VARCHAR column, {project-name} SQL returns the
actual length of the string stored in that column.
[[examples_of_char_length]]
=== Examples of CHAR_LENGTH
* This function returns 12 as the result. The concatenation operator is
denoted by two vertical bars (\|\|).
+
```
CHAR_LENGTH ('ROBERT' || ' ' || 'SMITH')
```
* The string '' is the null (or empty) string. This function returns 0
(zero):
+
```
CHAR_LENGTH ('')
```
* The DEPTNAME column has data type CHAR(12). Therefore, this function
always returns 12:
+
```
CHAR_LENGTH (deptname)
```
* The PROJDESC column in the PROJECT table has data type VARCHAR(18).
This function returns the actual length of the column value &#8212; not 18 for
shorter strings &#8212; because it is a VARCHAR value:
+
```
SELECT CHAR_LENGTH (projdesc) FROM persnl.project;
(EXPR)
----------
14
13
13
17
9
9
--- 6 row(s) selected.
```
<<<
[[coalesce_function]]
== COALESCE Function
The COALESCE function returns the value of the first expression in the
list that does not have a NULL value or if all the expressions have NULL
values, the function returns a NULL value.
```
COALESCE (expr1, expr2, ...)
```
* `_expr1_`
+
an expression to be compared.
* `_expr2_`
+
an expression to be compared.
[[examples_of_coalesce]]
=== Examples of COALESCE
* COALESCE returns the value of the first operand that is not NULL:
+
```
SELECT COALESCE (office_phone, cell_phone, home_phone, pager, fax_num, '411')
from emptbl;
```
<<<
[[code_value_function]]
== CODE_VALUE Function
The CODE_VALUE function returns an unsigned integer (INTEGER UNSIGNED)
that is the code point of the first character in a character value
expression that can be associated with one of the supported character
sets.
CODE_VALUE is a {project-name} SQL extension.
```
CODE_VALUE(character-value-expression)
character-set
```
* `_character-value-expression_`
+
is a character string.
[[examples_of_code_value_function]]
=== Examples of CODE_VALUE Function
* This function returns 97 as the result:
+
```
>>select code_value('abc') from (values(1))x;
(EXPR)
----------
97
```
<<<
[[concat_function]]
== CONCAT Function
The CONCAT function returns the concatenation of two character value
expressions as a character string value. You can also use the
concatenation operator (\|\|).
CONCAT is a {project-name} SQL extension.
```
CONCAT (character-expr-1, character-expr-2)
```
* `_character-expr-1_, _character-expr-2_`
+
are SQL character value expressions (of data type CHAR or VARCHAR) that
specify two strings of characters. Both character value expressions must
be either ISO88591 character expressions or UTF8 character expressions.
The result of the CONCAT function is the concatenation of
_character-expr-1_ with _character-expr-2_. The result type is CHAR if
both expressions are of type CHAR and it is VARCHAR if either of the
expressions is of type VARCHAR.
See <<character_value_expressions,Character Value Expressions>>.
[[concatenation_operator]]
=== Concatenation Operator (||)
The concatenation operator, denoted by two vertical bars (||),
concatenates two string values to form a new string value. To indicate
that two strings are concatenated, connect the strings with two vertical
bars (\|\|):
```
character-expr-1 || character-expr-2
```
An operand can be any SQL value expression of data type CHAR or VARCHAR.
[[considerations_for_concat]]
=== Considerations for CONCAT
[[operands]]
==== Operands
A string value can be specified by any character value expression, such
as a character string literal, character string function, column
reference, aggregate function, scalar subquery, CASE expression, or CAST
expression. The value of the operand must be of type CHAR or VARCHAR.
If you use the CAST expression, you must specify the length of CHAR or
VARCHAR.
[[sql-parameters]]
==== SQL Parameters
You can concatenate an SQL parameter and a character value expression.
The concatenated parameter takes on the data type attributes of the
character value expression. Consider this example, where ?p is assigned
a string value of '5 March':
?p || ' 2002'
The type assignment of the parameter ?p becomes CHAR(5), the same data
type as the character literal ' 2002'. Because you assigned a string
value of more than five characters to ?p, {project-name} SQL returns a
truncation warning, and the result of the concatenation is 5 Mar 2002.
To specify the type assignment of the parameter, use the CAST expression
on the parameter as:
CAST(?p AS CHAR(7)) || '2002'
In this example, the parameter is not truncated, and the result of the
concatenation is 5 March 2002.
[[examples_of_concat]]
=== Examples of CONCAT
* Insert information consisting of a single character string. Use the
CONCAT function to construct and insert the value:
+
```
INSERT INTO stats.logfile (user_key, user_info)
VALUES (001, CONCAT ('Executed at ', CAST (CURRENT_TIMESTAMP AS CHAR(26))));
```
* Use the concatenation operator || to construct and insert the value:
+
```
INSERT INTO stats.logfile (user_key, user_info)
VALUES (002, 'Executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26)));
```
<<<
[[converttohex_function]]
== CONVERTTOHEX Function
The CONVERTTOHEX function converts the specified value expression to
hexadecimal for display purposes.
CONVERTTOHEX is a {project-name} SQL extension.
```
CONVERTTOHEX (expression)
```
_expression_
is any numeric, character, datetime, or interval expression.
The primary purpose of the CONVERTTOHEX function is to eliminate any
doubt as to the exact value in a column. It is particularly useful for
character expressions where some characters may be from character sets
that are not supported by the client terminal's locale or may be control
codes or other non-displayable characters.
[[considerations_for_converttohex]]
=== Considerations for CONVERTTOHEX
Although CONVERTTOHEX is usable on datetime and interval expressions,
the displayed output shows the internal value and is, consequently, not
particularly meaningful to general users and is subject to change in
future releases.
CONVERTTOHEX returns ASCII characters in ISO88591 encoding.
<<<
[[examples_of_converttohex]]
=== Examples of CONVERTTOHEX
* Display the contents of a smallint, integer, and largeint in
hexadecimal:
+
```
CREATE TABLE EG (S1 smallint, I1 int, L1 largeint);
INSERT INTO EG VALUES( 37, 2147483647, 2305843009213693951);
SELECT CONVERTTOHEX(S1), CONVERTTOHEX(I1), CONVERTTOHEX(L1) from EG;
(EXPR) (EXPR) EXPR)
------ -------- ----------------
0025 7FFFFFFF 1FFFFFFFFFFFFFFF
```
* Display the contents of a CHAR(4) column, a VARCHAR(4) column, and a
CHAR(4) column that uses the UTF8 character set. The varchar column does
not have a trailing space character as the fixed-length columns have:
+
```
CREATE TABLE EG_CH (FC4 CHAR(4), VC4 VARCHAR(4), FC4U CHAR(4) CHARACTER SET UTF8);
INSERT INTO EG_CH values('ABC', 'abc', _UTF8'abc');
SELECT CONVERTTOHEX(FC4), CONVERTTOHEX(VC4), CONVERTTOHEX(FC4U) from EG_CH;
(EXPR) (EXPR) (EXPR)
-------- -------- ----------------
41424320 616263 0061006200630020
```
* Display the internal values for a DATE column, a TIME column, a
TIMESTAMP(2) column, and a TIMESTAMP(6) column:
+
```
CREATE TABLE DT (D1 date, T1 time, TS1 timestamp(2), TS2 timestamp(6) );
INSERT INTO DT values(current_date, current_time, current_timestamp, current_timestamp);
SELECT CONVERTTOHEX(D1), CONVERTTOHEX(T1), CONVERTTOHEX(TS1), CONVERTTOHEX(TS2) from DT;
(EXPR) (EXPR) (EXPR) (EXPR)
----------- --------- ------------------------- -------------------------
07D8040F 0E201E 07D8040F0E201E00000035 07D8040F0E201E00081ABB
```
<<<
* Display the internal values for an INTERVAL YEAR column, an INTERVAL
YEAR(2) TO MONTH column, and an INTERVAL DAY TO SECOND column:
+
```
CREATE TABLE IVT ( IV1 interval year, IV2 interval year(2) to month, IV3 interval day to second);
INSERT INTO IVT values( interval '1' year, interval '3-2' year(2) to
month, interval '31:14:59:58' day to second);
SELECT CONVERTTOHEX(IV1), CONVERTTOHEX(IV2), CONVERTTOHEX(IV3) from IVT;
(EXPR) (EXPR) (EXPR)
------ -------- -----------------------
0001 0026 0000027C2F9CB780
```
<<<
[[converttimestamp_function]]
== CONVERTTIMESTAMP Function
The CONVERTTIMESTAMP function converts a Julian timestamp to a value
with data type TIMESTAMP.
CONVERTTIMESTAMP is a {project-name} SQL extension.
```
CONVERTTIMESTAMP (julian-timestamp)
```
* `_julian-timestamp_`
+
is an expression that evaluates to a Julian timestamp, which is a
LARGEINT value.
[[considerations_for_converttimestamp]]
=== Considerations for CONVERTTIMESTAMP
The _julian-timestamp_ value must be in the range from 148731
63200000000 to 274927348799999999.
[[relationship_to_the_juliantimestamp_function]]
==== Relationship to the JULIANTIMESTAMP Function
The operand of CONVERTTIMESTAMP is a Julian timestamp, and the function
result is a value of data type TIMESTAMP. The operand of the
CONVERTTIMESTAMP function is a value of data type TIMESTAMP, and the
function result is a Julian timestamp. That is, the two functions have
an inverse relationship to one another.
[[use_of_converttimestamp]]
==== Use of CONVERTTIMESTAMP
You can use the inverse relationship between the JULIANTIMESTAMP and
CONVERTTIMESTAMP functions to insert Julian timestamp columns into your
database and display these column values in a TIMESTAMP format.
<<<
[[examples_of_converttimestamp]]
=== Examples of CONVERTTIMESTAMP
* Suppose that the EMPLOYEE table includes a column, named HIRE_DATE,
which contains the hire date of each employee as a Julian timestamp.
Convert the Julian timestamp into a TIMESTAMP value:
+
```
SELECT CONVERTTIMESTAMP (hire_date) FROM persnl.employee;
```
* This example illustrates the inverse relationship between
JULIANTIMESTAMP and CONVERTTIMESTAMP.
+
```
SELECT CONVERTTIMESTAMP (JULIANTIMESTAMP (ship_timestamp)) FROM persnl.project;
```
+
If, for example, the value of SHIP_TIMESTAMP is 2008-04-03
21:05:36.143000, the result of CONVERTTIMESTAMP(JULIANTIMESTAMP(ship_timestamp))
is the same value, 2008-04-03 21:05:36.143000.
<<<
[[cos_function]]
== COS Function
The COS function returns the cosine of a numeric value expression, where
the expression is an angle expressed in radians.
COS is a {project-name} SQL extension.
```
COS (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the COS function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_cos]]
=== Examples of COS
* This function returns the value 9.39680940386503680E-001, or
approximately 0.9397, the cosine of 0.3491 (which is 20 degrees):
+
```
COS (0.3491)
```
<<<
[[cosh_function]]
=== COSH Function
The COSH function returns the hyperbolic cosine of a numeric value
expression, where the expression is an angle expressed in radians.
COSH is a {project-name} SQL extension.
```
COSH (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the COSH function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_cosh]]
=== Examples of COSH
* This function returns the value 1.88842387716101568E+000, or
approximately 1.8884, the hyperbolic cosine of 1.25 in radians:
+
```
COSH (1.25)
```
<<<
[[count_function]]
== COUNT Function
The COUNT function counts the number of rows that result from a query or
the number of rows that contain a distinct value in a specific column.
The result of COUNT is data type LARGEINT. The result can never be NULL.
```
COUNT {(*) | ([ALL | DISTINCT] expression)}
```
* `COUNT (*)`
+
returns the number of rows in the table specified in the FROM clause of
the SELECT statement that contains COUNT (\*). If the result table is
empty (that is, no rows are returned by the query) COUNT (*) returns
zero.
* `ALL | DISTINCT`
+
returns the number of all rows or the number of distinct rows in the
one-column table derived from the evaluation of _expression_. The
default option is ALL, which causes duplicate values to be included. If
you specify DISTINCT, duplicate values are eliminated before the COUNT
function is applied.
* `_expression_`
+
specifies a value expression that determines the values to count. The
_expression_ cannot contain an aggregate function or a subquery. The
DISTINCT clause specifies that the COUNT function operates on distinct
values from the one-column table derived from the evaluation of
_expression_. See <<expressions,Expressions>>.
[[considerations_for_count]]
=== Considerations for COUNT
[[operands-of-the-expression-1]]
==== Operands of the Expression
The operand of COUNT is either * or an expression that includes columns
from the result table specified by the SELECT statement that contains
COUNT. However, the expression cannot include an aggregate function or a
subquery. These expressions are valid:
```
COUNT (*)
COUNT (DISTINCT JOBCODE)
COUNT (UNIT_PRICE * QTY_ORDERED)
```
<<<
[[count_nulls]]
==== Nulls
COUNT is evaluated after eliminating all nulls from the one-column table
specified by the operand. If the table has no rows, COUNT returns zero.
COUNT(\*) does not eliminate null rows from the table specified in the
FROM clause of the SELECT statement. If all rows in a table are null,
COUNT(\*) returns the number of rows in the table.
[[examples_of_count]]
=== Examples of COUNT
* Count the number of rows in the EMPLOYEE table:
+
```
SELECT COUNT (*) FROM persnl.employee;
(EXPR)
-----------
62
--- 1 row(s) selected.
```
* Count the number of employees who have a job code in the EMPLOYEE
table:
+
```
SELECT COUNT (jobcode) FROM persnl.employee;
(EXPR)
-----------
56
--- 1 row(s) selected.
SELECT COUNT(*)
FROM persnl.employee
WHERE jobcode IS NOT NULL;
(EXPR)
-----------
56
--- 1 row(s) selected.
```
<<<
* Count the number of distinct departments in the EMPLOYEE table:
+
```
SELECT COUNT (DISTINCT deptnum) FROM persnl.employee;
(EXPR)
-----------
11
--- 1 row(s) selected.
```
<<<
[[crc32_function]]
== CRC32 Function
Computes a cyclic redundancy check value and returns a 32-bit unsigned value.
The result is NULL if the argument is NULL. The argument is expected to be a
string and (if possible) is treated as one if it is not.
```
CRC32{ expression)}
```
* `_expression_`
+
specifies a value expression that determines the values to count. The
_expression_ cannot contain an aggregate function or a subquery.
See <<expressions,Expressions>>.
[[examples_of_crc32]]
=== examples of CR32
```
>>SELECT CRC32('Trafodion') from dual;
(EXPR)
----------
1960931967
>>SELECT CRC32(2016) from dual;
(EXPR)
----------
2177070256
```
<<<
[[current_function]]
== CURRENT Function
The CURRENT function returns a value of type TIMESTAMP based on the
current local date and time.
The function is evaluated once when the query starts execution and is
not reevaluated (even if it is a long running query).
You can also use <<current_timestamp_function,CURRENT_TIMESTAMP Function>>.
```
CURRENT [(precision)]
```
* `_precision_`
+
is an integer value in the range 0 to 6 that specifies the precision of
(the number of decimal places in) the fractional seconds in the returned
value. The default is 6.
+
For example, the function CURRENT (2) returns the current date and time
as a value of data type TIMESTAMP, where the precision of the fractional
seconds is 2, for example, 2008-06-26 09:01:20.89. The value returned is
not a string value.
[[examples_of_current]]
=== Examples of CURRENT
* The PROJECT table contains a column SHIP_TIMESTAMP of data type
TIMESTAMP. Update a row by using the CURRENT value:
+
```
UPDATE persnl.project
SET ship_timestamp = CURRENT WHERE projcode = 1000;
```
<<<
[[current_date_function]]
== CURRENT_DATE Function
The CURRENT_DATE function returns the local current date as a value of
type DATE.
The function is evaluated once when the query starts execution and is
not reevaluated (even if it is a long running query).
```
CURRENT_DATE
```
The CURRENT_DATE function returns the current date, such as 2008-09-28.
The value returned is a value of type DATE, not a string value.
[[examples_of_current_date]]
=== Examples of CURRENT_DATE
* Select rows from the ORDERS table based on the current date:
+
```
SELECT * FROM sales.orders
WHERE deliv_date >= CURRENT_DATE;
```
* The PROJECT table has a column EST_COMPLETE of type INTERVAL DAY. If
the current date is the start date of your project, determine the
estimated date of completion:
+
```
SELECT projdesc, CURRENT_DATE + est_complete FROM persnl.project;
Project/Description (EXPR)
------------------- ----------
SALT LAKE CITY 2008-01-18
ROSS PRODUCTS 2008-02-02
MONTANA TOOLS 2008-03-03
AHAUS TOOL/SUPPLY 2008-03-03
THE WORKS 2008-02-02
THE WORKS 2008-02-02
--- 6 row(s) selected.
```
<<<
[[current_time_function]]
== CURRENT_TIME Function
The CURRENT_TIME function returns the current local time as a value of
type TIME.
The function is evaluated once when the query starts execution and is
not reevaluated (even if it is a long running query).
```
CURRENT_TIME [(precision)]
```
* `_precision_`
+
is an integer value in the range 0 to 6 that specifies the precision of
(the number of decimal places in) the fractional seconds in the returned
value. The default is 0.
+
For example, the function CURRENT_TIME (2) returns the current time as a
value of data type TIME, where the precision of the fractional seconds
is 2, for example, 14:01:59.30. The value returned is not a string
value.
[[examples_of_current_time]]
=== Examples of CURRENT_TIME
* Use CURRENT_DATE and CURRENT_TIME as a value in an inserted row:
+
```
INSERT INTO stats.logfile (user_key, run_date, run_time, user_name)
VALUES (001, CURRENT_DATE, CURRENT_TIME, 'JuBrock');
```
<<<
[[current_timestamp_function]]
== CURRENT_TIMESTAMP Function
The CURRENT_TIMESTAMP function returns a value of type TIMESTAMP based
on the current local date and time.
The function is evaluated once when the query starts execution and is
not reevaluated (even if it is a long running query).
You can also use the <<current_function,CURRENT Function>>.
```
CURRENT_TIMESTAMP [(_precision_)]
```
* `_precision_`
+
is an integer value in the range 0 to 6 that specifies the precision of
(the number of decimal places in) the fractional seconds in the returned
value. The default is 6.
+
For example, the function CURRENT_TIMESTAMP (2) returns the current date
and time as a value of data type TIMESTAMP, where the precision of the
fractional seconds is 2; for example, 2008-06-26 09:01:20.89. The value
returned is not a string value.
[[examples_of_current_timestamp]]
=== Examples of CURRENT_TIMESTAMP
* The PROJECT table contains a column SHIP_TIMESTAMP of data type
TIMESTAMP. Update a row by using the CURRENT_TIMESTAMP value:
+
```
UPDATE persnl.project
SET ship_timestamp = CURRENT_TIMESTAMP WHERE projcode = 1000;
```
<<<
[[current_user_function]]
== CURRENT_USER Function
The CURRENT_USER function returns the database user name of the current
user who invoked the function. The current user is the authenticated
user who started the session. That database user name is used for
authorization of SQL statements in the current session.
```
CURRENT_USER
```
The CURRENT_USER function is similar to the <<user_function,USER Function>>.
[[considerations_for_current_user]]
=== Considerations for CURRENT_USER
* This function can be specified only in the top level of a SELECT statement.
* The value returned is string data type VARCHAR(128) and is in ISO88591 encoding.
[[examples_of_current_user]]
=== Examples of CURRENT_USER
* This example retrieves the database user name for the current user:
+
```
SELECT CURRENT_USER FROM (values(1)) x(a);
(EXPR)
-----------------------
TSHAW
--- 1 row(s) selected.
```
<<<
[[date_add_function]]
== DATE_ADD Function
The DATE_ADD function adds the interval specified by
_interval_expression_ to _datetime_expr_. If the specified interval is
in years or months, DATE_ADD normalizes the result. See
<<standard_normalization,Standard Normalization>>. The type of the
_datetime_expr_ is returned, unless the _interval_expression_ contains
any time components, then a timestamp is returned.
DATE_ADD is a {project-name} SQL extension.
```
DATE_ADD (datetime-expr, interval-expression)
```
* `_datetime-expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
* `_interval-expression_`
+
is an expression that can be combined in specific ways with addition
operators. The _interval_expression_ accepts all interval expression
types that the {project-name} database software considers as valid interval
expressions. See <<interval_value_expressions,Interval Value Expressions>>.
<<<
[[examples_of_date_add]]
=== Examples of DATE_ADD
* This function returns the value DATE '2007-03-07'
+
```
DATE_ADD(DATE '2007-02-28', INTERVAL '7' DAY)
```
* This function returns the value DATE '2008-03-06'
+
```
DATE_ADD(DATE '2008-02-28', INTERVAL '7' DAY)
```
* This function returns the timestamp '2008-03-07 00:00:00'
+
```
DATE_ADD(timestamp'2008-02-29 00:00:00', INTERVAL '7' DAY)
```
* This function returns the timestamp '2008-02-28 23:59:59'
+
```
DATE_ADD(timestamp '2007-02-28 23:59:59', INTERVAL '12' MONTH)
```
+
NOTE: compare this example with the last example under DATE_SUB.
<<<
[[date_sub_function]]
== DATE_SUB Function
The DATE_SUB function subtracts the specified _interval_expression_ from
_datetime_expr_. If the specified interval is in years or months,
DATE_SUB normalizes the result. See <<standard_normalization,Standard Normalization>>.
The type of the _datetime_expr_ is returned, unless the _interval_expression_ contains
any time components, then a timestamp is returned.
DATE_SUB is a {project-name} SQL extension.
```
DATE_SUB (datetime-expr, interval-expression)
```
* `_datetime-expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime_Value_Expression>>.
* `_interval-expression_`
+
is an expression that can be combined in specific ways with subtraction
operators. The _interval_expression_ accepts all interval expression
types that the {project-name} database software considers as valid interval
expressions. see <<interval_value_expressions,Interval Value Expressions>>.
<<<
[[examples_of_date_sub]]
=== Examples of DATE_SUB
* This function returns the value DATE '2009-02-28'
+
```
DATE_SUB(DATE '2009-03-07', INTERVAL'7' DAY)
```
* This function returns the value DATE '2008-02-29'
+
```
DATE_SUB(DATE '2008-03-07', INTERVAL'7' DAY)
```
* This function returns the timestamp '2008-02-29 00:00:00'
+
```
DATE_SUB(timestamp '2008-03-31 00:00:00', INTERVAL '31' DAY)
```
* This function returns the timestamp '2007-02-28 23:59:59'
+
```
DATE_SUB(timestamp '2008-02-29 23:59:59', INTERVAL '12' MONTH)
```
<<<
[[dateadd_function]]
== DATEADD Function
The DATEADD function adds the interval of time specified by _datepart_
and _num-expr_ to _datetime-expr_. If the specified interval is in
years or months, DATEADD normalizes the result. See
<<standard_normalization,Standard Normalization>>. The type of the
_datetime-expr_ is returned, unless the interval expression contains any
time components, then a timestamp is returned.
DATEADD is a {project-name} SQL extension.
```
DATEADD(datepart, num-expr, datetime-expr)
```
* `_datepart_`
+
is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, or one of the
following abbreviations:
+
[cols="15%,85%"]
|===
| YEAR | _YY_ and _YYYY_
| MONTH | _M_ and _MM_
| DAY | _D_ and _DD_
| HOUR | _HH_
| MINUTE | _MI_ and _M_
| SECOND | _SS_ and _S_
| QUARTER | _Q_ and _QQ_
| WEEK | _WW_ and _WK_
|===
* `_num-expr_`
+
is an SQL exact numeric value expression that specifies how many
_datepart_ units of time are to be added to _datetime_expr_. If
_num_expr_ has a fractional portion, it is ignored. If _num_expr_ is
negative, the return value precedes _datetime_expr_ by the specified
amount of time. See <<numeric_value_expressions,Numeric Value Expressions>>.
* `_datetime-expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. The type of the _datetime_expression_ is returned, unless the
interval expression contains any time components, then a timestamp is
returned. See <<datetime_value_expressions,Datetime Value Expressions>>.
<<<
[[examples_of_dateadd]]
=== Examples of DATEADD
* This function adds seven days to the date specified in _start_date_
+
```
DATEADD(DAY, 7,start_date)
```
* This function returns the value DATE '2009-03-07'
+
```
DATEADD(DAY, 7 , DATE '2009-02-28')
```
* This function returns the value DATE '2008-03-06'
+
```
DATEADD(DAY, 7, DATE '2008-02-28')
```
* This function returns the timestamp '2008-03-07 00:00:00'
+
```
DATEADD(DAY, 7, timestamp'2008-02-29 00:00:00')
```
<<<
[[datediff_function]]
== DATEDIFF Function
The DATEDIFF function returns the integer for the number of
_datepart_ units of time between _startdate_ and _enddate_.
DATEDIFF is a {project-name} SQL extension.
```
DATEDIFF (datepart, startdate, enddate)
```
<<<
[[syntax_description_of_datediff]]
=== Syntax Description of DATEDIFF
* `datepart`
+
is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, or one of the
following abbreviations:
+
[cols="15%,85%"]
|===
| YEAR | _YY_ and _YYYY_
| MONTH | _M_ and _MM_
| DAY | _D_ and _DD_
| HOUR | _HH_
| MINUTE | _MI_ and _M_
| SECOND | _SS_ and _S_
| QUARTER | _Q_ and QQ
| WEEK | _WW_ and _WK_
|===
* `startdate`
+
may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
See <<datetime_value_expressions,Datetime Value Expressions>>.
* `enddate`
+
may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
See <<datetime_value_expressions,Datetime Value Expressions>>.
[[considerations_for_datediff]]
=== Considerations for DATEDIFF
[[boundary]]
==== Boundary
The method of counting crossed boundaries such as days, minutes, and seconds makes the result given by DATEDIFF consistent across all data types.
The result is a signed integer value equal to the number of _datepart_ boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF function does not calculate the full _datepart_, it counts the difference between _startdate_ and _enddate_.
For example:
* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is only a single second.
+
However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 month, 1 quarter or 1 month depending on the specified _datepart_.
+
^1^ NOTE: 2017-12-31 (_startdate_) falls on a Sunday, which is in the same week as 2018-01-01. For more information, see <<the_first_day_of_week,The first day of week>>.
* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two years.
+
However, the DATEDIFF difference is 1 year if the specified _datepart_ is YEAR.
[[the_first_day_of_week]]
==== The first day of week
* The first day of the week is Sunday. Changing the first day of the week is not supported.
* The DATEDIFF function returns the number of Sundays between _startdate_ and _enddate_. This function counts _enddate_ if it falls on a Sunday, but doesn't count _startdate_ even if it does fall on a Sunday.
+
For example,
** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the specified _datepart_ is WEEK.
** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the specified _datepart_ is WEEK.
[[the_first_week_of_year]]
==== The first week of year
The first week of the year is the week in which January 1 occurs. Changing the first week of the year is not supported.
[[negative_or_zero]]
==== Negative or zero
If _enddate_ precedes _startdate_, the return value is negative or zero.
[[error]]
==== Error
* The DATEDIFF function generates an error if the result is out of range for integer values.
** For seconds, the maximum number is equivalent to approximately 68 years.
** For weeks, the dates must be later than 0001-01-07.
<<<
[[examples_of_datediff]]
=== Examples of DATEDIFF
[[date_difference_in_second]]
==== Date Difference in SECOND
* This function returns the value of 0 because no one-second boundary
is crossed.
+
```
SELECT DATEDIFF( SECOND
, TIMESTAMP '2006-09-12 11:59:58.999998'
, TIMESTAMP '2006-09-12 11:59:58.999999'
)
FROM DUAL;
```
* This function returns the value of 1 because a one-second boundary is
crossed even though the two timestamps differ by only one microsecond.
+
```
SELECT DATEDIFF( SECOND
, TIMESTAMP '2006-09-12 11:59:58.999999'
, TIMESTAMP '2006-09-12 11:59:59.000000'
)
FROM DUAL;
```
[[date_difference_in_minute]]
==== Date Difference in MINUTE
* This function returns the value of 2 because two minute boundaries are crossed.
+
```
SELECT DATEDIFF( MINUTE
, TIMESTAMP '2011-12-30 08:54:55'
, TIMESTAMP '2011-12-30 08:56:01'
)
FROM DUAL;
```
[[date_difference_in_day]]
==== Date Difference in DAY
* This function returns the value of -29.
+
```
SELECT DATEDIFF( DAY
, DATE '2008-03-01'
, DATE '2008-02-01'
)
FROM DUAL;
```
* This statement calculates how long buyers have to wait.
+
```
SELECT id, DATEDIFF( DAY, order_date, delivery_date ), price
FROM orders
ORDER BY price DESC;
```
+
```
ID (EXPR) PRICE
----------- ----------- ------
700300 145 926
312203 147 621
800660 23 568
100350 160 543
500450 148 324
700510 141 229
100210 3 228
600480 151 197
300380 154 183
200490 227 123
200320 153 91
400410 158 65
600250 143 32
--- 13 row(s) selected.
```
[[date_difference_in_week]]
==== Date Difference in WEEK
* This function returns the value of 1 because only a one-week boundary is
crossed.
+
```
SELECT DATEDIFF( WEEK
, DATE '2006-01-01'
, DATE '2006-01-09'
)
FROM DUAL;
```
[[date_difference_in_quarter]]
==== Date Difference in QUARTER
* This function returns the value of 3 because three quarter boundaries are crossed.
+
```
SELECT DATEDIFF( QUARTER
, DATE '2017-03-05'
, DATE '2017-11-17'
)
FROM DUAL;
```
[[date_difference_in_year]]
==== Date Difference in YEAR
* This function returns the value of 0.
+
```
SELECT DATEDIFF( YEAR
, TIMESTAMP '2006-12-31 23:59:59.999998'
, TIMESTAMP '2006-12-31 23:59:59.999999'
)
FROM DUAL;
```
* This function returns the value of 1 because a year boundary is
crossed.
+
```
SELECT DATEDIFF( YEAR
, TIMESTAMP '2006-12-31 23:59:59.999999'
, TIMESTAMP '2007-01-01 00:00:00.000000'
)
FROM DUAL;
```
<<<
[[dateformat_function]]
== DATEFORMAT Function
The DATEFORMAT function returns a datetime value as a character string
literal in the DEFAULT, USA, or EUROPEAN format. The data type of the
result is CHAR.
DATEFORMAT is a {project-name} SQL extension.
```
DATEFORMAT (datetime-expression,{DEFAULT | USA | EUROPEAN})
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE, TIME,
or TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
* `DEFAULT | USA | EUROPEAN`
+
specifies a format for a datetime value. See <<datetime_literals,Datetime Literals>>.
[[considerations_for_dateformat]]
=== Considerations for DATEFORMAT
The DATEFORMAT function returns the datetime value in ISO88591
encoding.
[[examples_of_dateformat]]
=== Examples of DATEFORMAT
* Convert a datetime literal in DEFAULT format to a string in USA
format: DATEFORMAT (TIMESTAMP '2008-06-20 14:20:20.00', USA) The
function returns this string literal:
+
```
'06/20/2008 02:20:20.00 PM'
```
* Convert a datetime literal in DEFAULT format to a string in European
format: DATEFORMAT (TIMESTAMP '2008-06-20 14:20:20.00', EUROPEAN) The
function returns this string literal:
+
```
'20.06.2008 14.20.20.00'
```
<<<
[[date_part_function_of_an_interval]]
== DATE_PART Function (of an Interval)
The DATE_PART function extracts the datetime field specified by _text_
from the _interval_ value specified by _interval_ and returns the result
as an exact numeric value. The DATE_PART function accepts the
specification of 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND'
for text.
DATE_PART is a {project-name} SQL extension.
```
DATEPART (text, interval)
```
* `_text_`
+
specifies YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. The value must be
enclosed in single quotes.
* `_interval_`
+
_interval_ accepts all interval expression types that the {project-name}
database software considers as valid interval expressions. See
<<interval_value_expressions,Interval Value Expressions>>.
The DATE_PART(_text_, _interval_) is equivalent to EXTRACT(_text_,
_interval_), except that the DATE_PART function requires single quotes
around the text specification, where EXTRACT does not allow single
quotes.
When SECOND is specified the fractional part of the second is returned.
[[examples_of_date_part]]
=== Examples of DATE_PART
* This function returns the value of 7.
+
```
DATE_PART('DAY', INTERVAL '07:04' DAY TO HOUR)
```
* This function returns the value of 6.
+
```
DATE_PART('MONTH', INTERVAL '6' MONTH)
```
* This function returns the value of 36.33.
+
```
DATE_PART('SECOND', INTERVAL '5:2:15:36.33' DAY TO SECOND(2))
```
<<<
[[date_part_function_of_a_timestamp]]
== DATE_PART Function (of a Timestamp)
The DATE_PART function extracts the datetime field specified by _text_
from the datetime value specified by _datetime_expr_ and returns the
result as an exact numeric value. The DATE_PART function accepts the
specification of 'YEAR', 'YEARQUARTER', 'YEARMONTH', 'YEARWEEK',
'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND' for text.
The DATE_PART function of a timestamp can be changed to DATE_PART
function of a datetime because the second argument can be either a
timestamp or a date expression.
DATE_PART is a {project-name} extension.
```
DATE_PART(text, datetime-expr)
```
* `_text_`
+
specifies YEAR, YEARQUARTER, YEARMONTH, YEARWEEK, MONTH, DAY, HOUR,
MINUTE, or SECOND. The value must be enclosed in single quotes.
** *YEARMONTH*: Extracts the year and the month, as a 6-digit integer of
the form yyyymm (100 \* year + month).
** *YEARQUARTER*: Extracts the year and quarter, as a 5-digit integer of
the form yyyyq, (10 \* year + quarter) with q being 1 for the first
quarter, 2 for the second, and so on.
** *YEARWEEK*: Extracts the year and week of the year, as a 6-digit integer
of the form yyyyww (100 \* year + week). The week number will be computed
in the same way as in the WEEK function.
* `_datetime-expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
DATE_PART(_text_, _datetime-expr_) is mostly equivalent to
EXTRACT(_text_, _datetime-expr_), except that DATE_PART requires
single quotes around the text specification where EXTRACT does not allow
single quotes. In addition, you cannot use the YEARQUARTER, YEARMONTH,
and YEARWEEK text specification with EXTRACT.
<<<
[[examples_of_date_part]]
=== Examples of DATE_PART
* This function returns the value of 12.
+
```
DATE_PART('month', date'12/05/2006')
```
* This function returns the value of 2006.
+
```
DATE_PART('year', date'12/05/2006')
```
* This function returns the value of 31.
+
```
DATE_PART('day', TIMESTAMP '2006-12-31 11:59:59.999999')
```
* This function returns the value 201107.
+
```
DATE_PART('YEARMONTH', date '2011-07-25')
```
<<<
[[date_trunc_function]]
== DATE_TRUNC Function
The DATE_TRUNC function returns a value of type TIMESTAMP, which has all
fields of lesser precision than _text_ set to zero (or 1 in the case of
months or days).
DATE_TRUNC is a {project-name} SQL extension.
```
DATE_TRUNC(text, datetime-expr)
```
* `_text_`
+
specifies 'YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', or 'SECOND'. The
DATE_TRUNC function also accepts the specification of 'CENTURY' or 'DECADE'.
* `_datetime_expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. DATE_TRUNC returns a value of type TIMESTAMP which has all
fields of lesser precision than _text_ set to zero (or 1 in the case of
months or days). See <<datetime_value_expressions,Datetime Value Expressions>>.
<<<
[[examples_of_date_trunc]]
=== Examples of DATE_TRUNC
* This function returns the value of TIMESTAMP '2006-12-31 00:00:00'.
+
```
DATE_TRUNC('day', TIMESTAMP '2006-12-31 11:59:59')
```
* This function returns the value of TIMESTAMP '2006-01-01 00:00:00'
+
```
DATE_TRUNC('YEAR', TIMESTAMP '2006-12-31 11:59:59')
```
* This function returns the value of TIMESTAMP '2006-12-01 00:00:00'
+
```
DATE_TRUNC('MONTH', DATE '2006-12-31')
```
Restrictions:
* DATE_TRUNC( 'DECADE', &#8230;) cannot be used on years less than 10.
* DATE_TRUNC( 'CENTURY', &#8230;) cannot be used on years less than 100.
<<<
[[day_function]]
== DAY Function
The DAY function converts a DATE or TIMESTAMP expression into an INTEGER
value in the range 1 through 31 that represents the corresponding day of
the month. The result returned by the DAY function is equal to the
result returned by the DAYOFMONTH function.
DAY is a {project-name} SQL extension.
```
DAY (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_day]]
=== Examples of Day
* Return an integer that represents the day of the month from the
start date column of the project table:
+
```
SELECT start_date, ship_timestamp, DAY(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 10
```
<<<
[[dayname_function]]
== DAYNAME Function
The DAYNAME function converts a DATE or TIMESTAMP expression into a
character literal that is the name of the day of the week (Sunday,
Monday, and so on).
DAYNAME is a {project-name} SQL extension.
```
DAYNAME (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[considerations_for_dayname]]
=== Considerations for DAYNAME
The DAYNAME function returns the name of the day in ISO88591.
[[examples_of_dayname]]
=== Examples of DAYNAME
Return the name of the day of the week from the start date column in the
project table:
```
SELECT start_date, ship_timestamp, DAYNAME(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ---------
2008-04-10 2008-04-21 08:15:00.000000 Thursday
```
<<<
[[dayofmonth_function]]
== DAYOFMONTH Function
The DAYOFMONTH function converts a DATE or TIMESTAMP expression into an
INTEGER in the range 1 through 31 that represents the
corresponding day of the month. The result returned by the DAYOFMONTH
function is equal to the result returned by the DAY function.
DAYOFMONTH is a {project-name} SQL extension.
```
DAYOFMONTH (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_dayofmonth]]
=== Examples of DAYOFMONTH
* Return an integer that represents the day of the month from the
start date column of the project table:
+
```
SELECT start_date, ship_timestamp, DAYOFMONTH(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 10
```
<<<
[[dayofweek_function]]
== DAYOFWEEK Function
The DAYOFWEEK function converts a DATE or TIMESTAMP expression into an
INTEGER in the range 1 through 7 that represents the corresponding
day of the week. The value 1 represents Sunday, 2 represents Monday, and
so forth.
DAYOFWEEK is a {project-name} SQL extension.
```
DAYOFWEEK (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_dayofweek]]
=== Examples of DAYOFWEEK
* Return an integer that represents the day of the week from the
START_DATE column in the PROJECT table:
+
```
SELECT start_date, ship_timestamp, DAYOFWEEK(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 5
```
+
The value returned is 5, representing Thursday. The week begins on Sunday.
<<<
[[dayofyear_function]]
== DAYOFYEAR Function
The DAYOFYEAR function converts a DATE or TIMESTAMP expression into an
INTEGER in the range 1 through 366 that represents the
corresponding day of the year.
DAYOFYEAR is a {project-name} SQL extension.
```
DAYOFYEAR (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_dayofyear]]
=== Examples of DAYOFYEAR
* Return an integer that represents the day of the year from the
start date column in the project table:
+
```
SELECT start_date, ship_timestamp, DAYOFYEAR(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- --------------------
2008-04-10 2008-04-21 08:15:00.000000 |101
```
<<<
[[Decode_function]]
== DECODE Function
The DECODE function compares _expr_ to each _test_expr_ value one by one
in the order provided. If _expr_ is equal to a _test_expr_, then the
corresponding _retval_ is returned. If no match is found, _default_ is
returned. If no match is found and _default_ is omitted, NULL is
returned.
DECODE is a {project-name} SQL extension.
```
DECODE (expr, test-expr, retval [, test-expr2, retval2 ... ] [ , default ] )
```
* `_expr_`
+
is an SQL expression.
* `_test-expr_, _test-expr_, &#8230;`
+
are each an SQL expression of a type comparable to that of _expr_.
* `_retval_`
+
is an SQL expression.
* `_default_, _retval2_, &#8230`
+
are each an SQL expression of a type comparable to that of _retval_.
[[considerations_for_decode]]
=== Considerations for DECODE
In a DECODE function, two nulls are considered to be equivalent. If
_expr_ is null, then the returned value is the _retval_ of the first
_test-expr_ that is also null.
The _expr_, _test-expr_, _retval_, and _default_ values can be
derived from expressions.
The arguments can be any of the numeric types or character types.
However, _expr_ and each _test-expr_ value must be of comparable types.
If _expr_ and _test-expr_ values are character types, they must be in
the same character set (to be comparable types.)
All the _retval_ values and the _default_ value, if any, must be of
comparable types.
If _expr_ and a _test-expr_ value are character data, the comparison is
made using non-padded comparison semantics.
If _expr_ and a _test-expr_ value are numeric data, the comparison is
made with a temporary copy of one of the numbers, according to defined
rules of conversion. For example, if one number is INTEGER and the other
is DECIMAL, the comparison is made with a temporary copy of the integer
converted to a decimal.
If all the possible return values are of fixed-length character types,
the returned value is a fixed-length character string with size equal to
the maximum size of all the possible return value types.
If any of the possible return values is a variable-length character
type, the returned value is a variable-length character string with
maximum size of all the possible return value types.
If all the possible return values are of integer types, the returned
value is the same type as the largest integer type of all the possible
return values.
If the returned value is of type FLOAT, the precision is the maximum
precision of all the possible return values.
If all the possible returned values are of the same non-integer, numeric
type (REAL, FLOAT, DOUBLE PRECISION, NUMERIC, or DECIMAL), the returned
value is of that same type.
If all the possible return values are of numeric types but not all the
same, and at least one is REAL, FLOAT, or DOUBLE PRECISION, then the
returned value is of type DOUBLE PRECISION.
If all the possible return values are of numeric types but not all the
same, none are REAL, FLOAT, or DOUBLE PRECISION, and at least one is of
type NUMERIC, then the returned value is of type NUMERIC.
If all the possible return values are of numeric types, none are
NUMERIC, REAL, FLOAT, or DOUBLE PRECISION, and at least one is of type
DECIMAL, then the returned value will be of type DECIMAL.
If the returned value is of type NUMERIC or DECIMAL, it has a precision
equal to the sum of:
* The maximum scale of all the possible return value types and
* The maximum value of (precision - scale) for all the possible return value types.
However, the precision will not exceed 18.
The scale of the returned value is the minimum of:
* The maximum scale of all the possible return value types and
* 18 - (the maximum value of (precision - scale) for all the possible
return value types).
The number of components in the DECODE function, including _expr_,
_test-exprs_, _retvals_, and _default_, has no limit other than
the general limit of how big an SQL expression can be. However, large
lists do not perform well.
The syntax
```
DECODE (expr, test_expr, retval [, test_expr2, retval2 ... ] [, default ] ):
```
is logically equivalent to the following:
```
CASE
WHEN (expr IS NULL AND test-expr IS NULL) OR expr = test-expr THEN retval
WHEN (expr IS NULL AND test-expr2 IS NULL) OR expr = test_expr2 THEN retval2
...
ELSE default /* or ELSE NULL if _default_ not specified */
END
```
No special conversion of _expr_, _test-exprN_, or _retvalN_ exist
other than what a CASE statement normally does.
[[examples_of_decode]]
<<<
=== Examples of DECODE
* Example of the DECODE function:
+
```
SELECT
emp_name
, DECODE( CAST (( yrs_of_service + 3) / 4 AS INT )
, 0,0.04
, 1,0.04
, 0.06
) as perc_value
FROM employees;
SELECT
supplier_name
, DECODE( supplier_id
, 10000
, 'Company A'
, 10001
, 'Company B'
, 10002
, 'Company C'
, 'Company D'
) as result
FROM suppliers;
```
* This example shows a different way of handling NULL specified as
default and not specified as default explicitly:
+
```
SELECT DECODE( (?p1 || ?p2), trim(?p1), 'Hi', ?p3, null ) from emp;
..
*** ERROR[4049] A CASE expression cannot have a result data type of both CHAR(2) and NUMERIC(18,6).
*** ERROR[4062] The preceding error actually occurred in function DECODE((?P1 || ?P2),(' ' TRIM ?P1), 'Hi', ?P3, NULL)
*** ERROR[8822] The statement was not prepared.
```
+
The last _ret-val_ is an explicit NULL. When {project-name} SQL encounters
this situation, it assumes that the return value will be NUMERIC(18,6).
Once {project-name} SQL determines that the return values are numeric, it
determines that all possible return values must be numeric. When 'Hi' is
encountered in a _ret-val_ position, the error is produced because the
CHAR(2) type argument is not comparable with a NUMERIC(18,6) type return
value.
+
This statement is equivalent and will not produce an error:
+
```
SELECT DECODE( (?p1 || ?p2), trim(?p1), 'Hi' ) from emp;
```
<<<
[[degrees_function]]
== DEGREES Function
The DEGREES function converts a numeric value expression expressed in
radians to the number of degrees.
DEGREES is a {project-name} SQL extension.
```
DEGREES (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the DEGREES function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_degrees]]
=== Examples of Degrees
* This function returns the value 45.0001059971939008 in degrees:
+
```
DEGREES(0.78540)
```
* This function returns the value of 45. The function degrees is the
inverse of the function radians.
+
```
DEGREES(RADIANS(45))
```
<<<
[[diff1_function]]
== DIFF1 Function
The DIFF1 function is a sequence function that calculates the amount of
change in an expression from row to row in an intermediate result table
ordered by a sequence by clause in a select statement.
See <<sequence_by_clause,SEQUENCE BY Clause>>.
DIFF1 is a {project-name} SQL extension.
```
DIFF1 (column-expression-a [,column-expression-b])
```
* `_column-expression-a_`
+
specifies a derived column determined by the evaluation of the column
expression. If you specify only one column as an argument, DIFF1 returns
the difference between the value of the column in the current row and
its value in the previous row; this version calculates the unit change
in the value from row to row.
* `_column-expression-b_`
+
specifies a derived column determined by the evaluation of the column
expression. If you specify two columns as arguments, DIFF1 returns the
difference in consecutive values in _column-expression-a_ divided by
the difference in consecutive values in _column-expression-b_.
The purpose of the second argument is to distribute the amount of change
from row to row evenly over some unit of change (usually time) in
another column.
[[considerations_for_diff1]]
=== Considerations for DIFF1
[[equivalent-result]]
==== Equivalent Result
If you specify one argument, the result of DIFF1 is equivalent to:
column-expression-a - OFFSET(column-expression-a, 1) If you specify two
arguments, the result of DIFF1 is equivalent to:
```
DIFF1(column-expression-a) / DIFF1(column-expression-b)
```
The two-argument version involves division by the result of the DIFF1
function. To avoid divide-by-zero errors, be sure that
_column-expression-b_ does not contain any duplicate values whose DIFF1
computation could result in a divisor of zero.
[[datetime-arguments]]
==== Datetime Arguments
In general, {project-name} SQL does not allow division by a value of INTERVAL
data type. However, to permit use of the two-argument version of DIFF1
with times and dates, {project-name} SQL relaxes this restriction and allows
division by a value of INTERVAL data type.
[[examples_of_diff1]]
=== Examples of DIFF1
* Retrieve the difference between the I1 column in the current row and
the I1 column in the previous row:
+
```
SELECT DIFF1 (I1) AS DIFF1_I1
FROM mining.seqfcn SEQUENCE BY TS;
DIFF1_I1
------------
?
21959
-9116
-14461
7369
--- 5 row(s) selected.
```
+
The first row retrieved displays null because the offset from the
current row does not fall within the results set.
* Retrieve the difference between the TS column in the current row and
the TS column in the previous row:
+
```
SELECT DIFF1 (TS) AS DIFF1_TS
FROM mining.seqfcn SEQUENCE BY TS;
DIFF1_TS
--------------------
?
30002620.000000
134157861.000000
168588029.000000
114055223.000000
--- 5 row(s) selected.
```
+
The results are expressed as the number of seconds. For example, the
difference between TIMESTAMP '1951-02-15 14:35:49' and TIMESTAMP
'1950-03-05 08:32:09' is approximately 347 days. The difference between
TIMESTAMP '1955-05-18 08:40:10' and TIMESTAMP '1951-02-15 14:35:49' is
approximately 4 years and 3 months, and so on.
<<<
* This query retrieves the difference in consecutive values in I1
divided by the difference in consecutive values in TS:
+
```
SELECT DIFF1 (I1,TS) AS DIFF1_I1TS
FROM mining.seqfcn SEQUENCE BY TS;
DIFF1_I1TS
-------------------
?
.0007319
-.0000679
-.0000857
.0000646
--- 5 row(s) selected.
```
+
The results are equivalent to the quotient of the results from the two
preceding examples. For example, in the second row of the output of this
example, 0.0007319 is equal to 21959 divided by 30002620.
<<<
[[diff2_function]]
== DIFF2 Function
The DIFF2 function is a sequence function that calculates the amount of
change in a DIFF1 value from row to row in an intermediate result table
ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
DIFF2 is a {project-name} SQL extension.
```
DIFF2 (column-expression-a [,column-expression-b])
```
* `_column-expression-a_`
+
specifies a derived column determined by the evaluation of the column
expression. If you specify only one column as an argument, DIFF2 returns
the difference between the value of DIFF1(_column-expression-a_) in
the current row and the same result in the previous row.
* `_column-expression-b_`
+
specifies a derived column determined by the evaluation of the column
expression. If you specify two columns as arguments, DIFF2 returns the
difference in consecutive values of DIFF1(_column-expression-a_)
divided by the difference in consecutive values in
_column-expression-b_.
See <<diff1_function,DIFF1 Function>>.
[[considerations_for_diff2]]
=== Considerations for DIFF2
[[equivalent_result_1]]
==== Equivalent Result
* If you specify one argument, the result of DIFF2 is equivalent to:
+
```
DIFF1(column-expression-a)- OFFSET(DIFF1(column-expression-a),1)
```
* If you specify two arguments, the result of DIFF2 is equivalent to:
+
```
DIFF2(column-expression-a) / DIFF1(column-expression-b)
```
The two-argument version involves division by the result of the DIFF1
function. To avoid divide-by-zero errors, be sure that
_column-expression-b_ does not contain any duplicate values whose DIFF1
computation could result in a divisor of zero.
[[datetime_arguments]]
==== Datetime Arguments
In general, {project-name} SQL does not allow division by a value of INTERVAL
data type. However, to permit use of the two-argument version of DIFF2
with times and dates, {project-name} SQL relaxes this restriction and allows
division by a value of INTERVAL data type.
[[examples_of_diff2]]
=== Examples of DIFF2
* Retrieve the difference between the value of DIFF1(I1) in the current
row and the same result in the previous row:
+
```
SELECT DIFF2 (I1) AS DIFF2_I1
FROM mining.seqfcn SEQUENCE BY TS;
DIFF2_I1
--------------------
?
?
-31075
-5345
21830
--- 5 row(s) selected.
```
+
The results are equal to the difference of DIFF1(I1) for the current row
and DIFF1(I1) of the previous row. For example, in the third row of the
output of this example, -31075 is equal to
-91 6 minus 21959. The value -91 6 is the result of DIFF1(I1) for the
current row, and the
value 21959 is the result of DIFF1(I1) for the previous row.
See <<examples_of_diff1,Examples of DIFF1>>.
* Retrieve the difference in consecutive values of DIFF1(I1) divided by
the difference in consecutive values of TS:
+
```
SELECT DIFF2 (I1,TS) AS DIFF2_I1TS
FROM mining.seqfcn SEQUENCE BY TS;
DIFF2_I1TS
---------------------
?
?
-.000231
-.000031
.000191
--- 5 row(s) selected.
```
<<<
[[emptyblob_function]]
== EMPTY_BLOB() Function
[[syntax_descriptions_of_emptyblob_function]]
=== Syntax Descriptions of EMPTY_BLOB() Function
The EMPTY_BLOB() function creates a dummy LOB handle of type BLOB.
This function can be used in an INSERT or UPDATE statement, to initialize a LOB column or attribute to EMPTY.
EMPTY_BLOB() function is a Trafodion SQL extension.
For more information, see http://trafodion.apache.org/docs/lob_guide/index.html[Trafodion SQL Large Objects Guide].
```
EMPTY_BLOB()
```
[[considerations_for_emptyblob_function]]
=== Considerations for EMPTY_BLOB() Function
* If EMPTY_BLOB() is specified, then a dummy lob handle is created.
** No data is associated with the empty LOBs yet, but these dummy LOB handles can later be used to populate the LOB with new data. If the LOB had data previously associated with it, it will be erased.
** The dummy LOB handle will get the same datatype as the underlying column.
+
For example,
+
*** If the LOB column is defined as `'EXTERNAL'` during table creation, then the dummy LOB handle will get the type `'EXTERNAL'` and can only process external LOB data.
+
*** If the LOB column is not defined as `'EXTERNAL'` during table creation, then the dummy LOB handle will be considered as a regular LOB dummy handle. If this handle is used to perform an insert or update operation, the LOB data will be maintained within the Trafodion space in the internal location maintained by Trafodion.
* An empty LOB is distinct from a LOB containing a string of length zero or a null LOB.
[[examples_of_emptyblob_function]]
=== Examples of EMPTY_BLOB() Function
* This example uses the EMPTY_BLOB to insert an empty LOB and creates a dummy LOB handle.
+
```
insert into test1 values (1, empty_blob());
```
<<<
[[emptyclob_function]]
== EMPTY_CLOB() Function
[[syntax_descriptions_of_emptyclob_function]]
=== Syntax Descriptions of EMPTY_CLOB() Function
The EMPTY_CLOB() function creates a dummy LOB handle of type CLOB.
This function can be used in an INSERT or UPDATE statement, to initialize a LOB column or attribute to EMPTY.
EMPTY_CLOB() function is a Trafodion SQL extension.
For more information, see http://trafodion.apache.org/docs/lob_guide/index.html[Trafodion LOB Guide].
```
EMPTY_CLOB()
```
[[considerations_for_emptyclob_function]]
=== Considerations for EMPTY_CLOB() Function
* If EMPTY_CLOB() is specified, then a dummy lob handle is created.
** No data is associated with the empty LOBs yet, but these dummy LOB handles can later be used to populate the LOB with new data. If the LOB had data previously associated with it, it will be erased.
** The dummy LOB handle will get the same datatype as the underlying column.
+
For example, if the LOB column was defined as `‘EXTERNAL’` during table creation, then the LOB column gets that type. If it’s not defined, then it is considered as a regular LOB.
* An empty LOB is distinct from a LOB containing a string of length zero or a null LOB.
[[examples_of_emptyclob_function]]
=== Examples of EMPTY_CLOB() Function
* This example uses the EMPTY_CLOB to insert an empty LOB and creates a dummy LOB handle.
+
```
insert into test2 values (1, empty_clob());
```
<<<
[[exp_function]]
== EXP Function
This function returns the exponential value (to the base e) of a numeric
value expression. EXP is a {project-name} SQL extension.
```
EXP (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the EXP function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
The minimum input value must be between -744.4400719 and -744.4400720.
The maximum input value must be between 709.78271289338404 and
709.78271289338405.
[[examples_of_exp]]
=== Examples of EXP
* This function returns the value 3.49034295746184128E+000, or
approximately 3.4903:
+
```
EXP (1.25)
```
* This function returns the value 2.0. The function EXP is the inverse
of the function LOG:
+
```
EXP (LOG(2.0))
```
<<<
[[explain_function]]
== EXPLAIN Function
The EXPLAIN function is a table-valued stored function that generates a
result table describing an access plan for a SELECT, INSERT, DELETE, or
UPDATE statement.
See <<result_of_the_explain_function,Result of the EXPLAIN Function>>.
The EXPLAIN function can be specified as a table reference (_table_)
in the FROM clause of a SELECT statement if it is preceded by the
keyword TABLE and surrounded by parentheses.
For information on the EXPLAIN statement,
see <<explain_statement,EXPLAIN Statement>>.
```
EXPLAIN (module,'statement-pattern')
```
* `_module_ is:`
+
```
'module-name' | NULL
```
* `'_module-name_'`
+
Reserved for future use.
The module name is enclosed in single quotes and is case-sensitive. If a
module name is uppercase, the value you specify within single quotes
must be uppercase. For example: 'MYCAT.MYSCH.MYPROG'
* `NULL`
+
explains statements prepared in the session. '_statement-pattern_'
+
A statement pattern is enclosed in single quotes and is case-sensitive.
The statement name must be in uppercase, unless you delimit the statement
name in a PREPARE statement.
[[considerations_for_explain_function]]
=== Considerations for EXPLAIN Function
[[using_a_statement_pattern]]
==== Using a Statement Pattern
Using a statement pattern is analogous to using a LIKE pattern. You can
use the LIKE pattern in the following ways:
```
SELECT * FROM table (EXPLAIN(NULL,'S%'));
SELECT * FROM table (EXPLAIN(NULL,'S1'));
SELECT * FROM table (EXPLAIN(NULL,'%1'));
```
However, you cannot use the LIKE pattern in this way:
```
SELECT * FROM table (EXPLAIN (NULL, '%'))
```
This statement returns the EXPLAIN result for all prepared statements
whose names begin with the uppercase letter 'S':
```
SELECT * FROM table (EXPLAIN (NULL,'S%'))
```
If the statement pattern does not find any matching statement names, no
rows are returned as the result of the SELECT statement.
[[obtaining_an_explain_plan_while_queries_are_running]]
==== Obtaining an EXPLAIN Plan While Queries Are Running
{project-name} SQL provides the ability to capture an EXPLAIN plan for a
query at any time while the query is running with the QID option. By
default, this behavior is disabled for a {project-name} session.
NOTE: Enable this feature before you start preparing and executing
queries.
After this feature is enabled, use the following syntax in an EXPLAIN
function to get the query execution plan of a running query:
```
SELECT * FROM table (EXPLAIN(NULL, 'QID=_qid_'))
```
* `_qid_` is a case-sensitive identifier, which represents the query ID. For
example:
+
```
'QID=MXID01001011194212103659400053369000000085905admin00_2605_S1'
```
The EXPLAIN function or statement returns the plan that was generated
when the query was prepared. EXPLAIN for QID retrieves all the
information from the original plan of the executing query. The plan is
available until the query finishes executing and is removed or
deallocated.
<<<
[[result_of_the_explain_function]]
==== Result of the EXPLAIN Function
The result table of the EXPLAIN function describes the access plans for
SELECT, INSERT, DELETE, or UPDATE statements.
In this description of the result of the EXPLAIN function, an operator
tree is a structure that represents operators used in an access plan as
nodes, with at most one parent node for each node in the tree, and with
only one root node.
A node of an operator tree is a point in the tree that represents an
event (involving an operator) in a plan. Each node might have
subordinate nodes &#8212; that is, each event might generate a subordinate event
or events in the plan.
[cols="30%l,30%l,40%",options="header"]
|===
| Column Name | Data Type | Description
| MODULE_NAME | CHAR(60) | Reserved for future use.
| STATEMENT_ NAME | CHAR(60) | Statement name; truncated on the right if longer than 60 characters.
| PLAN_ID | LARGEINT | Unique system-generated plan ID automatically assigned by {project-name} SQL;
generated at compile time.
| SEQ_NUM | INT | Sequence number of the current operator in the operator tree; indicates
the sequence in which the operator tree is generated.
| OPERATOR | CHAR(30) | Current operator type.
| LEFT_CHILD_ SEQ_NUM | INT | Sequence number for the first child operator of the current operator;
null if node has no child operators.
| RIGHT_CHILD_ SEQ_NUM | INT | Sequence number for the second child operator of the current operator;
null if node does not have a second child.
| TNAME | CHAR(60) | For operators in scan group, full name of base table, truncated on the
right if too long for column. If correlation name differs from table
name, simple correlation name first and then table name in parentheses.
| CARDINALITY | REAL | Estimated number of rows that will be returned by the current operator.
Cardinality appears as ROWS/REQUEST in some forms of EXPLAIN output. For
the right child of a nested join, multiply the cardinality by the number
of requests to get the total number of rows produced by this operator.
| OPERATOR_COST | REAL | Estimated cost associated with the current operator to execute the
operator.
| TOTAL_COST | REAL | Estimated cost associated with the current operator to execute the
operator, including the cost of all subtrees in the operator tree.
| DETAIL_COST | VARCHAR (200) | Cost vector of five items, described in the next table.
| DESCRIPTION | VARCHAR (3000) | Additional information about the operator.
|===
The DETAIL_COST column of the EXPLAIN function results contains these
cost factors:
[cols="20%l,80%"]
|===
| CPU_TIME | An estimate of the number of seconds of processor time it might take to
execute the instructions for this operator. A value of 1.0 is 1 second.
| IO_TIME | An estimate of the number of seconds of I/O time (seeks plus data
transfer) to perform the I/O for this operator.
| MSG_TIME | An estimate of the number of seconds it takes for the messaging for this
operator. The estimate includes the time for the number of local and
remote messages and the amount of data sent.
| IDLETIME | An estimate of the number of seconds to wait for an event to happen. The
estimate includes the amount of time to open a table or start an ESP
process.
| PROBES | The number of times the operator will be executed. Usually, this value
is 1, but it can be greater when you have, for example, an inner scan of
a nested-loop join.
|===
[[examples_of_explain_function]]
=== Examples of EXPLAIN Function
* Display the specified columns in the result table of the EXPLAIN
function for the prepared statement REGION:
+
```
>>SELECT seq_num, operator, operator_cost FROM table (EXPLAIN (null, 'REG'));
SEQ_NUM OPERATOR OPERATOR_COST
----------- ------------------------------ ---------------
1 TRAFODION_SCAN 0.43691027
2 ROOT 0.0
--- 2 row(s) selected.
>>log;
```
+
The example displays only part of the result table of the EXPLAIN
function. It first uses the EXPLAIN function to generate the table and
then selects the desired columns.
<<<
[[externaltolob_function]]
== EXTERNALTOLOB Function
[[externaltolob_function_syntax]]
=== Syntax Descriptions of EXTERNALTOLOB Function
The EXTERNALTOLOB function converts data from an external file into LOB format. This function can be used in an INSERT or UPDATE statement.
EXTERNALTOLOB function is a Trafodion SQL extension.
For more information, see http://trafodion.apache.org/docs/lob_guide/index.html[Trafodion SQL Large Objects Guide].
```
EXTERNALTOLOB('external lob source file name')
```
```
external lob source file name is:
hdfs:///{local hdfs file name}
```
[[externaltolob_function_considerations]]
=== Considerations for EXTERNALTOLOB Function
* When an external LOB is specified via `EXTERNALTOLOB('external lob source file name')`, the data associated with the external HDFS file is not transferred into the Trafodion LOB. Instead, Trafodion stores the file path/handle of the external file.
+
For example, if you have a directory of pictures, you can specify the full hdfs path to each picture file to this function and the path will get stored in the Trafodion table. Later during retrieval, the file name will be used to go to the actual file to retrieve the data.
[[externaltolob_function_examples]]
=== Examples of EXTERNALTOLOB Function
* This example uses different functions to convert strings, files, external lob into LOB data. The EXTERNALTOLOB function takes an external file.
+
```
insert into tlob130ext values(1, stringtolob('first lob'),
filetolob('hdfs:///lobs/lob_input_a1.txt'),
externaltolob('hdfs:///lobs/lob_input_a1.txt'));
```
* In the table tlob130ext where c1 is 2, this example updates (overwrites) the c4 to lob_input_d1.txt stored in hdfs:///lobs/.
+
```
update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_d1.txt') where c1=2;
```
<<<
[[extract_function]]
== EXTRACT Function
The EXTRACT function extracts a datetime field from a datetime or
interval value expression. It returns an exact numeric value.
```
EXTRACT (datetime-field FROM extract-source)
```
* `_datetime-field_` is:
+
YEAR \| MONTH \| DAY \| HOUR \| MINUTE \| SECOND
* `_extract-source_` is:
+
datetime-expression \| interval-expression
See <<datetime_value_expressions,Datetime Value Expressions>> and
<<interval_value_expressions,Interval Value Expressions>>.
[[examples_of_extract]]
=== Examples of EXTRACT
* Extract the year from a DATE value:
+
```
EXTRACT (YEAR FROM DATE '2007-09-28')
```
+
The result is 2007.
* Extract the year from an INTERVAL value:
+
```
EXTRACT (YEAR FROM INTERVAL '01-09' YEAR TO MONTH)
```
+
The result is 1.
<<<
[[filetolob_function]]
== FILETOLOB Function
[[filetolob_function_syntax]]
=== Syntax Descriptions of FILETOLOB Function
The FILETOLOB function converts data from a local linux/hdfs file into LOB format. This function can be used in an INSERT or UPDATE statement.
FILETOLOB function is a Trafodion SQL extension.
For more information, see http://trafodion.apache.org/docs/lob_guide/index.html[Trafodion SQL Large Objects Guide].
```
FILETOLOB('lob source file name')
```
lob source file name is:
```
hdfs:///{local hdfs file name} |
{local linux file name} |
{file:///linux file name}
```
[[filetolob_function_examples]]
=== Examples of FILETOLOB Function
* This example converts data from a local file into LOB format, and stores all data into HDFS associated with that column/row.
+
```
insert into tlob130txt1 values(1,filetolob('lob_input_a1.txt'));
```
* In the table tlob1 where c1 is 3, this example updates (appends) the c2 to lob_update.txt stored in hdfs:///lobs/.
+
```
update tlob1 set c2=filetolob('hdfs:///lobs/lob_update.txt', append) where c1 = 3;
```
<<<
[[group_concat_function]]
== GROUP_CONCAT Function
This function returns a string result with the concatenated non-NULL values from a group.
It returns NULL if there are no non-NULL values.
The syntax is as follows:
```
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
```
Get the concatenated values of expression combinations. To eliminate duplicate values,
use the DISTINCT clause.
To sort values in the result, use the ORDER BY clause. To sort in reverse order, add
the DESC (descending) keyword to the name of the column you are sorting by in the
ORDER BY clause. The default is ascending order; this may be specified explicitly using
the ASC keyword. The default separator between values in a group is comma (,). To specify
a separator explicitly, use SEPARATOR followed by the string literal value that should be
inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.
[[examples_of_group_concat]]
=== Examples of GROUP_CONCAT
The following example returns concatenated strings for column test_score for each student.
```
>> SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name;
STUDENT_NAME (EXPR)
-------------- --------------
scott 91 90 80 56
tom 91 77 43
```
<<<
[[hour_function]]
== HOUR Function
The HOUR function converts a TIME or TIMESTAMP expression into an
INTEGER in the range 0 through 23 that represents the
corresponding hour of the day.
HOUR is a {project-name} SQL extension.
```
HOUR (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type TIME or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_hour]]
=== Examples of HOUR
* Return an integer that represents the hour of the day from the
ship timestamp column in the project table:
+
```
SELECT start_date, ship_timestamp, HOUR(ship_timestamp)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2007-04-10 2007-04-21 08:15:00.000000 8
```
<<<
[[insert_function]]
== INSERT Function
The INSERT function returns a character string where a specified number
of characters within the character string has been deleted, beginning at
a specified start position, and where another character string has been
inserted at the start position. Every character, including multi-byte
characters, is treated as one character.
INSERT is a {project-name} SQL extension.
```
INSERT (char-expr-1, start, length, char-expr-2)
```
* `_char-expr-1_, _char-expr-2_`
+
are SQL character value expressions (of data type CHAR or VARCHAR) that
specify two strings of characters. The character string _char-expr-2_ is
inserted into the character string_char-expr-1_.
See <<character_value_expressions,Character Value Expressions>>.
* `_start_`
+
specifies the starting position start within char-expr-1 at which to
start deleting length number of characters. after the deletion, the
character string char-expr-2 is inserted into the character string
char-expr-1 , beginning at the start position specified by the number
start . The number start must be a value greater than zero of exact
numeric data type and with a scale of zero.
* `_length_`
+
specifies the number of characters to delete from _char-expr-1_. The
number _length_ must be a value greater than or equal to zero of exact
numeric data type and with a scale of zero. _length_ must be less than
or equal to the length of _char-expr-1_.
[[examples_of_insert]]
=== Examples of INSERT
* Suppose that your JOB table includes an entry for a sales
representative. Use the INSERT function to change SALESREP to SALES REP:
+
```
UPDATE persnl.job
SET jobdesc = INSERT (jobdesc, 6, 3, ' REP')
WHERE jobdesc = 'SALESREP';
```
+
Now check the row you updated:
+
```
SELECT jobdesc FROM persnl.job WHERE jobdesc = 'SALES REP';
Job Description
------------------
SALES REP
--- 1 row(s) selected.
```
<<<
[[is_ipv4_function]]
== IS_IPV4 Function
For a given argument, if it is a valid IPV4 string, IS_IPV4() returns 1 else returns 0.
```
IS_IPV4( expression )
```
* `_expression_`
+
specifies an expression that determines the values to include in the
validation of the IP address. The _expression_ cannot contain an aggregate
function or a subquery. If the input value is NULL, IS_IPV4 returns NULL.
See <<expressions,Expressions>>.
[[examples_of_is_ipv4]]
=== Examples of IS_IPV4
This function returns 1 for the first input argument, since it is a valid IPV4 string;
0 for the second input argument, since it is an invalid IPV4 string.
```
>>SELECT IS_IPV4('10.0.5.9'), IS_IPV4('10.0.5.256') from dual;
(EXPR) (EXPR)
------- -------
1 0
```
<<<
[[is_ipv6_function]]
== IS_IPV6 Function
Returns 1 if the argument is a valid IPv6 address specified as a string, 0 otherwise.
This function does not consider IPv4 addresses to be valid IPv6 addresses.
```
IS_IPV6( expression )
```
* `_expression_`
+
specifies an expression that determines the values to include in the
validation of the IP address. The _expression_ cannot contain an aggregate
function or a subquery. If the input value is NULL, IS_IPV6 returns NULL.
See <<expressions,Expressions>>.
[[examples_of_is_ipv6]]
=== Examples of IS_IPV6
This function returns 0 for the second input argument, since it is a valid IPV6 string;
1 for the second input argument, since it is an invalid IPVr6 string.
```
>>SELECT IS_IPV6('10.0.5.9'), IS_IPV6('::1') from dual;
(EXPR) (EXPR)
-------- -------
1 0
```
[[inet_aton_function]]
== INET_ATON Function
Given the dotted-quad representation of an IPv4 network address as a string,
returns an integer that represents the numeric value of the address in network
byte order (big endian). INET_ATON() returns NULL if it does not understand its argument.
```
INET_ATON( expression )
```
* `_expression_`
+
specifies an expression that determines the values to include in the
conversion of the IP address. The _expression_ cannot contain an aggregate
function or a subquery. If the input value is NULL, INET_ATON returns NULL.
See <<expressions,Expressions>>.
[[examples_of_inet_aton]]
=== Examples of INET_ATON
```
>>SELECT INET_ATON('10.0.5.9') from dual;
(EXPR)
-----------
167773449
```
<<<
[[inet_ntoa_function]]
== INET_NTOA Function
Given a numeric IPv4 network address in network byte order, returns the
dotted-quad string representation of the address as a nonbinary string in
the connection character set. INET_NTOA() returns NULL if it does
not understand its argument.
```
INET_NTOA( expression )
```
* `_expression_`
+
specifies an expression that determines the values to include in the
conversion of the number to IP address. The _expression_ cannot contain
an aggregate function or a subquery. If the input value is NULL, INET_NTOA
returns NULL.
See <<expressions,Expressions>>.
[[examples_of_inet_ntoa]]
=== Examples of INET_NTOA
this function will convert an integer into the dotted-quad string
representation of the IP address.
```
>>SELECT INET_NTOA(167773449) from dual
(EXPR)
-------------
'10.0.5.9'
```
[[isnull_function]]
== ISNULL Function
The ISNULL function returns the value of the first argument if it is not
null, otherwise it returns the value of the second argument. Both
expressions must be of comparable types.
ISNULL is a {project-name} SQL extension.
```
ISNULL(ck-expr, repl-value)
```
* `_ck-expr_`
+
an expression of any valid SQL data type.
* `_repl-value_`
+
an expression of any valid SQL data type, but must be a comparable type
with that of _ck-expr_.
[[examples_of_isnull]]
=== Examples of ISNULL
* This function returns a 0 instead of a null if value is null.
+
```
ISNULL(value,0)
```
* This function returns the date constant if date_col is null.
+
```
ISNULL(date_col, DATE '2006-01-01')
```
* This function returns 'Smith' if the string column last_name is null.
+
```
ISNULL(last_name, 'Smith')
```
<<<
[[juliantimestamp_function]]
== JULIANTIMESTAMP Function
The JULIANTIMESTAMP function converts a datetime value into a 64-bit
Julian timestamp value that represents the number of microseconds that
have elapsed between 4713 B.C., January 1, 00:00, and the specified
datetime value. JULIANTIMESTAMP returns a value of data type LARGEINT.
The function is evaluated once when the query starts execution and is
not reevaluated (even if it is a long running query).
JULIANTIMESTAMP is a {project-name} SQL extension.
```
JULIANTIMESTAMP(datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a value of type DATE, TIME, or
TIMESTAMP. If _datetime-expression_ does not contain all the fields from YEAR through
SECOND, {project-name} SQL extends the value before converting it to a Julian
timestamp. Datetime fields to the left of the specified datetime value
are set to current date fields. Datetime fields to the right of the
specified datetime value are set to zero. See
<<datetime_value_expressions,Datetime Value Expressions>>.
[[considerations_for_juliantimestamp]]
=== Considerations for JULIANTIMESTAMP
The _datetime-expression_ value must be a date or timestamp value from
the beginning of year 0001 to the end of year 9999.
[[examples_of_juliantimestamp]]
=== Examples of JULIANTIMESTAMP
The project table consists of five columns using the data types NUMERIC,
VARCHAR, DATE, TIMESTAMP, and INTERVAL.
* Convert the TIMESTAMP value into a Julian timestamp representation:
+
```
SELECT ship_timestamp, JULIANTIMESTAMP (ship_timestamp)
FROM persnl.project
WHERE projcode = 1000;
SHIP_TIMESTAMP (EXPR)
-------------------------- --------------------
2008-04-21 08:15:00.000000 212075525700000000
--- 1 row(s) selected.
```
* Convert the DATE value into a Julian timestamp representation:
+
```
SELECT start_date, JULIANTIMESTAMP (start_date)
FROM persnl.project
WHERE projcode = 1000;
START_DATE (EXPR)
---------- --------------------
2008-04-10 212074545600000000
--- 1 row(s) selected.
```
<<<
[[lastnotnull_function]]
== LASTNOTNULL Function
The LASTNOTNULL function is a sequence function that returns the last
non-null value of a column in an intermediate result table ordered by a
SEQUENCE BY clause in a SELECT statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
LASTNOTNULL is a {project-name} SQL extension.
```
LASTNOTNULL(column-expression)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression. If only null values have been returned, LASTNOTNULL returns null.
[[examples_of_lastnotnull]]
=== Examples of LASTNOTNULL
* Return the last non-null value of a column:
+
```
SELECT LASTNOTNULL(I1) AS lastnotnull
FROM mining.seqfcn SEQUENCE BY ts;
lastnotnull
-----------
6215
6215
19058
19058
11966
--- 5 row(s) selected.
```
<<<
[[lcase_function]]
== LCASE Function
The LCASE function down-shifts alphanumeric characters. For
non-alphanumeric characters, LCASE returns the same character. LCASE can
appear anywhere in a query where a value can be used, such as in a
select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE
predicate, an expression, or as qualifying a new value in an UPDATE or
INSERT statement. The result returned by the LCASE function is equal to
the result returned by the <<lower_function,LOWER Function>>.
LCASE returns a string of fixed-length or variable-length character
data, depending on the data type of the input string.
LCASE is a {project-name} SQL extension.
```
LCASE (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression that specifies a string of
characters to down-shift. See
<<character_value_expressions,Character Value Expressions>>.
[[examples_of_lcase]]
=== Examples of LCASE
* Suppose that your CUSTOMER table includes an entry for Hotel Oregon.
Select the column CUSTNAME and return in uppercase and lowercase letters
by using the UCASE and LCASE functions:
+
```
SELECT custname,UCASE(custname),LCASE(custname) FROM sales.customer;
(EXPR) (EXPR) (EXPR)
--------------- --------------------- ------------------
... ... ...
Hotel Oregon HOTEL OREGON hotel oregon
--- 17 row(s) selected.
```
+
See <<ucase_function,UCASE Function>>.
<<<
[[left_function]]
=== LEFT Function
The LEFT function returns the leftmost specified number of characters
from a character expression. Every character, including multi-byte
characters, is treated as one character.
LEFT is a {project-name} SQL extension.
```
LEFT (character-expr, count)
```
* `_character-expr_`
+
specifies the source string from which to return the leftmost specified
number of characters. The source string is an SQL character value expression.
The operand is the result of evaluating _character-expr_.
See <<character_value_expressions,Character Value Expressions>>.
* `_count_`
+
specifies the number of characters to return from _character-expr_.
The number count must be a value of exact numeric data type greater
than or equal to 0 with a scale of zero.
[[examples_of_left]]
=== Examples of LEFT
* Return_'robert':
+
```
left('robert john smith', 6)
```
* Use the LEFT function to append the company name to the job
descriptions:
+
```
UPDATE persnl.job SET jobdesc = LEFT (jobdesc, 11) ||' COMNET';
SELECT jobdesc FROM persnl.job;
Job Description
------------------
MANAGER COMNET
PRODUCTION COMNET
ASSEMBLER COMNET
SALESREP COMNET
SYSTEM ANAL COMNET
ENGINEER COMNET
PROGRAMMER COMNET
ACCOUNTANT COMNET
ADMINISTRAT COMNET
SECRETARY COMNET
--- 10 row(s) selected.
```
<<<
[[locate_function]]
== LOCATE Function
The LOCATE function searches for a given substring in a character
string. If the substring is found, {project-name} SQL returns the character
position of the substring within the string. Every character, including
multi-byte characters, is treated as one character. The result returned
by the LOCATE function is equal to the result returned by the
<<position_function,Position Function>>.
LOCATE is a {project-name} SQL extension.
```
LOCATE(substring-expression,source-expression)
```
* `_substring-expression_`
+
is an SQL character value expression that specifies the substring to
search for in _source-expression_. The _substring-expression_ cannot be NULL.
See <<character_value_expressions,Character Value Expressions>>.
* `_source-expression_`
+
is an SQL character value expression that specifies the source string.
the _source-expression_ cannot be null.
See <<character_value_expressions,Character Value Expressions>>.
{project-name} SQL returns the result as a 2-byte signed integer with a scale
of zero. If substring-expression is not found in source-expression , {project-name}
SQL returns 0.
[[considerations_for_locate]]
=== Considerations for LOCATE
[[result_of_locate]]
==== Result of LOCATE
* If the length of _source-expression_ is zero and the length of
_substring-expression_ is greater than zero, {project-name} SQL returns 0.
* If the length of _substring-expression_ is zero, {project-name} SQL returns 1.
* If the length of _substring-expression_ is greater than the length of
_source-expression_, {project-name} SQL returns 0.
* If _source-expression_ is a null value, {project-name} SQL returns a null value.
[[using_ucase]]
==== Using UCASE
To ignore case in the search, use the UCASE function (or the LCASE
function) for both the _substring-expression_ and the _source-expression_.
[[examples_of_locate]]
=== Examples of LOCATE
* Return the value 8 for the position of the substring 'John' within the string:
+
```
LOCATE ('John','Robert John Smith')
```
* Suppose that the EMPLOYEE table has an EMPNAME column that contains
both the first and last names. This SELECT statement returns all records
in table EMPLOYEE that contain the substring 'SMITH', regardless of
whether the column value is in uppercase or lowercase characters:
+
```
SELECT * FROM persnl.employee
WHERE LOCATE ('SMITH',UCASE(empname)) > 0 ;
```
<<<
[[log_function]]
== LOG Function
The LOG function returns the natural logarithm of a numeric value
expression. LOG is a {project-name} SQL extension.
```
LOG (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the LOG function. The value of the argument must be greater
than zero. See <<numeric_value_expressions>>.
[[examples_of_log]]
=== Examples of LOG
* This function returns the value 6.93147180559945344e-001, or
approximately 0.69315:
+
```
LOG (2.0)
```
<<<
[[log10_function]]
=== LOG10 Function
The LOG10 function returns the base 10 logarithm of a numeric value
expression.
LOG10 is a {project-name} SQL extension.
```
LOG10 (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the LOG10 function. The value of the argument must be
greater than zero.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_log10]]
=== Examples of LOG10
* This function returns the value 1.39794000867203776E+000, or
approximately 1.3979:
+
```
LOG10 (25)
```
<<<
[[lower_function]]
== LOWER Function
The LOWER function down-shifts alphanumeric characters. For
non-alphanumeric characters, LOWER returns the same character. LOWER can
appear anywhere in a query where a value can be used, such as in a
select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE
predicate, an expression, or as qualifying a new value in an UPDATE or
INSERT statement. The result returned by the LOWER function is equal to
the result returned by the <<lcase_function,LCASE Function>>.
LOWER returns a string of fixed-length or variable-length character
data, depending on the data type of the input string.
```
LOWER (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression that specifies a string of
characters to down-shift.
See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_lower]]
=== Considerations for LOWER
For a UTF8 character expression, the LOWER function down-shifts all the
uppercase or title case characters in a given string to lowercase and
returns a character string with the same data type and character set as
the argument.
A lower case character is a character that has the "alphabetic" property
in Unicode Standard 2 whose Unicode name includes lower. An uppercase
character is a character that has the "alphabetic" property in the
Unicode Standard 2 and whose Unicode name includes _upper_. A title
case character is a character that has the Unicode "alphabetic" property
and whose Unicode name includes _title_.
<<<
[[examples_of_lower]]
=== Examples of LOWER
* Suppose that your CUSTOMER table includes an entry for Hotel Oregon.
Select the column CUSTNAME and return the result in uppercase and
lowercase letters by using the UPPER and LOWER functions:
+
```
SELECT custname,UPPER(custname),LOWER(custname) FROM sales.customer;
(EXPR) (EXPR) (EXPR)
----------------- ------------------- ---------------------
... ... ...
Hotel Oregon HOTEL OREGON hotel oregon
--- 17 row(s) selected.
```
See <<upper_function,UPPER Function>>.
<<<
[[lpad_function]]
== LPAD Function
The LPAD function pads the left side of a string with the specified
string. Every character in the string, including multi-byte characters,
is treated as one character.
LPAD is a {project-name} SQL extension.
```
LPAD (str, len [,padstr])
```
* `_str_`
+
can be an expression.
See <<character_value_expressions,Character Value Expressions>>.
* `_len_`
+
identifies the desired number of characters to be returned and can be an
expression but must be an integral value. If _len_ is equal to the
length of the string, no change is made. If _len_ is smaller than the
string size, the string is truncated.
* `_pad-character_`
+
can be an expression and may be a string.
[[examples_of_lpad]]
=== Examples of LPAD
* This function returns ' kite':
+
```
LPAD('kite', 7)
```
* This function returns 'ki':
+
```
LPAD('kite', 2)
```
* This function returns '0000kite':
+
```
LPAD('kite', 8, '0')
```
* This function returns 'go fly a kite':
+
```
LPAD('go fly a kite', 13, 'z')
```
* This function returns 'John,John, go fly a kite'':
+
```
LPAD('go fly a kite', 23, 'John,')
```
<<<
[[ltrim_function]]
== LTRIM Function
The LTRIM function removes leading spaces from a character string. If
you must remove any leading character other than space, use the TRIM
function and specify the value of the character. See the <<trim_function,TRIM Function>>.
LTRIM is a {project-name} SQL extension.
```
LTRIM (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression and specifies the string from which
to trim leading spaces.
See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_ltrim]]
=== Considerations for LTRIM
[[result_of_ltrim]]
==== Result of LTRIM
The result is always of type VARCHAR, with maximum length equal to the
fixed length or maximum variable length of _character-expression_.
[[examples_of_ltrim]]
=== Examples of LTRIM
* Return 'Robert ':
+
```
LTRIM (' Robert ')
```
See <<trim_function,TRIM Function>> and <<rtrim_function,RTRIM Function>>.
<<<
[[max_function]]
== MAX/MAXIMUM Function
MAX is an aggregate function that returns the maximum value within a set
of values. MAXIMUM is the equivalent of MAX wherever the function name
MAX appears within a statement. The data type of the result is the same
as the data type of the argument.
```
MAX | MAXIMUM ([ALL | DISTINCT] expression)
```
* `ALL | DISTINCT`
+
specifies whether duplicate values are included in the computation of
the maximum of the _expression_. The default option is ALL, which
causes duplicate values to be included. If you specify DISTINCT,
duplicate values are eliminated before the MAX/MAXIMUM function is
applied.
* `_expression_`
+
specifies an expression that determines the values to include in the
computation of the maximum. The _expression_ cannot contain an aggregate
function or a subquery. The DISTINCT clause specifies that the
MAX/MAXIMUM function operates on distinct values from the one-column
table derived from the evaluation of _expression_. All nulls are
eliminated before the function is applied to the set of values. If the
result table is empty, MAX/MAXIMUM returns NULL.
See <<expressions,Expressions>>.
[[considerations_for_max]]
=== Considerations for MAX/MAXIMUM
[[operands_of_the_expression]]
=== Operands of the Expression
The expression includes columns from the rows of the SELECT result table
but cannot include an aggregate function. These expressions are valid:
```
MAX (SALARY)
MAX (SALARY * 1.1)
MAX (PARTCOST * QTY_ORDERED)
```
[[examples_of_max]]
=== Examples of MAX/MAXIMUM
* Display the maximum value in the SALARY column:
+
```
SELECT MAX (salary) FROM persnl.employee;
(EXPR)
-----------
175500.00
--- 1 row(s) selected.
```
<<<
[[md5_function]]
== MD5 Function
Calculates an MD5 128-bit checksum for the string. The value is returned
as a string of 32 hexadecimal digits, or NULL if the argument was NULL.
```
MD5( _expression_)
```
* `_expression_`
+
specifies an expression that determines the values to include in the
computation of the MD5. The _expression_ cannot contain an aggregate
function or a subquery. If the input value is NULL, MD5 returns NULL.
See <<expressions,Expressions>>.
[[examples_of_md5]]
=== Examples of MD5
The return value is a nonbinary string in the connection character set.
```
>>SELECT MD5('testing') from dual;
(EXPR)
---------------------------------
'ae2b1fca515949e5d54fb22b8ed95575'
```
<<<
[[min_function]]
== MIN Function
MIN is an aggregate function that returns the minimum value within a set
of values. The data type of the result is the same as the data type of
the argument.
```
MIN ([ALL | DISTINCT] _expression_)
```
* `ALL | DISTINCT`
+
specifies whether duplicate values are included in the computation of
the minimum of the _expression_. The default option is ALL, which
causes duplicate values to be included. If you specify DISTINCT,
duplicate values are eliminated before the MIN function is applied.
* `_expression_`
+
specifies an expression that determines the values to include in the
computation of the minimum. The _expression_ cannot contain an aggregate
function or a subquery. The DISTINCT clause specifies that the MIN
function operates on distinct values from the one-column table derived
from the evaluation of _expression_. All nulls are eliminated before
the function is applied to the set of values. If the result table is
empty, MIN returns NULL.
See <<expressions,Expressions>>.
[[considerations_for_min]]
=== Considerations for MIN
[[operands_of_the_expression_3]]
==== Operands of the Expression
The expression includes columns from the rows of the SELECT result
table &#8212; but cannot include an aggregate function. These expressions are
valid:
```
MIN (SALARY)
MIN (SALARY * 1.1)
MIN (PARTCOST * QTY_ORDERED)
```
<<<
[[examples_of_min]]
=== Examples of MIN
* Display the minimum value in the SALARY column:
+
```
SELECT MIN (salary) FROM persnl.employee;
(EXPR)
-----------
17000.00
--- 1 row(s) selected.
```
<<<
[[minute_function]]
== MINUTE Function
The MINUTE function converts a TIME or TIMESTAMP expression into an
INTEGER, in the range 0 through 59, that represents the
corresponding minute of the hour.
MINUTE is a {project-name} SQL extension.
```
MINUTE (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type TIME or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_minute]]
=== Examples of minute
* Return an integer that represents the minute of the hour from the
ship timestamp column in the project table:
+
```
SELECT start_date, ship_timestamp, MINUTE(ship_timestamp)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 15
```
<<<
[[mod_function]]
== MOD Function
The MOD function returns the remainder (modulus) of an integer value
expression divided by an integer value expression.
MOD is a {project-name} SQL extension.
```
MOD (integer-expression-1,integer-expression-2)
```
* `_integer-expression-1_`
+
is an SQL numeric value expression of data type SMALLINT, INTEGER, or
LARGEINT that specifies the value for the dividend argument of the MOD
function.
* `_integer-expression-2_`
+
is an SQL numeric value expression of data type SMALLINT, INTEGER, or
LARGEINT that specifies the value for the divisor argument of the MOD
function. The divisor argument cannot be zero.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_mod]]
=== Examples of MOD
* This function returns the value 2 as the remainder or modulus:
+
```
MOD(11,3)
```
<<<
[[month_function]]
== MONTH Function
The MONTH function converts a DATE or TIMESTAMP expression into an
INTEGER value in the range 1 through 12 that represents the
corresponding month of the year.
MONTH is a {project-name} SQL extension.
```
MONTH (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_month]]
=== Examples of MONTH
* Return an integer that represents the month of the year from the
start date column in the project table:
+
```
SELECT start_date, ship_timestamp, MONTH(start_date) FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 4
```
<<<
[[monthname_function]]
== MONTHNAME Function
The MONTHNAME function converts a DATE or TIMESTAMP expression into a
character literal that is the name of the month of the year (January,
February, and so on).
MONTHNAME is a {project-name} SQL extension.
```
MONTHNAME (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[considerations_for_monthname]]
=== Considerations for MONTHNAME
The MONTHNAME function returns the name of the month in ISO88591.
[[examples_of_monthname]]
=== Examples of MONTHNAME
* Return a character literal that is the month of the year from the
start date column in the project table:
+
```
SELECT start_date, ship_timestamp, MONTHNAME(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ---------
2008-04-10 2008-04-21 08:15:00.000000 April
```
<<<
[[movingavg_function]]
== MOVINGAVG Function
The MOVINGAVG function is a sequence function that returns the average
of non-null values of a column in the current window of an intermediate
result table ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGAVG is a {project-name} SQL extension.
```
MOVINGAVG(column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
integer-_expression_ and _max-rows_.
* If these conditions are met, MOVINGAVG returns the same result as
RUNNINGAVG:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[examples_of_movingavg]]
=== Examples of MOVINGAVG
* Return the average of non-null values of a column in the current window
of three rows:
+
```
CREATE TABLE db.mining.seqfcn (I1 INTEGER, ts TIMESTAMP);
SELECT MOVINGAVG (I1,3) AS MOVINGAVG3
FROM mining.seqfcn SEQUENCE BY ts;
I1 TS
6215 TIMESTAMP '1950-03-05 08:32:09'
28174 TIMESTAMP '1951-02-15 14:35:49'
null TIMESTAMP '1955-05-18 08:40:10'
4597 TIMESTAMP '1960-09-19 14:40:39'
11966 TIMESTAMP '1964-05-01 16:41:02'
MOVINGAVG3
---------------------
6215
17194
17194
16385
8281
--- 5 row(s) selected.
```
<<<
[[movingcount_function]]
== MOVINGCOUNT Function
The MOVINGCOUNT function is a sequence function that returns the number
of non-null values of a column in the current window of an intermediate
result table ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGCOUNT is a {project-name} SQL extension.
```
MOVINGCOUNT (column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
_integer-expression_ and _max-rows_.
* If these conditions are met, MOVINGCOUNT returns the same result as
RUNNINGCOUNT:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[considerations_for_movingcount]]
=== Considerations for MOVINGCOUNT
The MOVINGCOUNT sequence function is defined differently from the COUNT
aggregate function. If you specify DISTINCT for the COUNT aggregate
function, duplicate values are eliminated before COUNT is applied. You
cannot specify DISTINCT for the MOVINGCOUNT sequence function; duplicate
values are counted.
[[examples_of_movingcount]]
=== Examples of MOVINGCOUNT
* Return the number of non-null values of a column in the current window of
three rows:
+
```
SELECT MOVINGCOUNT (I1,3) AS MOVINGCOUNT3
FROM mining.seqfcn SEQUENCE BY ts;
MOVINGCOUNT3
------------
1
2
2
2
2
--- 5 row(s) selected.
```
<<<
[[movingmax_function]]
== MOVINGMAX Function
The MOVINGMAX function is a sequence function that returns the maximum
of non-null values of a column in the current window of an intermediate
result table ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGMAX is a {project-name} SQL extension.
```
MOVINGMAX (column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
_integer-expression_ and _max-rows_.
* If these conditions are met, MOVINGMAX returns the same result as
RUNNINGMAX:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[examples_of_movingmax]]
=== Examples of MOVINGMAX
* Return the maximum of non-null values of a column in the current window
of three rows:
+
```
SELECT MOVINGMAX (I1,3) AS MOVINGMAX3
FROM mining.seqfcn SEQUENCE BY ts;
MOVINGMAX3
------------
6215
28174
28174
28174
11966
--- 5 row(s) selected.
```
<<<
[[movingmin_function]]
== MOVINGMIN Function
The MOVINGMIN function is a sequence function that returns the minimum
of non-null values of a column in the current window of an intermediate
result table ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGMIN is a {project-name} SQL extension.
```
MOVINGMIN (column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
_integer-expression_ and _max-rows_.
* If these conditions are met, MOVINGMIN returns the same result as
RUNNINGMIN:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[examples_of_movingmin]]
=== Examples of MOVINGMIN
* Return the minimum of non-null values of a column in the current window
of three rows:
+
```
SELECT MOVINGMIN (I1,3) AS MOVINGMIN3
FROM mining.seqfcn SEQUENCE BY ts;
MOVINGMIN3
------------
6215
6215
6215
4597
4597
--- 5 row(s) selected.
```
<<<
[[movingstddev_function]]
== MOVINGSTDDEV Function
The MOVINGSTDDEV function is a sequence function that returns the
standard deviation of non-null values of a column in the current window
of an intermediate result table ordered by a SEQUENCE BY clause in a
SELECT statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGSTDDEV is a {project-name} SQL extension.
```
MOVINGSTDDEV (column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
_integer-expression_ and _max-rows_.
* If these conditions are met, MOVINGSTDDEV returns the same result as
RUNNINGSTDDEV:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[examples_of_movingstddev]]
=== Examples of MOVINGSTDDEV
* Return the standard deviation of non-null values of a column in the
current window of three rows:
+
```
SELECT MOVINGSTDDEV (I1,3) AS MOVINGSTDDEV3
FROM mining.seqfcn SEQUENCE BY ts;
MOVINGSTDDEV3
-------------------------
0.00000000000000000E+000
1.55273578080753976E+004
1.48020166531456112E+004
1.51150124820766640E+004
6.03627542446499008E+003
--- 5 row(s) selected.
```
* You can use the CAST function for display purposes. For example:
+
```
SELECT CAST(MOVINGSTDDEV (I1,3) AS DEC (18,3))
FROM mining.seqfcn SEQUENCE BY ts;
(EXPR)
--------------------
.000
15527.357
14802.016
15115.012
6036.275
--- 5 row(s) selected.
```
<<<
[[movingsum_function]]
== MOVINGSUM Function
The MOVINGSUM function is a sequence function that returns the sum of
non-null values of a column in the current window of an intermediate
result table ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGSUM is a {project-name} SQL extension.
```
MOVINGSUM (column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
_integer-expression_ and _max-rows_.
* If these conditions are met, MOVINGSUM returns the same result as
RUNNINGSUM:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[examples_of_movingsum]]
=== Examples of MOVINGSUM
* Return the sum of non-null values of a column in the current window of
three rows:
+
```
SELECT MOVINGSUM (I1,3) AS MOVINGSUM3
FROM mining.seqfcn SEQUENCE BY ts;
MOVINGSUM3
------------
6215
34389
34389
32771
16563
--- 5 row(s) selected.
```
<<<
[[movingvariance_function]]
== MOVINGVARIANCE Function
The MOVINGVARIANCE function is a sequence function that returns the
variance of non-null values of a column in the current window of an
intermediate result table ordered by a SEQUENCE BY clause in a SELECT
statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
MOVINGVARIANCE is a {project-name} SQL extension.
```
MOVINGVARIANCE (column-expression, integer-expression [, max-rows])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_integer-expression_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the current window. The current window is defined
as the current row and the previous (_integer-expression_ - 1) rows.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows in the current window.
Note these considerations for the window size:
* The actual value for the window size is the minimum of
_integer-expression_ and _max-rows_.
* If these conditions are met, MOVINGVARIANCE returns the same result as
RUNNINGVARIANCE:
** The _integer-expression_ is out of range, and _max-rows_ is not
specified. This condition includes the case in which both
_integer-expression_ and _max-rows_ are larger than the result table.
** The minimum of _integer-expression_ and _max-rows_ is out of range.
In this case, _integer-expression_ could be within range, but _max-rows_
might be the minimum value of the two and be out of range (for example,
a negative number).
* The number of rows is out of range if it is larger than the size of
the result table, negative, or NULL.
<<<
[[examples_of_movingvariance]]
=== Examples of MOVINGVARIANCE
* Return the variance of non-null values of a column in the current window
of three rows:
+
```
SELECT MOVINGVARIANCE (I1,3) AS MOVINGVARIANCE3
FROM mining.seqfcn SEQUENCE BY ts;
MOVINGVARIANCE3
-------------------------
0.00000000000000000E+000
2.41098840499999960E+008
2.19099696999999968E+008
2.28463602333333304E+008
3.64366210000000016E+007
--- 5 row(s) selected.
```
* You can use the CAST function for display purposes. For example:
+
```
SELECT CAST(MOVINGVARIANCE (I1,3) AS DEC (18,3))
FROM mining.seqfcn SEQUENCE BY ts;
(EXPR)
--------------------
.000
241098840.500
219099697.000
228463602.333
36436621.000
--- 5 row(s) selected.
```
<<<
[[nullif_function]]
=== NULLIF Function
The NULLIF function compares the value of two expressions. Both
expressions must be of comparable types. The return value is NULL when
the two expressions are equal. Otherwise, the return value
is the value of the first expression.
```
NULLIF(expr1, expr2)
```
* `_expr1_`
+
an expression to be compared.
* `_expr2_`
+
an expression to be compared.
The NULLIF(_expr1_, _expr2_) is equivalent to:
```
CASE
WHEN expr1 = expr2 THEN NULL
ELSE expr1
END
```
NULLIF returns a NULL if both arguments are equal. The return value is
the value of the first argument when the two expressions are not equal.
[[examples_of_nullif]]
=== Examples of NULLIF
* This function returns a null if the _value_ is equal to 7. The return
value is the value of the first argument when that value is not 7.
+
```
NULLIF(value,7)
```
<<<
[[nullifzero_function]]
== NULLIFZERO Function
The NULLIFZERO function returns the value of the expression if that
value is not zero. It returns NULL if the value of the expression is
zero.
```
NULLIFZERO (expression)
```
* `_expression_`
+
specifies a value expression. It must be a numeric data type.
<<<
[[examples_of_nullifzero]]
=== Examples of NULLIFZERO
* This function returns the value of the column named salary for each
row where the column's value is not zero. It returns a NULL for each row
where the column's value is zero.
+
```
SELECT NULLIFZERO(salary) FROM employee_tab;
```
* This function returns a value of 1 for each row of the table:
+
```
SELECT NULLIFZERO(1) FROM employee_tab;
```
* This function returns a value of NULL for each row of the table:
+
```
SELECT NULLIFZERO(0) FROM employee_tab;
```
<<<
[[nvl_function]]
== NVL Function
The NVL function determines if the selected column has a null value and
then returns the new-operand value; otherwise the operand value is
returned.
```
NVL (operand, new-operand)
```
* `_operand_`
+
specifies a value expression.
* `_new-operand_`
+
specifies a value expression. _operand_ and _new-operand_ must be
comparable data types.
If _operand_ is a null value, NVL returns _new-operand_. If _operand_
is not a null value, NVL returns _operand_.
The _operand_ and _new-operand_ can be a column name, subquery,
{project-name} SQL string functions, math functions, or constant values.
[[examples_of_nvl]]
=== Examples of NVL
* This function returns a value of z:
+
```
SELECT NVL(CAST(NULL AS CHAR(1)), 'z') FROM (VALUES(1)) x(a);
(EXPR)
------
"z"
--- 1 row(s) selected.
```
* This function returns a value of 1:
+
```
SELECT NVL(1, 2) FROM (VALUES(0)) x(a)
(EXPR)
-------
1
--- 1 row(s) selected.
```
* This function returns a value of 9999999 for the null value in the
column named a1:
+
```
SELECT NVL(a1, 9999999) from t1;
(EXPR)
-------
123
34
9999999
--- 3 row(s) selected.
select * from t1;
A1
-------
123
34
?
--- 3 row(s) selected.
```
<<<
[[octet_length_function]]
== OCTET_LENGTH Function
The OCTET_LENGTH function returns the length of a character string in
bytes.
```
OCTET_LENGTH (string-value-expression)
```
* `_string-value-expression_`
+
specifies the string value expression for which to return the length in
bytes. {project-name} SQL returns the result as a 2-byte signed integer with
a scale of zero. If _string-value-expression_ is null, {project-name} SQL returns
a length of zero.
See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_octet_length]]
=== Considerations for OCTET_LENGTH
[[char_and_varchar_operands_1]]
==== CHAR and VARCHAR Operands
For a column declared as fixed CHAR, {project-name} SQL returns the length of
that column as the maximum number of storage bytes. For a VARCHAR
column, {project-name} SQL returns the length of the string stored in that
column as the actual number of storage bytes.
[[similarity_to_char_length_function]]
==== Similarity to CHAR_LENGTH Function
The OCTET_LENGTH and CHAR_LENGTH functions are similar. The OCTET_LENGTH
function returns the number of bytes, rather than the number of
characters, in the string. This distinction is important for multi-byte
implementations. For an example of selecting a double-byte column, see
<<examples_of_octet_length,Example of OCTET_LENGTH>>.
[[examples_of_octet_length]]
==== Examples of OCTET_LENGTH
* If a character string is stored as two bytes for each character, this
function returns the value 12. Otherwise, the function returns 6:
+
```
OCTET_LENGTH ('Robert')
```
<<<
[[offset_function]]
=== OFFSET Function
The OFFSET function is a sequence function that retrieves columns from
previous rows of an intermediate result table ordered by a SEQUENCE BY
clause in a SELECT statement. See <<sequence_by
clause>>._offset_is_a_trafodion_sql_extension.
```
OFFSET ( column-expression , number-rows [, max-rows ])
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
* `_number-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the offset as the number of rows from the current
row. If the number of rows exceeds _max-rows_, OFFSET returns
OFFSET(_column-expression_,_max-rows_). If the number of rows is out
of range and _max-rows_ is not specified or is out of range, OFFSET
returns null. The number of rows is out of range if it is larger than
the size of the result table, negative, or NULL.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows of the offset.
[[examples_of_offset]]
=== Examples of OFFSET
* Retrieve the I1 column offset by three rows:
+
```
SELECT OFFSET(I1,3) AS offset3
FROM mining.seqfcn SEQUENCE BY ts;
offset3
------------
?
?
?
6215
28174
--- 5 row(s) selected.
```
+
The first three rows retrieved display null because the offset from the
current row does not fall within the result table.
<<<
[[pi_function]]
== PI Function
The PI function returns the constant value of pi as a floating-point
value.
PI is a {project-name} SQL extension.
```
PI()
```
[[examples_of_pi]]
=== Examples of PI
* This constant function returns the value 3.14159260000000000E+000:
+
```
PI()
```
<<<
[[position_function]]
== POSITION Function
The POSITION function searches for a given substring in a character
string. If the substring is found, {project-name} SQL returns the character
position of the substring within the string. Every character, including
multi-byte characters, is treated as one character. The result returned
by the POSITION function is equal to the result returned by the
<<locate_function,LOCATE Function>>.
```
POSITION (substring-expression IN source-expression)
```
* `_substring-expression_`
+
is an SQL character value expression that specifies the substring to
search for in _source-expression_. The _substring-expression_ cannot be NULL.
See <<character_value_expressions,Character Value Expressions>>.
* `_source-expression_`
+
is an SQL character value expression that specifies the source string.
the _source-expression_ cannot be null.
See <<character_value_expressions,Character Value Expressions>>.
{project-name} SQL returns the result as a 2-byte signed integer with a scale
of zero. If _substring-expression_ is not found in _source-expression_,
{project-name} SQL returns zero.
[[considerations_for_position]]
=== Considerations for POSITION
[[result_of_position]]
==== Result of POSITION
If the length of _source-expression_ is zero and the length of
_substring-expression_ is greater than zero, {project-name} SQL returns 0. If
the length of _substring-expression_ is zero, {project-name} SQL returns 1.
If the length of _substring-expression_ is greater than the length of
_source-expression_, {project-name} SQL returns zero. If
_source-expression_ is a null value, {project-name} SQL returns a null value.
[[using_the_upshift_function]]
==== Using the UPSHIFT Function
To ignore case in the search, use the UPSHIFT function (or the LOWER
function) for both the _substring-expression_ and the _source-expression_.
[[examples_of_position]]
=== Examples of POSITION
* This function returns the value 8 for the position of the substring
'John' within the string:
+
```
POSITION ('John' IN 'Robert John Smith')
```
* Suppose that the EMPLOYEE table has an EMPNAME column that contains
both the first and last names. Return all records in table EMPLOYEE that
contain the substring 'Smith' regardless of whether the column value is
in uppercase or lowercase characters:
+
```
SELECT * FROM persnl.employee
WHERE POSITION ('SMITH' IN UPSHIFT(empname)) > 0 ;
```
<<<
[[power_function]]
== POWER Function
The POWER function returns the value of a numeric value expression
raised to the power of an integer value expression. You can also use the
exponential operator \*\*.
POWER is a {project-name} SQL extension.
```
POWER (numeric-expression-1, numeric-expression-2)
```
* `_numeric-expression-1_, _numeric-expression-2_`
+
are SQL numeric value expressions that specify the values for the base
and exponent arguments of the POWER function. See
<<numeric_value_expressions>>.
+
If base _numeric-expression-1 _is_zero, the exponent _numeric-expression-2_
must be greater than zero, and the result is zero. If the exponent is zero,
the base cannot be 0, and the result is 1. If the base is negative, the
exponent must be a value with an exact numeric data type and a scale of zero.
[[examples_of_power]]
=== Examples of POWER
* Return the value 15.625:
+
```
POWER (2.5,3)
```
* Return the value 27. The function POWER raised to the power of 2 is
the inverse of the function SQRT:
+
```
POWER (SQRT(27),2)
```
<<<
[[quarter_function]]
== QUARTER Function
The QUARTER function converts a DATE or TIMESTAMP expression into an
INTEGER value in the range 1 through 4 that represents the corresponding
quarter of the year. Quarter 1 represents January 1 through March 31,
and so on.
QUARTER is a {project-name} SQL extension.
```
QUARTER (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_quarter]]
=== Examples of QUARTER
* Return an integer that represents the quarter of the year from the
START_DATE column in the PROJECT table:
+
```
SELECT start_date, ship_timestamp, QUARTER(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 2
```
<<<
[[radians_function]]
== RADIANS Function
The RADIANS function converts a numeric value expression (expressed in
degrees) to the number of radians.
RADIANS is a {project-name} SQL extension.
```
RADIANS (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the RADIANS function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_radians]]
=== Examples of RADIANS
* Return the value 7.85398150000000000E-001, or approximately 0.78540 in degrees:
+
```
RADIANS (45)
```
* Return the value 45 in degrees. The function DEGREES is the inverse of
the function RADIANS.
+
```
DEGREES (RADIANS (45))
```
<<<
[[runningrank_function]]
== RANK/RUNNINGRANK Function
The RANK/RUNNINGRANK function is a sequence function that returns the
rank of the given value of an intermediate result table ordered by a
SEQUENCE BY clause in a SELECT statement. RANK is an alternative syntax
for RANK/RUNNINGRANK.
RANK/RUNNINGRANK is a {project-name} extension.
```
RUNNINGRANK(expression) | RANK(expression)
```
* _expression_
+
specifies the expression on which to perform the rank.
RANK/RUNNINGRANK returns the rank of the expression within the
intermediate result table. The definition of rank is as follows:
```
RANK = 1 for the first value of the intermediate result table.
= the previous value of RANK if the previous value of expression is
the same as the current value of expression.
= RUNNINGCOUNT(*) otherwise.
```
In other words, RANK starts at 1. Values that are equal have the same
rank. The value of RANK advances to the relative position of the row in
the intermediate result when the value changes.
[[considerations_for_runningrank]]
=== Considerations for RANK/RUNNINGRANK
[[sequence_order_dependency]]
==== Sequence Order Dependency
The RUNNINGRANK function is meaningful only when the given expression is
the leading column of the SEQUENCE BY clause. This is because the
RUNNINGRANK function assumes that the values of expression are in order
and that like values are contiguous. If an ascending order is specified
for expression in the SEQUENCE BY clause, then the RUNNINGRANK function
assigns a rank of 1 to the lowest value of expression. If a descending
order is specified for expression in the SEQUENCE BY clause, then the
RUNNINGRANK function assigns a rank of 1 to the highest value of
expression.
[[runningrank_null_values]]
==== NULL Values
For the purposes of RUNNINGRANK, NULL values are considered to be equal.
[[examples_of_runningrank]]
=== Examples of RANK/RUNNINGRANK
* Suppose that _seqfcn_ has been created as:
+
```
CREATE TABLE cat.sch.seqfcn (i1 INTEGER, i2 INTEGER);
```
+
The table SEQFCN has columns _i1_ and _i2_ with data:
+
[cols="15%,85%",options="header"]
|===
| i1 | i2
| 1 | 100
| 3 | 200
| 4 | 100
| 2 | 200
| 5 | 300
| 10 | null
|===
* Return the rank of _i1_:
+
```
SELECT i1, RUNNINGRANK(i1) AS rank
FROM cat.sch.seqfcn SEQUENCE BY i1;
i1 rank
----------- --------------------
1 1
2 2
3 3
4 4
5 5
6 6
8 7
10 8
--- 8 row(s) selected.
```
<<<
* Return the rank of _i1_ descending:
+
```
SELECT i1, RUNNINGRANK (i1) AS rank
FROM cat.sch.seqfcn SEQUENCE BY i1 DESC;
i1 rank
----------- --------------------
10 1
8 2
6 3
5 4
4 5
3 6
2 7
1 8
--- 8 row(s) selected.
```
* Return the rank of _i2_, using the alternative RANK syntax:
+
```
SELECT i2, RANK (i2) AS rank
FROM cat.sch.seqfcn SEQUENCE BY i2;
i2 rank
----------- --------------------
100 1
100 1
200 3
200 3
200 3
300 6
? 7
? 7
--- 8 row(s) selected.
```
+
Notice that the two NULL values received the same rank.
<<<
* Return the rank of _i2_ descending, using the alternative RANK syntax:
+
```
SELECT i2, RANK (i2) AS rank
FROM cat.sch.seqfcn SEQUENCE BY i2 DESC;
i2 rank
----------- --------------------
? 1
? 1
300 3
200 4
200 4
200 4
100 7
100 7
--- 8 row(s) selected.
```
* Return the rank of _i2_ descending, excluding NULL values:
+
```
SELECT i2, RANK (i2) AS rank
FROM cat.sch.seqfcn WHERE i2 IS NOT NULL SEQUENCE BY i2 DESC;
i2 rank
----------- --------------------
300 1
200 2
200 2
200 2
100 5
100 5
--- 6 row(s) selected.
```
<<<
[[repeat_function]]
== REPEAT Function
The REPEAT function returns a character string composed of the
evaluation of a character expression repeated a specified number of
times.
REPEAT is a {project-name} SQL extension.
```
REPEAT (character-expr, count)
```
* `_character-expr_`
+
specifies the source string from which to return the specified number of
repeated strings. The source string is an SQL character value expression.
The operand is the result of evaluating _character-expr_.
See <<character_value_expressions,Character Value Expressions>>.
* `_count_`
specifies the number of times the source string _character-expr_ is to
be repeated. The number count must be a value greater than or equal
to zero of exact numeric data type and with a scale of zero.
[[examples_of_repeat]]
=== Examples of REPEAT
* Return this quote from Act 5, Scene 3, of King Lear:
+
```
REPEAT ('Never,', 5)
Never,Never,Never,Never,Never,
```
<<<
[[replace_function]]
== REPLACE Function
The REPLACE function returns a character string where all occurrences of
a specified character string in the original string are replaced with
another character string. All three character value expressions must be
comparable types. The return value is the VARCHAR type.
REPLACE is a {project-name} SQL extension.
```
REPLACE (char-expr-1, char-expr-2, char-expr-3)
```
* `_char-expr-1_, _char-expr-2_, _char-expr-3_`
+
are SQL character value expressions. The operands are the result of
evaluating the character expressions. All occurrences of _char-expr-2_
in _char-expr-1_ are replaced by _char-expr-3_.
See <<character_value_expressions,Character Value Expressions>>.
[[examples_of_replace]]
=== Examples of REPLACE
* Use the REPLACE function to change job descriptions so that occurrences
of the company name are updated:
+
```
SELECT jobdesc FROM persnl.job;
job_description
------------------
MANAGER COMNET
PRODUCTION COMNET
ASSEMBLER COMNET
SALESREP COMNET
SYSTEM ANAL COMNET
...
--- 10 row(s) selected.
UPDATE persnl.job
SET jobdesc = REPLACE(jobdesc, 'COMNET', 'TDMNET');
Job Description
------------------
MANAGER TDMNET
PRODUCTION TDMNET
ASSEMBLER TDMNET
SALESREP TDMNET
SYSTEM ANAL TDMNET
...
--- 10 row(s) selected.
```
<<<
[[right_function]]
== RIGHT Function
The RIGHT function returns the rightmost specified number of characters
from a character expression. Every character, including multi-byte
characters, is treated as one character.
RIGHT is a {project-name} SQL extension.
```
RIGHT (character-expr, count)
```
* `_character-expr_`
+
specifies the source string from which to return the rightmost specified
number of characters. The source string is an SQL character value expression.
The operand is the result of evaluating _character-expr_.
See <<character_value_expressions,Character Value Expressions>>.
* `_count_`
+
specifies the number of characters to return from _character-expr_.
The number count must be a value of exact numeric data type with a scale
of zero.
[[examples_of_right]]
=== Examples of RIGHT
* Return 'smith':
+
```
RIGHT('robert_john_smith', 5)
```
* Suppose that a six-character company literal has been concatenated as
the first six characters to the job descriptions in the JOB table. Use
the RIGHT function to remove the company literal from the job
descriptions:
+
```
UPDATE persnl.job
SET jobdesc = RIGHT (jobdesc, 12);
```
<<<
[[rollup_function]]
== ROLLUP Function
The ROLLUP function calculates multiple levels of subtotals aggregating from right to left through the comma-separated list of columns, and provides a grand total.
ROLLUP is an extension to the `GROUP BY` clause. Related features such as the GROUPING function can be used with `ORDER BY` to control the placement of summary results.
```
SELECT…GROUP BY ROLLUP (column 1, [column 2,]…[column n])
```
ROLLUP generates n+1 levels of subtotals, including a grand total, where n is the number of the selected column(s).
For example, a query that contains three rollup columns returns the following rows:
* First-level: the usual aggregate values as calculated by GROUP BY clause without using ROLLUP.
* Second-level: subtotals aggregating across column 3 for each combination of column 1 and column 2.
* Third-level: subtotals aggregating across column 2 and column 3 for each column 1.
* Fourth-level: the grand total row.
NOTE: Trafodion does not support CUBE function which works slightly differently from ROLLUP.
[[considerations_for_rollup]]
=== Considerations for ROLLUP
[[null_in_result_sets]]
==== NULL in Result Sets
* In super-aggregate rows representing subtotals or the grand total, lower level grouping columns are replaced by NULLs.
* The NULLs in selected columns are considered equal and sorted into one NULL group in result sets.
[[using_rollup_with_the_column_order_reversed]]
==== Using ROLLUP with the Column Order Reversed
ROLLUP removes the right-most column at each step, therefore the result sets vary with the column order specified in the comma-separated list.
[cols="50%,50%"]
|===
| If the column order is _country_, _state_, _city_ and _name_, ROLLUP returns following groupings.
| If the column order is _name_, _city_, _state_ and _country_, ROLLUP returns following groupings.
| _country_, _state_, _city_ and _name_ | _name_, _city_, _state_ and _country_
| _country_, _state_ and _city_ | _name_, _city_ and _state_
| _country_ and _state_ | _name_ and _city_
| _country_ | _name_
| grand total | grand total
|===
[[examples_of_rollup]]
=== Examples of ROLLUP
[[examples_of_grouping_by_one_or_multiple_rollup_columns]]
==== Examples of Grouping By One or Multiple Rollup Columns
Suppose that we have a _sales1_ table like this:
```
SELECT * FROM sales1;
DELIVERY_YEAR REGION PRODUCT REVENUE
------------- ------ -------------------------------- -----------
2016 A Dress 100
2016 A Dress 200
2016 A Pullover 300
2016 B Dress 400
2017 A Pullover 500
2017 B Dress 600
2017 B Pullover 700
2017 B Pullover 800
--- 8 row(s) selected.
```
* This is an example of grouping by one rollup column.
+
```
SELECT delivery_year, SUM (revenue) AS total_revenue
FROM sales1
GROUP BY ROLLUP (delivery_year);
```
+
```
DELIVERY_YEAR TOTAL_REVENUE
------------- --------------------
2016 1000
2017 2600
NULL 3600
--- 3 row(s) selected.
```
* This is an example of grouping by two rollup columns.
+
ROLLUP firstly aggregates at the lowest level (_region_) and then rolls up those aggregations to the next
level (_delivery_year_), finally it produces a grand total across these two levels.
+
```
SELECT delivery_year, region, SUM (revenue) AS total_revenue
FROM sales1
GROUP BY ROLLUP (delivery_year, region);
```
+
```
DELIVERY_YEAR REGION TOTAL_REVENUE
------------- ------ --------------------
2016 A 600
2016 B 400
2016 NULL 1000
2017 A 500
2017 B 2100
2017 NULL 2600
NULL NULL 3600
--- 7 row(s) selected.
```
+
* This is an example of grouping by three rollup columns.
+
```
SELECT delivery_year, region, product, SUM (revenue) AS total_revenue
FROM sales1
GROUP BY ROLLUP (delivery_year, region, product);
```
+
.Grouping By Three Rollup Columns
image::grouping-by-three-rollup-columns.jpg[700,700]
+
** First-level: the rows marked in *blue* are the total revenue for each year (_2016_ and _2017_), each region (_A_ and _B_) and each product (_Dress_ and _Pullover_), they are caculated by GROUP BY instead of ROLLUP.
+
** Second-level: the rows marked in *red* provide the total revenue for the given _delivery_year_ and _region_ by _product_.
+
These rows have the _product_ columns set to NULL.
+
** Third-level: the rows marked in *yellow* show the total revenue in each year (_2016_ and _2017_).
+
These rows have the _region_ and _product_ columns set to NULL.
+
** Fourth-level: the row marked in *purple* aggregates over all rows in the _delivery_year_, _region_ and _product_ columns.
+
This row has the _delivery_year_, _region_ and _product_ columns set to NULL.
[[examples_of_null]]
==== Examples of NULL
The example below demonstrates how ROLLUP treats NULLs in the selected columns and generates NULLs for super-aggregate rows.
Suppose that we have a _sales2_ table like this:
```
SELECT * FROM sales2;
DELIVERY_YEAR REGION PRODUCT REVENUE
------------- ------ -------------------------------- -----------
NULL A Dress 100
NULL A Dress 200
2016 A Pullover 300
2016 B Dress 400
2017 A Pullover 500
2017 B Dress 600
NULL B Pullover 700
NULL B Pullover 800
--- 8 row(s) selected.
```
```
SELECT delivery_year, region, product, SUM (revenue) AS total_revenue
FROM sales2
GROUP BY ROLLUP (delivery_year, region, product);
```
```
DELIVERY_YEAR REGION PRODUCT TOTAL_REVENUE
------------- ------ -------------------------------- --------------------
2016 A Pullover 300
2016 A NULL 300
2016 B Dress 400
2016 B NULL 400
2016 NULL NULL 700
2017 A Pullover 500
2017 A NULL 500
2017 B Dress 600
2017 B NULL 600
2017 NULL NULL 1100
NULL A Dress 300
NULL A NULL 300
NULL B Pullover 1500
NULL B NULL 1500
NULL NULL NULL 1800
NULL NULL NULL 3600
--- 16 row(s) selected.
```
[[examples_of_using_rollup_with_the_column_order_reversed]]
==== Examples of Using ROLLUP with the Column Order Reversed
Suppose that we have the same _sales1_ table as shown in the <<examples_of_grouping_by_one_or_multiple_rollup_columns,Examples of Grouping By One or Multiple Rollup Columns>>.
* The column order of the example below is _delivery_year_, _region_ and _product_.
+
```
SELECT delivery_year, region, product, SUM (revenue) AS total_revenue
FROM sales1
GROUP BY ROLLUP (delivery_year, region, product);
```
+
```
DELIVERY_YEAR REGION PRODUCT TOTAL_REVENUE
------------- ------ -------------------------------- --------------------
2016 A Dress 300
2016 A Pullover 300
2016 A NULL 600
2016 B Dress 400
2016 B NULL 400
2016 NULL NULL 1000
2017 A Pullover 500
2017 A NULL 500
2017 B Dress 600
2017 B Pullover 1500
2017 B NULL 2100
2017 NULL NULL 2600
NULL NULL NULL 3600
--- 13 row(s) selected.
```
* The column order of the example below is _product_, _region_ and _delivery_year_, the output is different than the result sets above.
+
```
SELECT product, region, delivery_year, SUM (revenue) AS total_revenue
FROM sales1
GROUP BY ROLLUP (product, region, delivery_year);
```
+
```
PRODUCT REGION DELIVERY_YEAR TOTAL_REVENUE
-------------------------------- ------ ------------- --------------------
Dress A 2016 300
Dress A NULL 300
Dress B 2016 400
Dress B 2017 600
Dress B NULL 1000
Dress NULL NULL 1300
Pullover A 2016 300
Pullover A 2017 500
Pullover A NULL 800
Pullover B 2017 1500
Pullover B NULL 1500
Pullover NULL NULL 2300
NULL NULL NULL 3600
--- 13 row(s) selected.
```
[[examples_of_using_rollup_with_order_by]]
==== Examples of Using ROLLUP with ORDER BY
Suppose that we have the same _sale1_ table as shown in the <<examples_of_grouping_by_one_or_multiple_rollup_columns,Examples of Grouping By One or Multiple Rollup Columns>>.
This example uses ROLLUP with the ORDER BY clause to sort the results.
```
SELECT delivery_year, product, SUM (revenue) AS total_revenue
FROM sales1
GROUP BY ROLLUP (delivery_year, product)
ORDER BY total_revenue;
```
```
DELIVERY_YEAR PRODUCT TOTAL_REVENUE
------------- -------------------------------- --------------------
2016 Pullover 300
2017 Dress 600
2016 Dress 700
2016 NULL 1000
2017 Pullover 2000
2017 NULL 2600
NULL NULL 3600
--- 7 row(s) selected.
```
<<<
[[round_function]]
== ROUND Function
The ROUND function returns the value of _numeric_expr_ rounded to _num_
places to the right of the decimal point.
ROUND is a {project-name} SQL extension.
```
ROUND(numeric-expr [ , num ] )
```
* `_numeric-expr_`
+
is an SQL numeric value expression.
* `_num_`
+
specifies the number of places to the right of the decimal point for
rounding. If _num_ is a negative number, all places to the right of the
decimal point and _num_ places to the left of the decimal point are
zeroed. If _num_ is not specified or is 0, then all places to the right
of the decimal point are zeroed.
+
For any exact numeric value, the value _numeric_expr_ is rounded away
from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is
negative). For the inexact numeric values (real, float, and double) the
value _numeric_expr_ is rounded toward the nearest even number.
<<<
[[examples_of_round]]
=== Examples of ROUND
* This function returns the value of 123.46.
+
```
ROUND(123.4567,2)
```
* This function returns the value of 123.
+
```
ROUND(123.4567,0)
```
* This function returns the value of 120.
+
```
ROUND(123.4567,-1)
```
* This function returns the value of 0.
+
```
ROUND(999.0,-4)
```
* This function returns the value of 1000.
+
```
ROUND(999.0.-3)
```
* This function returns the value of 2.0E+000.
+
```
ROUND(1.5E+000,0)
```
* This function returns the value of 2.0E+00.
+
```
ROUND(2.5E+000,0)
```
* This function returns the value of 1.0E+00.
+
```
ROUND(1.4E+000,0)
```
<<<
[[rows_since_function]]
== ROWS SINCE Function
The ROWS SINCE function is a sequence function that returns the number
of rows counted since the specified condition was last true in the
intermediate result table ordered by a SEQUENCE BY clause in a SELECT
statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
Rows since is a {project-name} SQL extension.
```
ROWS_SINCE [INCLUSIVE] (condition [, max-rows])
```
* `INCLUSIVE`
+
specifies the current row is to be considered. If you specify INCLUSIVE,
the condition is evaluated in the current row. Otherwise, the condition
is evaluated beginning with the previous row. If you specify INCLUSIVE
and the condition is true in the current row, ROWS SINCE returns 0.
* `_condition_`
+
specifies a condition to be considered for each row in the result table.
Each column in _condition_ must be a column that exists in the result
table. If the condition has never been true for the result table, ROWS
SINCE returns null.
* `_max-rows_`
+
is an SQL numeric value expression of signed data type SMALLINT or
INTEGER that specifies the maximum number of rows from the current row
to consider. If the condition has never been true for _max-rows_ from
the current row, or if _max-rows_ is negative or null, ROWS SINCE
returns null.
[[considerations_for_rows_since]]
=== Considerations for ROWS SINCE
[[counting_the_rows]]
==== Counting the Rows
If you specify INCLUSIVE, the condition in each row of the result table
is evaluated starting with the current row as row 0 (zero) (up to the
maximum number of rows or the size of the result table). Otherwise, the
condition is evaluated starting with the previous row as row 1.
If a row is reached where the condition is true, ROWS SINCE returns the
number of rows counted so far. Otherwise, if the condition is never true
within the result table being considered, ROWS SINCE returns null.
{project-name} SQL then goes to the next row as the new current row.
[[examples_of_rows_since]]
=== Examples of ROWS SINCE
* Return the number of rows since the condition _i1 IS NULL_ became true:
+
```
SELECT ROWS SINCE (i1 IS NULL) AS rows_since_null
FROM mining.seqfcn SEQUENCE BY ts;
rows_since_null
---------------
?
?
1
2
1
--- 5 row(s) selected.
```
* Return the number of rows since the condition _i1 < i2_ became true:
+
```
SELECT ROWS SINCE (i1<i2), ROWS SINCE INCLUSIVE (i1<i2)
FROM mining.seqfcn SEQUENCE BY ts;
(EXPR) (EXPR)
--------------- ---------------
? 0
1 1
2 0
1 1
2 0
--- 5 row(s) selected.
```
<<<
[[rows_since_changed_function]]
== ROWS SINCE CHANGED Function
The ROWS SINCE CHANGED function is a sequence function that returns the
number of rows counted since the specified set of values last changed in
the intermediate result table ordered by a SEQUENCE BY clause in a
SELECT statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
ROWS SINCE CHANGED is a {project-name} SQL extension.
```
ROWS SINCE CHANGED (column-expression-list)
```
* `_column-expression-list_`
+
is a comma-separated list that specifies a derived column list
determined by the evaluation of the column expression list.
ROWS SINCE CHANGED returns the number of rows counted since the
values of _column-expression-list_ changed.
[[considerations_for_rows_since_changed]]
=== Considerations for ROWS SINCE CHANGED
[[counting_the_rows]]
==== Counting the Rows
For the first row in the intermediate result table, the count is 1. For
subsequent rows that have the same value for _column-expression-list_ as
the previous row, the count is 1 plus the count
in the previous row. For subsequent rows that have a different value of
_column-expression-list_
than the previous row, the count is 1.
[[examples_of_rows_since_changed]]
=== Examples of ROWS SINCE CHANGED
* Return the number of rows since the value _i1_ last changed:
+
```
SELECT ROWS SINCE CHANGED (i1)
FROM mining.seqfcn SEQUENCE BY ts;
```
* Return the number of rows since the values _i1_ and _ts_ last changed:
+
```
SELECT ROWS SINCE CHANGED (i1, ts)
FROM mining.seqfcn SEQUENCE BY ts;
```
<<<
[[rpad_function]]
== RPAD Function
The RPAD function pads the right side of a string with the specified
string. Every character in the string, including multi-byte characters,
is treated as one character.
RPAD is a {project-name} SQL extension.
```
RPAD (str, len [, padstr])
```
* `_str_`
+
can be an expression.
See <<character_value_expressions,Character Value Expressions>>.
* `_len_`
+
identifies the desired number of characters to be returned and can be an
expression but must be an integral value. If _len_ is equal to the
length of the string, no change is made. If _len_ is smaller than the
string size, the string is truncated.
* `_pad-character_`
+
can be an expression and may be a string.
<<<
[[examples_of_rpad_function]]
=== Examples of RPAD Function
* This function returns 'kite ':
+
```
RPAD('kite', 7)
```
* This function returns 'ki':
+
```
RPAD('kite', 2)
```
* This function returns 'kite0000':
+
```
RPAD('kite', 8, '0')
```
* This function returns 'go fly a kite':
+
```
RPAD('go fly a kite', 13, 'z')
```
* This function returns 'go fly a kitez'
+
```
RPAD('go fly a kite', 14, 'z')
```
* This function returns 'kitegoflygoflygof':
+
```
RPAD('kite', 17, 'gofly' )
```
<<<
[[rtrim_function]]
== RTRIM Function
The RTRIM function removes trailing spaces from a character string. If
you must remove any leading character other than space, use the TRIM
function and specify the value of the character.
See the <<trim_function,TRIM Function>>.
RTRIM is a {project-name} SQL extension.
```
RTRIM (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression and specifies the string from which
to trim trailing spaces.
+
See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_rtrim]]
=== Considerations for RTRIM
[[result_of_rtrim]]
==== Result of RTRIM
The result is always of type VARCHAR, with maximum length equal to the
fixed length or maximum variable length of _character-expression_.
[[examples_of_rtrim]]
=== Examples of RTRIM
* Return ' Robert':
+
```
RTRIM (' Robert ')
```
+
See <<trim_function,TRIM Function>> and <<ltrim_function,LTRIM Function>>.
<<<
[[runningavg_function]]
== RUNNINGAVG Function
The RUNNINGAVG function is a sequence function that returns the average
of non-null values of a column up to and including the current row of an
intermediate result table ordered by a SEQUENCE BY clause in a SELECT
statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGAVG is a {project-name} SQL extension.
```
RUNNINGAVG (_column-expression_)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
+
RUNNINGAVG returns the average of non-null values of _column-expression_
up to and including the current row.
[[considerations_for_runningavg]]
=== Considerations for RUNNINGAVG
[[equivalent_result]]
==== Equivalent Result
The result of RUNNINGAVG is equivalent to:
```
RUNNINGSUM(column-expr) / RUNNINGCOUNT(*)
```
[[examples_of_runningavg]]
=== Examples of RUNNINGAVG
* Return the average of non-null values of _i1_ up to and including the
current row:
+
```
SELECT RUNNINGAVG(i1) AS avg_i1
FROM mining.seqfcn SEQUENCE BY ts;
avg_i1
--------------------
6215
17194
11463
9746
10190
--- 5 row(s) selected.
```
<<<
[[runningcount_function]]
== RUNNINGCOUNT Function
The RUNNINGCOUNT function is a sequence function that returns the number
of rows up to and including the current row of an intermediate result
table ordered by a SEQUENCE BY clause in a SELECT statement. See
<<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGCOUNT is a {project-name} SQL extension.
```
RUNNINGCOUNT {(*) | (column-expression)}
```
* `*`
+
as an argument causes RUNNINGCOUNT(*) to return the number of rows in
the intermediate result table up to and including the current row.
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression. If _column-expression_ is the argument, RUNNINGCOUNT returns
the number of rows containing non-null values of _column-expression_ in the
intermediate result table up to and including the current row.
[[considerations_for_runningcount]]
=== Considerations for RUNNINGCOUNT
[[no_distinct_clause]]
==== No DISTINCT Clause
The RUNNINGCOUNT sequence function is defined differently from the COUNT
aggregate function. If you specify DISTINCT for the COUNT aggregate
function, duplicate values are eliminated before COUNT is applied. You
cannot specify DISTINCT for the RUNNINGCOUNT sequence function;
duplicate values are counted.
<<<
[[examples_of_runningcount]]
=== Examples of RUNNINGCOUNT
* Return the number of rows that include non-null values of _i1_ up to and
including the current row:
+
```
SELECT RUNNINGCOUNT (i1) AS count_i1
FROM mining.seqfcn SEQUENCE BY ts;
count_i1
------------
1
2
2
3
4
--- 5 row(s) selected.
```
<<<
[[runningmax_function]]
== RUNNINGMAX Function
The RUNNINGMAX function is a sequence function that returns the maximum
of values of a column up to and including the current row of an
intermediate result table ordered by a SEQUENCE BY clause in a SELECT
statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGMAX is a {project-name} SQL extension.
```
RUNNINGMAX (column-expression)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
+
RUNNINGMAX returns the maximum of values of _column-expression_ up to
and including the current row.
[[examples_of_runningmax]]
=== Examples of RUNNINGMAX
* Return the maximum of values of _i1_ up to and including the current row:
+
```
SELECT RUNNINGMAX(i1) AS max_i1
FROM mining.seqfcn SEQUENCE BY ts;
max_i1
------------
6215
28174
28174
28174
28174
--- 5 row(s) selected.
```
<<<
[[runningmin_function]]
== RUNNINGMIN Function
The RUNNINGMIN function is a sequence function that returns the minimum
of values of a column up to and including the current row of an
intermediate result table ordered by a SEQUENCE BY clause in a SELECT
statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGMIN is a {project-name} SQL extension.
```
RUNNINGMIN (column-expression)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
+
RUNNINGMIN returns the minimum of values of _column-expression_ up to
and including the current row.
[[examples_of_runningmin]]
=== Examples of RUNNINGMIN
* Return the minimum of values of _i1_ up to and including the current row:
+
```
SELECT RUNNINGMIN(i1) AS min_i1
FROM mining.seqfcn SEQUENCE BY ts;
min_i1
------------
6215
6215
6215
4597
4597
--- 5 row(s) selected.
```
<<<
[[runningstddev_function]]
=== RUNNINGSTDDEV Function
The RUNNINGSTDDEV function is a sequence function that returns the
standard deviation of non-null values of a column up to and including the
current row of an intermediate result table ordered by a SEQUENCE BY
clause in a SELECT statement.
See <<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGSTDDEV is a {project-name} SQL extension.
```
RUNNINGSTDDEV (_column-expression_)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
+
RUNNINGSTDDEV returns the standard deviation of non-null values of
_column-expression_ up to and including the current row.
[[considerations_for_runningstddev]]
=== Considerations for RUNNINGSTDDEV
[[equivalent_result]]
==== Equivalent Result
The result of RUNNINGSTDDEV is equivalent to:
```
SQRT(RUNNINGVARIANCE(column-expression))
```
<<<
[[examples_of_runningstddev]]
=== Examples of RUNNINGSTDDEV
* Return the standard deviation of non-null values of _i1_ up to and
including the current row:
+
```
SELECT RUNNINGSTDDEV (i1) AS stddev_i1
FROM mining.seqfcn SEQUENCE BY ts;
STDDEV_I1
-------------------------
0.00000000000000000E+000
1.55273578080753976E+004
1.48020166531456112E+004
1.25639147428923072E+004
1.09258501408357232E+004
--- 5 row(s) selected.
```
* You can use the CAST function for display purposes. For example:
+
```
SELECT CAST(RUNNINGSTDDEV(i1) AS DEC(18,3))
FROM mining.seqfcn SEQUENCE BY ts;
(EXPR)
--------------------
.000
5527.357
14802.016
12563.914
10925.850
--- 5 row(s) selected.
```
<<<
[[runningsum_function]]
== RUNNINGSUM Function
The RUNNINGSUM function is a sequence function that returns the sum of
non-null values of a column up to and including the current row of an
intermediate result table ordered by a SEQUENCE BY clause in a SELECT
statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGSUM is a {project-name} SQL extension.
```
RUNNINGSUM (column-expression)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
+
RUNNINGSUM returns the sum of non-null values of _column-expression_ up
to and including the current row.
[[examples_of_runningsum]]
=== Examples of RUNNINGSUM
* Return the sum of non-null values of _i1_ up to and including the current
row:
+
```
SELECT RUNNINGSUM(i1) AS sum_i1
FROM mining.seqfcn SEQUENCE BY ts;
sum_i1
--------------------
6215
34389
34389
38986
50952
--- 5 row(s) selected.
```
<<<
[[runningvariance_function]]
== RUNNINGVARIANCE Function
The RUNNINGVARIANCE function is a sequence function that returns the
variance of non-null values of a column up to and including the current
row of an intermediate result table ordered by a SEQUENCE BY clause in a
SELECT statement. See <<sequence_by_clause,SEQUENCE BY Clause>>.
RUNNINGVARIANCE is a {project-name} SQL extension.
```
RUNNINGVARIANCE (column-expression)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression.
+
RUNNINGVARIANCE returns the variance of non-null values of
_column-expression_ up to and including the current row.
[[examples_of_runningvariance]]
=== Examples of RUNNINGVARIANCE
* Return the variance of non-null values of _i1_ up to and including the
current row:
+
```
SELECT RUNNINGVARIANCE(i1) AS variance_i1
FROM mining.seqfcn SEQUENCE BY TS;
variance_i1
-------------------------
0.00000000000000000E+000
2.41098840499999960E+008
2.19099696999999968E+008
1.57851953666666640E+008
1.19374201299999980E+008
--- 5 row(s) selected.
```
<<<
* You can use the CAST function for display purposes. For example:
+
```
SELECT CAST(RUNNINGVARIANCE (i1) AS DEC (18,3))
FROM mining.seqfcn SEQUENCE BY ts;
(EXPR)
--------------------
.000
241098840.500
219099697.000
157851953.666
119374201.299
--- 5 row(s) selected.
```
<<<
[[second_function]]
== SECOND Function
The SECOND function converts a TIME or TIMESTAMP expression into an
INTEGER value in the range 0 through 59 that represents the
corresponding second of the hour.
SECOND is a {project-name} SQL extension.
```
SECOND (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type TIME or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_second]]
=== Examples of SECOND
* Return a numeric value that represents the second of the hour from the
_ship_timestamp_ column:
```
SELECT start_date, ship_timestamp, SECOND(ship_timestamp)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- -----------
2008-04-10 2008-04-21 08:15:00.000000 .000000
```
<<<
[[sha_function]]
== SHA Function
Calculates an SHA-1 160-bit checksum for the string, as described in
RFC 3174 (Secure Hash Algorithm). The value is returned as a string of
40 hexadecimal digits, or NULL if the argument was NULL.
[[examples_of_sha]]
=== examples of SHA
```
>>SELECT SHA1('abc') from dual;
(EXPR)
-----------------------------------------
'a9993e364706816aba3e25717850c26c9cd0d89d'
```
<<<
[[sha2_function]]
== SHA2 Function
Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384,
and SHA-512). The first argument is the cleartext string to be hashed.
The second argument indicates the desired bit length of the result, which
must have a value of 224, 256, 384, 512.
If either argument is NULL or the hash length is not one of the permitted values,
the return value is NULL. Otherwise, the function result is a hash value containing
the desired number of bits. See the notes at the beginning of this section
about storing hash values efficiently.
[[examples_of_sha2]]
=== examples of SHA2
```
>>SELECT SHA2('abc', 224) from dual;
(EXPR)
--------------------------------------------------------
'23097d223405d8228642a477bda255b32aadbce4bda0b3f7e36c9da7'
```
<<<
[[sign_function]]
== SIGN Function
The SIGN function returns an indicator of the sign of a numeric value
expression. If the value is less than zero, the function returns -1 as
the indicator. If the value is zero, the function returns 0. If the
value is greater than zero, the function returns 1.
SIGN is a {project-name} SQL extension.
```
SIGN (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the SIGN function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_sign]]
=== Examples of SIGN
* Return the value -1:
+
```
SIGN(-20 + 12)
```
* Return the value 0:
+
```
SIGN(-20 + 20)
```
* Return the value 1:
+
```
SIGN(-20 + 22)
```
<<<
[[sin_function]]
== SIN Function
The SIN function returns the SINE of a numeric value expression, where
the expression is an angle expressed in radians.
SIN is a {project-name} SQL extension.
```
SIN (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the SIN function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_sin]]
=== Examples of SIN
* This function returns the value 3.42052233254419840E-001, or
approximately 0.3420, the sine of 0.3491 (which is 20 degrees):
+
```
SIN (0.3491)
```
<<<
[[sinh_function]]
== SINH Function
The SINH function returns the hyperbolic sine of a numeric value
expression, where the expression is an angle expressed in radians.
SINH is a {project-name} SQL extension.
```
SINH (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the SINH function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_sinh]]
=== Examples of SINH
* This function returns the value 1.60191908030082560E+000, or
approximately 1.6019, the hyperbolic sine of 1.25:
+
```
SINH (1.25)
```
<<<
[[space_function]]
=== SPACE Function
The SPACE function returns a character string consisting of a specified
number of spaces, each of which is 0x20 or 0x0020, depending on the
chosen character set.
SPACE is a {project-name} SQL extension.
```
SPACE (length [, char-set-name])
```
* `_length_`
+
specifies the number of characters to be returned. The number _count_
must be a value greater than or equal to zero of exact numeric data type
and with a scale of zero. _length_ cannot exceed 32768 for the ISO88591
or UTF8 character sets.
* `_char-set-name_`
+
can be ISO88591 or UTF8. If you do not specify this second argument, the
default is the default character set.
+
The returned character string will be of data type VARCHAR associated
with the character set specified by _char-set-name_.
[[examples_of_space]]
=== Examples of SPACE
* Return three spaces:
+
```
SPACE(3)
```
<<<
[[sqrt_function]]
== SQRT Function
The SQRT function returns the square root of a numeric value expression.
SQRT is a {project-name} SQL extension.
```
SQRT (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the SQRT function. The value of the argument must not be a
negative number. See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_sqrt]]
=== Examples of SQRT
* This function returns the value 5.19615242270663232e+000, or
approximately 5.196:
+
```
SQRT(27)
```
<<<
[[stddev_function]]
== STDDEV Function
STDDEV is an aggregate function that returns the standard deviation of a
set of numbers. STDDEV is a {project-name} SQL extension.
```
STDDEV ([ALL | DISTINCT] expression [, weight])
```
* `ALL | DISTINCT`
+
specifies whether duplicate values are included in the computation of
the STDDEV of the _expression_. The default option is ALL, which
causes duplicate values to be included. If you specify DISTINCT,
duplicate values are eliminated before the STDDEV function is applied.
If DISTINCT is specified, you cannot specify _weight_.
* `_expression_`
+
specifies a numeric value expression that determines the values for
which to compute the standard deviation. The _expression_ cannot contain
an aggregate function or a subquery. The DISTINCT clause specifies that
the STDDEV function operates on distinct values from the one-column
table derived from the evaluation of _expression_.
* `_weight_`
+
specifies a numeric value expression that determines the weights of the
values for which to compute the standard deviation. _weight_ cannot
contain an aggregate function or a subquery. _weight_ is defined on
the same table as _expression_. The one-column table derived from the
evaluation of _expression_ and the one-column table derived from the
evaluation of _weight_ must have the same cardinality.
[[considerations_for_stddev]]
=== Considerations for STDDEV
[[definition_of_stddev]]
==== Definition of STDDEV
The standard deviation of a value expression is defined to be the square
root of the variance of the expression.
See <<variance_function,VARIANCE Function>>.
Because the definition of variance has _N-1_ in the denominator of the
expression (if weight is not specified), {project-name} SQL returns a
system-defined default setting of zero (and no error) if the number of
rows in the table, or a group of the table, is equal to 1.
[[data_type_of_the_result]]
==== Data Type of the Result
The data type of the result is always DOUBLE PRECISION.
[[operands_of_the_expression]]
==== Operands of the Expression
The expression includes columns from the rows of the SELECT result table
but cannot include an aggregate function. These are valid:
```
STDDEV (SALARY) STDDEV (SALARY * 1.1)
STDDEV (PARTCOST * QTY_ORDERED)
```
[[stddev_nulls]]
==== Nulls
STDDEV is evaluated after eliminating all nulls from the set. If the
result table is empty, STDDEV returns NULL.
[[float54_and_double_precision_data]]
==== FLOAT(54) and DOUBLE PRECISION Data
Avoid using large FLOAT(54) or DOUBLE PRECISION values as arguments to
STDDEV. If SUM(x * x) exceeds the value of 1.15792089237316192e77 during the computation
of STDDEV(x), a numeric overflow occurs.
<<<
[[examples_of_stddev]]
=== Examples of STDDEV
* Compute the standard deviation of the salary of the current employees:
+
```
SELECT STDDEV(salary) AS StdDev_Salary FROM persnl.employee;
STDDEV_SALARY
-------------------------
3.57174062500000000E+004
--- 1 row(s) selected.
```
* Compute the standard deviation of the cost of parts in the current
inventory:
+
```
SELECT STDDEV (price * qty_available) FROM sales.parts;
(EXPR)
-------------------------
7.13899499999999808E+006
--- 1 row(s) selected.
```
<<<
[[stringtolob_function]]
== STRINGTOLOB Function
[[stringtolob_function_syntax]]
=== Syntax Descriptions of STRINGTOLOB Function
The STRINGTOLOB function converts a simple string literal into LOB format. This function can be used in an INSERT or UPDATE statement.
STRINGTOLOB function is a Trafodion SQL extension.
For more information, see http://trafodion.apache.org/docs/lob_guide/index.html[Trafodion SQL Large Objects Guide].
```
STRINGTOLOB('string literal expression')
```
* STRINGTOLOB
+
Converts a simple string literal into LOB format.
** string literal expression
+
is a series of characters enclosed in single quotes.
[[stringtolob_function_examples]]
=== Examples of STRINGTOLOB Function
* This example converts a simple string literal into LOB format before inserting.
+
```
insert into tlob1 values(1,stringtolob('inserted row'));
```
<<<
[[substring_function]]
== SUBSTRING/SUBSTR Function
The SUBSTRING function extracts a substring out of a given character
expression. It returns a character string of data type VARCHAR, with a
maximum length equal to the smaller of these two:
* The fixed length of the input string (for CHAR-type strings) or the
maximum variable length (for VARCHAR-type strings)
* The value of the length argument (when a constant is specified) or
32708 (when a non-constant is specified)
SUBSTR is equivalent to SUBSTRING.
```
SUBSTRING (character-expr FROM start-position [FOR length])
```
or:
```
SUBSTRING (character-expr, start-position [, length])
```
* `_character-expr_`
+
specifies the source string from which to extract the substring. The
source string is an SQL character value expression. The operand is the
result of evaluating _character-expr_. See
<<character_value_expressions,Character Value Expressions>>.
* `_start-position_`
+
specifies the starting position _start-position_ within _character-expr_
at which to start extracting the substring. _start-position_ must be a
value with an exact numeric data type and a scale of zero.
* `_length_`
+
specifies the number of characters to extract from _character-expr_.
Keep in mind that every character, including multi-byte characters,
counts as one character. _length_ is the length of the extracted
substring and must be a value greater than or equal to zero of exact
numeric data type and with a scale of zero. The _length_ field is
optional, so if you do not specify the substring _length_, all
characters starting at _start-position_ and continuing until the end of
the character expression are returned.
+
The length field is optional. If you do not specify it, all characters
starting at _start-position_
and continuing until the end of the _character-expr_ are returned.
[[alternative_forms]]
=== Alternative Forms
* The SUBSTRING function treats SUBSTRING( _string_ FOR _int_ )
equivalent to SUBSTRING( _string_ FROM 1 FOR _int_ ). The {project-name}
database software already supports the ANSI standard form as:
+
```
SUBSTRING(string FROM int [ FOR int ])
```
* The SUBSTRING function treats SUBSTRING (_string_, Fromint)
equivalent to SUBSTRING(_string_ FROM _Fromint_). The {project-name}
database software already supports SUBSTRING (_string_, _Fromint_,
_Forint_) as equivalent to the ANSI standard form:
+
```
SUBSTRING(string FROM Fromint FOR Forint)
```
[[considerations_for_substring]]
=== Considerations for SUBSTRING/SUBSTR
[[requirements_for_the_expression_length_and_start_position]]
==== Requirements for the Expression, Length, and Start Position
* The data types of the substring length and the start position must be
numeric with a scale of zero. Otherwise, an error is returned.
* If the sum of the start position and the substring length is greater
than the length of the character expression, the substring from the
start position to the end of the string is returned.
* If the start position is greater than the length of the character
expression, an empty string ('') is returned.
* The resulting substring is always of type VARCHAR. If the source
character string is an up-shifted CHAR or VARCHAR string, the result is
an up-shifted VARCHAR type.
<<<
[[examples_of_substring]]
=== Examples of SUBSTRING/SUBSTR
* Extract 'Ro':
+
```
SUBSTRING('Robert John Smith' FROM 0 FOR 3)
SUBSTR('Robert John Smith' FROM 0 FOR 3)
```
* Extract 'John':
+
```
SUBSTRING ('Robert John Smith' FROM 8 FOR 4)
SUBSTR ('Robert John Smith' FROM 8 FOR 4)
```
* Extract 'John Smith':
+
```
SUBSTRING ('Robert John Smith' FROM 8)
SUBSTR ('Robert John Smith' FROM 8)
```
* Extract 'Robert John Smith':
+
```
SUBSTRING ('Robert John Smith' FROM 1 FOR 17)
SUBSTR ('Robert John Smith' FROM 1 FOR 17)
```
* Extract 'John Smith':
+
```
SUBSTRING ('Robert John Smith' FROM 8 FOR 15)
SUBSTR ('Robert John Smith' FROM 8 FOR 15)
```
* Extract 'Ro':
+
```
SUBSTRING ('Robert John Smith' FROM -2 FOR 5)
SUBSTR ('Robert John Smith' FROM -2 FOR 5)
```
* Extract an empty string '':
+
```
SUBSTRING ('Robert John Smith' FROM 8 FOR 0)
SUBSTR ('Robert John Smith' FROM 8 FOR 0)
```
<<<
[[sum_function]]
== SUM Function
SUM is an aggregate function that returns the sum of a set of numbers.
```
SUM ([ALL | DISTINCT] expression)
```
* `ALL | DISTINCT`
+
specifies whether duplicate values are included in the computation of
the SUM of the _expression_. The default option is ALL, which causes
duplicate values to be included. If you specify DISTINCT, duplicate
values are eliminated before the SUM function is applied.
* `_expression_`
+
specifies a numeric or interval value expression that determines the
values to sum. The _expression_ cannot contain an aggregate function or
a subquery. The DISTINCT clause specifies that the SUM function operates
on distinct values from the one-column table derived from the evaluation
of _expression_. All nulls are eliminated before the function is
applied to the set of values. If the result table is empty, SUM returns
NULL. See <<expressions,Expressions>>.
[[considerations_for_sum]]
=== Considerations for SUM
[[data_type_and_scale_of_the_result]]
==== Data Type and Scale of the Result
The data type of the result depends on the data type of the argument. If
the argument is an exact numeric type, the result is LARGEINT. If the
argument is an approximate numeric type, the result
is DOUBLE PRECISION. If the argument is INTERVAL data type, the result
is INTERVAL with the same precision as the argument. The scale of the
result is the same as the scale of the argument. If the argument has no
scale, the result is truncated.
[[operands_of_the_expression]]
==== Operands of the Expression
The expression includes columns from the rows of the SELECT result
table &#8212; but cannot include an aggregate function. The valid expressions
are:
```
SUM (SALARY)
SUM (SALARY * 1.1)
SUM (PARTCOST * QTY_ORDERED)
```
[[examples_of_sum]]
=== Examples of SUM
* Compute the total value of parts in the current inventory:
+
```
SELECT SUM (price * qty_available) FROM sales.parts;
(EXPR)
---------------------
117683505.96
--- 1 row(s) selected.
```
<<<
[[sysdate_function]]
== SYSDATE Function
The SYSDATE function, which is equivalent to the `CURRENT_DATE` function, retrieves the current date of the server rather than the session.
The returned value is `DATE` and the default format is `YYYY-MM-DD`.
For example, if you execute a query on your local machine located in Shanghai on 2018-03-14 06:00:00 (UTC+8) against a database server located in Berlin on 2018-03-13 23:00:00 (UTC+1), the result of `SELECT SYSDATE FROM DUAL;` is 2018-03-13 rather than 2018-03-14.
```
SYSDATE
```
[[examples_of_sysdate]]
=== Examples of SYSDATE
* This example returns the current date.
+
```
SQL>SELECT SYSDATE FROM DUAL;
(EXPR)
----------
2018-03-15
--- 1 row(s) selected.
```
* This example returns the date of yesterday, today and tomorrow.
+
```
SQL>SELECT SYSDATE -1 AS yesterday,
SYSDATE AS today,
SYSDATE +1 AS tomorrow FROM DUAL;
YESTERDAY TODAY TOMORROW
---------- ---------- ----------
2018-03-14 2018-03-15 2018-03-16
--- 1 row(s) selected.
```
* The following examples show that the values of `SYSDATE` can be converted to character values.
+
```
SQL>SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY') FROM DUAL;
(EXPR)
-----------
15-MAR-2018
--- 1 row(s) selected.
```
+
```
SQL>SELECT TO_CHAR (SYSDATE,'HH:MI:SS') FROM DUAL;
*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a time. [2018-03-15 11:49:22]
```
+
```
SQL>SELECT TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') FROM DUAL;
(EXPR)
-------------------
03/15/2018 00:00:00
--- 1 row(s) selected.
```
* This example converts days to minutes using `SYSDATE`.
+
```
SQL>SELECT (SYSDATE-(SYSDATE-7))*1440 FROM DUAL;
(EXPR)
-----------------
10080
--- 1 row(s) selected.
```
* This example demonstrates how the SYSDATE function works in SQL statement.
+
Suppose that we have the following table:
+
```
SQL>SELECT * FROM orders;
ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM
-------- ---------- ---------- -------- -------
100210 2018-03-02 2018-04-10 220 127
100250 2018-01-23 2018-06-16 220 123
101220 2018-02-21 2018-12-15 221 156
200300 2018-02-06 2018-07-15 222 126
200320 2018-03-08 2018-07-20 223 121
200490 2018-02-19 2018-11-01 226 123
300350 2018-03-03 2018-08-10 231 123
300380 2018-01-19 2018-08-15 226 156
400410 2018-01-27 2018-09-14 227 154
500450 2018-03-12 2018-09-16 220 124
600480 2018-02-12 2018-10-14 226 123
700510 2018-02-01 2018-10-16 220 143
800660 2018-01-09 2018-11-01 229 100
--- 13 row(s) selected.
```
+
The SYSDATE is 2018-03-15.
+
```
SQL>SELECT SYSDATE FROM DUAL;
(EXPR)
----------
2018-03-15
--- 1 row(s) selected.
```
+
This statement returns qualified rows using `SYSDATE` function.
+
```
SQL>SELECT * FROM orders
WHERE DAY(deliv_date) = DAY(sysdate+1)
AND ordernum <>100210
AND salesrep=220
ORDER BY order_date DESC;
ORDERNUM ORDER_DATE DELIV_DATE SALESREP CUSTNUM
-------- ---------- ---------- -------- -------
500450 2018-03-12 2018-09-16 220 124
700510 2018-02-01 2018-10-16 220 143
100250 2018-01-23 2018-06-16 220 123
--- 3 row(s) selected.
```
<<<
[[systimestamp_function]]
== SYSTIMESTAMP Function
The SYSTIMESTAMP function, which is equivalent to the `CURRENT_TIMESTAMP` function, provides much high granularity than the `SYSDATE` function and retrieves the current date and time (including fractional seconds with six-digit precision) of the server rather than the session.
The returned value is `TIMESTAMP` and the default format is `YYYY-MM-DD HH:MM:SS.FFFFFF`.
For example, if you execute a query on your local machine located in Shanghai on 2018-03-14 06:00:00 (UTC+8) against a database server located in Berlin on 2018-03-13 23:00:00 (UTC+1), the result of `SELECT SYSTIMESTAMP FROM DUAL;` is 2018-03-13 23:00:00 rather than 2018-03-14 06:00:00.
```
SYSTIMESTAMP
```
[[examples_of_systimestamp]]
=== Examples of SYSTIMESTAMP
* This example calculates the date and time of anniversary using SYSTIMESTAMP function.
+
```
SELECT SYSTIMESTAMP AS today, SYSTIMESTAMP + INTERVAL '12' MONTH AS Annisversary FROM DUAL;
TODAY ANNISVERSARY
-------------------------- --------------------------
2018-03-15 11:19:42.400382 2019-03-15 11:19:42.400382
--- 1 row(s) selected.
```
* This example demonstrates how to insert the value of SYSTIMESTAMP into a column.
+
```
SQL>CREATE TABLE test1 (C1 TIMESTAMP, C2 VARCHAR(40));
--- SQL operation complete.
```
+
```
SQL>INSERT INTO test1 VALUES (SYSTIMESTAMP, 'This is the time that I insert values');
--- 1 row(s) inserted.
```
+
```
SQL>SELECT * FROM test1;
C1 C2
-------------------------- ----------------------------------------
2018-03-15 11:33:32.091057 This is the time that I insert values
--- 1 row(s) selected.
```
<<<
[[tan_function]]
== TAN Function
The TAN function returns the tangent of a numeric value expression,
where the expression is an angle expressed in radians.
TAN is a {project-name} SQL extension.
```
TAN (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the TAN function.
See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_tan]]
=== Examples of TAN
* This function returns the value 3.64008908293626880E-001, or
approximately 0.3640, the tangent of 0.3491 (which is 20 degrees):
+
```
TAN (0.3491)
```
<<<
[[tanh_function]]
=== TANH Function
The TANH function returns the hyperbolic tangent of a numeric value
expression, where the expression is an angle expressed in radians.
TANH is a {project-name} SQL extension.
```
TANH (numeric-expression)
```
* `_numeric-expression_`
+
is an SQL numeric value expression that specifies the value for the
argument of the TANH
function. See <<numeric_value_expressions,Numeric Value Expressions>>.
[[examples_of_tanh]]
=== Examples of TANH
* This function returns the value 8.48283639957512960E-001 or
approximately 0.8483, the hyperbolic tangent of 1.25:
+
```
TANH (1.25)
```
<<<
[[this_function]]
== THIS Function
The THIS function is a sequence function that is used in the ROWS SINCE
function to distinguish between the value of the column in the current
row and the value of the column in previous rows (in an intermediate
result table ordered by a SEQUENCE BY clause in a SELECT statement).
See <<rows_since_function,ROWS SINCE Function>>.
THIS is a {project-name} SQL extension.
```
THIS (column-expression)
```
* `_column-expression_`
+
specifies a derived column determined by the evaluation of the column
expression. If the value of the expression is null, THIS returns null.
[[considerations_for_this]]
=== Considerations for THIS
[[counting_the_rows]]
==== Counting the Rows
You can use the THIS function only within the ROWS SINCE function. For
each row, the ROWS SINCE condition is evaluated in two steps:
1. The expression for THIS is evaluated for the current row. This value
becomes a constant.
2. The condition is evaluated for the result table, using a combination
of the THIS constant and the data for each row in the result table,
starting with the previous row as row 1 (up to the maximum number of
rows or the size of the result table).
If a row is reached where the condition is true, ROWS SINCE returns the
number of rows counted so far. Otherwise, if the condition is never true
within the result table being considered, ROWS SINCE returns null.
{project-name} SQL then goes to the next row as the new current row and the
THIS constant is reevaluated.
<<<
[[examples_of_this]]
=== Examples of THIS
* Return the number of rows since the condition _i1_ less than a previous
row became true:
+
```
SELECT ROWS SINCE (THIS(i1) < i1) AS rows_since_this
FROM mining.seqfcn SEQUENCE BY ts;
rows_since_this
---------------
?
?
1
1
?
--- 5 row(s) selected.
```
<<<
[[timestampadd_function]]
== TIMESTAMPADD Function
The TIMESTAMPADD function adds the interval of time specified by
_interval-ind_ and _num_expr_ to _datetime_expr_. If the specified
interval is in years, months, or quarters and the resulting date is not
a valid date, the day will be rounded down to the last day of the result
month. The type of the _datetime_expr_ is returned except when the
_interval-ind_ contains any time component, in which case a TIMESTAMP is
returned.
TIMESTAMPADD is a {project-name} SQL extension.
```
TIMESTAMPADD (interval-ind, num-expr, datetime-expr)
```
* `_interval-ind_`
+
is SQL_TSI_YEAR, SQL_TSI_MONTH, SQL_TSI_DAY, SQL_TSI_HOUR,
SQL_TSI_MINUTE, SQL_TSI_SECOND, SQL_TSI_QUARTER, or SQL_TSI_WEEK
* `_num_expr_`
+
is an SQL exact numeric value expression that specifies how many
_interval-ind_ units of time are to be added to _datetime_expr_. If
_num_expr_ has a fractional portion, it is ignored. If _num_expr_ is
negative, the return value precedes _datetime_expr_ by the specified
amount of time.
* `_datetime_expr_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. The type of the _datetime_expr_ is returned except when the
_interval-ind_ contains any time component, in which case a TIMESTAMP is
returned.
<<<
[[examples_of_timestampadd]]
=== Examples of TIMESTAMPADD
* This function adds seven days to the date specified in _start-date_:
+
```
TIMESTAMPADD (SQL_TSI_DAY, 7, start-date)
```
* This function returns the value DATE '2008-03-06':
+
```
TIMESTAMPADD (SQL_TSI_WEEK, 1, DATE '2008-02-28')
```
* This function returns the value DATE '1999-02-28':
+
```
TIMESTAMPADD (SQL_TSI_YEAR, -1, DATE '2000-02-29')
```
* This function returns the value TIMESTAMP '2003-02-28 13:27:35':
+
```
TIMESTAMPADD (SQL_TSI_MONTH, -12, TIMESTAMP '2004-02-29 13:27:35')
```
* This function returns the value TIMESTAMP '2004-02-28 13:27:35':
+
```
TIMESTAMPADD (SQL_TSI_MONTH, 12, TIMESTAMP '2003-02-28 13:27:35')
```
* This function returns the value DATE '2008-06-30':
+
```
TIMESTAMPADD (SQL_TSI_QUARTER, -2, DATE '2008-12-31')
```
* This function returns the value TIMESTAMP '2008-06-30 23:59:55':
+
```
TIMESTAMPADD (SQL_TSI_SECOND, -5, DATE '2008-07-01')
```
<<<
[[timestampdiff_function]]
== TIMESTAMPDIFF Function
The TIMESTAMPDIFF function returns the integer for the number of
_interval-ind_ units of time between _startdate_ and _enddate_. If
_enddate_ precedes _startdate_, the return value is negative or zero.
```
TIMESTAMPDIFF (interval-ind, startdate, enddate)
```
* `_interval-ind_`
+
is SQL_TSI_YEAR, SQL_TSI_MONTH, SQL_TSI_DAY, SQL_TSI_HOUR,
SQL_TSI_MINUTE, SQL_TSI_SECOND, SQL_TSI_QUARTER, or SQL_TSI_WEEK
* `_startdate_` and `_enddate_`
+
are each of type DATE or TIMESTAMP
The method of counting crossed boundaries such as days, minutes, and
seconds makes the result given by TIMESTAMPDIFF consistent across all
data types. The TIMESTAMPDIFF function makes these boundary assumptions:
* A year begins at the start of January 1.
* A new quarter begins on January 1, April 1, July 1, and October 1.
* A week begins at the start of Sunday.
* A day begins at midnight.
The result is a signed integer value equal to the number of
_interval-ind_ boundaries crossed between the first and second date. For
example, the number of weeks between Sunday, January 4 and Sunday,
January 1 is 1. The number of months between March 31 and April 1 would
be 1 because the month boundary is crossed from March to April.
The TIMESTAMPDIFF function generates an error if the result is out of
range for integer values. For seconds, the maximum number is equivalent
to approximately 68 years. The TIMESTAMPDIFF function generates an error
if a difference in weeks is requested and one of the two dates precedes
January 7 of the year 0001.
<<<
[[examples_of_timestampdiff]]
=== Examples of TIMESTAMPDIFF
* This function returns the value 1 because a 1-second boundary is
crossed even though the two timestamps differ by only one microsecond:
+
```
TIMESTAMPDIFF
(
SQL_TSI_SECOND
, TIMESTAMP '2006-09-12 11:59:58.999999'
, TIMESTAMP '2006-09-12 11:59:59.000000'
)
```
* This function returns the value 0 because no 1-second boundaries are
crossed:
+
```
TIMESTAMPDIFF
( SQL_TSI_YEAR
, TIMESTAMP '2006-12-31 23:59:59.00000
, TIMESTAMP '2006-12-31 23:59:59.999999'
)
```
* This function returns the value 1 because a year boundary is crossed:
+
```
TIMESTAMPDIFF
( SQL_TSI_YEAR
, TIMESTAMP '2006-12-31 23:59:59.999999'
, TIMESTAMP '2007-01-01 00:00:00.000000;
)
```
* This function returns the value 1 because a WEEK boundary is crossed:
+
```
TIMESTAMPDIFF (SQL_TSI_WEEK, DATE '2006-01-01', DATE '2006-01-09')
```
* This function returns the value of -29:
+
```
TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2004-03-01', DATE '2004-02-01')
```
<<<
[[to_char_function]]
== TO_CHAR Function
The TO_CHAR function converts a datetime value to a character value. The optional
second argument describes the format of the character value.
```
TO_CHAR(character-expression [,format-string])
```
* `_character-expression_`
+
is an expression that gives a datetime value.
* `_format-string_`
+
is one of the following character string literals:
** 'YYYY-MM-DD'
** 'MM/DD/YYYY'
** 'DD.MM.YYYY'
** 'YYYY-MM'
** 'MM/DD/YYYY'
** 'YYYY/MM/DD'
** 'YYYYMMDD'
** 'YY/MM/DD'
** 'MM/DD/YY'
** 'MM-DD-YYYY'
** 'YYYYMM'
** 'DD-MM-YYYY'
** 'DD-MON-YYYY'
** 'DDMONYYYY'
** 'HH:MI:SS'
** 'HH24:MI:SS'
** 'YYYYMMDDHH24MISS'
** 'DD.MM.YYYY:HH24.MI.SS'
** 'YYYY-MM-DD HH24:MI:SS'
** 'YYYYMMDD:HH24:MI:SS'
** 'MMDDYYYY HH24:MI:SS'
** 'MM/DD/YYYY HH24:MI:SS'
** 'DD-MON-YYYY HH:MI:SS'
** 'MONTH DD, YYYY, HH:MI'
** 'DD.MM.YYYY HH24.MI.SS'
Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM refers to a two-digit month. MON refers to
a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
'OCT', 'NOV' or 'DEC'). MONTH refers to the month spelled out. In the output value, the month abbreviation or month
will appear in upper case.
HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
If the _format-string_ argument is omitted, 'YYYY-MM-DD' is used as the
default for date and timestamp values, and 'HH:MI:SS' is used for time values.
[[considerations_for_to_char]]
=== Considerations for TO_CHAR
If the _format-string_ contains only hour, minute or seconds fields, the input data type must be time or timestamp.
If the _format-string_ contains only year, month or day fields, the input data type must be date or timestamp.
If the _format-string_ contains all fields, and the input data type is date, the hour, minute and second fields in the result will be filled with zeroes.
[[examples_of_to_char]]
=== Examples of TO_CHAR
* This function returns the character value '01MAR2016':
+
```
TO_CHAR (DATE '2016-03-01', 'DDMONYYYY')
```
* This function returns the character value '01.03.2016 00:00:00':
+
```
TO_CHAR (DATE '2016-03-01', 'DD.MM.YYYY HH24.MI.SS')
```
* This function returns the character value '12:05:10':
+
```
TO_CHAR (TIME '12:05:10')
```
* This function returns the character value '20160301120510':
+
```
TO_CHAR (TIMESTAMP '2016-03-01 12:05:10','YYYYMMDDHH24MISS')
```
<<<
[[to_date_function]]
== TO_DATE Function
The TO_DATE function converts a character value to a date. The optional
second argument describes the format of the character value.
```
TO_DATE(character-expression [,format-string])
```
* `_character-expression_`
+
is an expression that gives a character value.
* `_format-string_`
+
is one of the following character string literals:
** 'YYYY-MM-DD'
** 'MM/DD/YYYY'
** 'DD.MM.YYYY'
** 'YYYY-MM'
** 'MM/DD/YYYY'
** 'YYYY/MM/DD'
** 'YYYYMMDD'
** 'YY/MM/DD'
** 'MM/DD/YY'
** 'MM-DD-YYYY'
** 'YYYYMM'
** 'DD-MM-YYYY'
** 'DD-MON-YYYY'
** 'DDMONYYYY'
** 'YYYYMMDDHH24MISS'
** 'DD.MM.YYYY:HH24.MI.SS'
** 'YYYY-MM-DD HH24:MI:SS'
** 'YYYYMMDD:HH24:MI:SS'
** 'MMDDYYYY HH24:MI:SS'
** 'MM/DD/YYYY HH24:MI:SS'
** 'DD-MON-YYYY HH:MI:SS'
** 'MONTH DD, YYYY, HH:MI'
** 'DD.MM.YYYY HH24.MI.SS'
Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM refers to a two-digit month. MON refers to
a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
'OCT', 'NOV' or 'DEC'). MONTH refers to the month spelled out. The month in either MON or MONTH form may be given in lower case, upper case or any mixture.
HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
If the _format-string_ argument is omitted, 'YYYY-MM-DD' is used as the
default.
[[considerations_for_to_date]]
=== Considerations for TO_DATE
If the _format-string_ contains hour, minute or seconds fields, the output data type is timestamp. Otherwise,
the output data type is date.
[[examples_of_to_date]]
=== Examples of TO_DATE
* This function returns the date value '2016-12-07':
+
```
TO_DATE ('2016-12-07')
```
* This function returns the date value '2016-07-12':
+
```
TO_DATE ('12-07-2016', 'DD-MM-YYYY')
```
* This function returns the date value '2016-12-07':
+
```
TO_DATE ('07DEC2016', 'DDMONYYYY')
```
* This function returns the timestamp value '2016-12-07 17:05:01.000000':
+
```
TO_DATE ('07.12.2016:17.05.01', 'DD.MM.YYYY:HH24.MI.SS')
```
<<<
[[to_time_function]]
== TO_TIME Function
The TO_TIME function converts a character value to a time. The
second argument describes the format of the character value.
```
TO_TIME(character-expression ,format-string)
```
* `_character-expression_`
+
is an expression that gives a character value.
* `_format-string_`
+
is one of the following character string literals:
** 'HH:MI:SS'
** 'HH24:MI:SS'
Here, HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
[[examples_of_to_time]]
=== Examples of TO_TIME
* This function returns the time value '17:05:01':
+
```
TO_TIME ('17:05:01', 'HH24:MI:SS')
```
<<<
[[to_timestamp_function]]
== TO_TIMESTAMP Function
The TO_TIMESTAMP function converts a character value to a timestamp.
```
TO_TIMESTAMP(character-expression)
```
* `_character-expression_`
+
is an expression that gives a character value. The expression ia assumed to have the format YYYY-MM-DD HH:MI:SS[.FFFFFF],
where YYYY is a 4-digit year, MM is a 2-digit month, DD is a 2-digit day, HH is a 2-digit hours field, MI is a 2-digit
minutes field, SS is a two-digit seconds field and FFFFFF is an optional microseconds field.
[[examples_of_to_timestamp]]
=== Example of TO_TIMESTAMP
* This function returns the timestamp value '2016-12-07 10:01:00':
+
```
TO_TIMESTAMP ('2016-12-07 10:01:00')
```
<<<
[[translate_function]]
== TRANSLATE Function
The TRANSLATE function translates a character string from a source
character set to a target character set. The TRANSLATE function changes
both the character string data type and the character set encoding of
the string.
```
TRANSLATE(character-value-expression USING translation-name)
```
* `_character-value-expression_`
+
is a character string.
* `_translation-name_`
+
is one of these translation names:
+
[cols="25%l,25%l,25%l,25%",options="header"]
|===
| Translation Name | Source Character Set | Target Character Set | Comments
| ISO88591TOUTF8 | ISO88591 | UTF8 | Translates ISO88591 characters to UTF8 characters. No data loss is possible.
| UTF8TOISO88591 | UTF8 | ISO88591 | Translates UTF8 characters to ISO88591 characters. {project-name} SQL will
display an error if it encounters a Unicode character that cannot be converted to the target character set.
|===
_translation-name_ identifies the translation, source and target
character set. When you translate to the UTF8 character set, no data
loss is possible. However, when {project-name} SQL translates a
_character-value-expression_ from UTF8, it may be that certain
characters cannot be converted to the target character set. {project-name}
SQL reports an error in this case.
{project-name} SQL returns a variable-length character string with character
repertoire equal to the character repertoire of the target character set
of the translation and the maximum length equal to the fixed length or
maximum variable length of the source _character-value-expression_.
If you enter an illegal _translation-name_, {project-name} SQL returns an
error.
If the character set for _character-value-expression_ is different from
the source character set as specified in the _translation-name_,
{project-name} SQL returns an error.
<<<
[[trim_function]]
== TRIM Function
The TRIM function removes leading and trailing characters from a
character string. Every character, including multi-byte characters, is
treated as one character.
```
TRIM ([[trim-type] [trim-char] FROM] trim-source)
```
* `_trim-type_` is:
+
```
LEADING | TRAILING | BOTH
```
* `_trim-type_`
+
specifies whether characters are to be trimmed from the leading end
(LEADING), trailing end (TRAILING), or both ends (BOTH) of
_trim-source_. If you omit _trim-type_, the default is BOTH.
* `_trim_char_`
+
is an SQL character value expression and specifies the character to be
trimmed from _trim-source. trim_char_ has a maximum length of 1. If you omit
_trim_char_, SQL trims blanks (' ') from _trim-source_.
* `_trim-source_`
+
is an SQL character value expression and specifies the string from which
to trim characters. See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_trim]]
=== Considerations for TRIM
[[result_of_trim]]
==== Result of TRIM
The result is always of type VARCHAR, with maximum length equal to the
fixed length or maximum variable length of _trim-source_. If the
source character string is an up-shifts CHAR or VARCHAR string, the
result is an up-shifts VARCHAR type.
<<<
[[examples_of_trim]]
=== Examples of TRIM
* Return 'Robert':
+
```
TRIM(' Robert ')
```
* The EMPLOYEE table defines FIRST_NAME as CHAR(15) and LAST_NAME as
CHAR(20). This expression uses the TRIM function to return the value
'Robert Smith' without extra blanks:
+
```
TRIM(first_name) || ' ' || TRIM (last_name)
```
<<<
[[ucase_function]]
== UCASE Function
The UCASE function up-shifts alphanumeric characters. For
non-alphanumeric characters, UCASE returns the same character. UCASE can
appear anywhere in a query where a value can be used, such as in a
select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE
predicate, an expression, or as qualifying a new value in an UPDATE or
INSERT statement. The result returned by the UCASE function is equal to
the result returned by the <<upper_function,UPPER Function>>
or <<upshift_function,UPSHIFT Function>>.
UCASE returns a string of fixed-length or variable-length character
data, depending on the data type of the input string.
UCASE is a {project-name} SQL extension.
```
UCASE (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression that specifies a string of
characters to upshift. See <<character_value_expressions,Character Value Expressions>>.
[[considerations_for_ucase]]
=== Considerations for UCASE
For a UTF8 character_expression, the UCASE function up-shifts all
lowercase or title case characters to uppercase and returns a character
string. If the argument is of type CHAR(_n_) or VARCHAR(_n_), the
result is of type VARCHAR(min(3_n_, 2048)), where the maximum length
of VARCHAR is the minimum of 3_n_ or 2048, whichever is smaller.
A lowercase character is a character that has the "alphabetic" property
in Unicode Standard 2 and whose Unicode name includes lower. An
uppercase character is a character that has the "alphabetic" property
and whose Unicode name includes upper. A title case character is a
character that has the Unicode "alphabetic" property and whose Unicode
name includes _title_.
<<<
[[examples_of_ucase]]
=== Examples of UCASE
* Suppose that your CUSTOMER table includes an entry for Hotel Oregon.
Select the column CUSTNAME and return in uppercase and lowercase letters
by using the UCASE and LCASE functions:
+
```
SELECT custname,UCASE(custname),LCASE(custname) FROM sales.customer;
(EXPR) (EXPR) (EXPR)
----------------- ------------------- ------------------
... ... ...
Hotel Oregon HOTEL OREGON hotel oregon
--- 17 row(s) selected.
```
+
See <<lcase_function,LCASE Function>>.
<<<
[[upper_function]]
=== UPPER Function
The UPPER function up-shifts alphanumeric characters. For
non-alphanumeric characters, UCASE returns the same character. UPPER can
appear anywhere in a query where a value can be used, such as in a
select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE
predicate, an expression, or as qualifying a new value in an UPDATE or
INSERT statement. The result returned by the UPPER function is equal to
the result returned by the <<upshift_function,UPSHIFT Function>> or <<ucase_function,UCASE Function>>.
UPPER returns a string of fixed-length or variable-length character
data, depending on the data type of the input string.
```
UPPER (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression that specifies a string of
characters to upshift.
See <<character_value_expressions,Character Value Expressions>>.
[[examples_of_upper]]
=== Examples of UPPER
* Suppose that your CUSTOMER table includes an entry for Hotel Oregon.
Select the column CUSTNAME and return in uppercase and lowercase letters
by using the UPPER and LOWER functions:
+
```
SELECT custname,UPPER(custname),LOWER(custname) FROM sales.customer;
(EXPR) (EXPR) (EXPR)
----------------- ------------------- ------------------
... ... ...
Hotel Oregon HOTEL OREGON hotel oregon
--- 17 row(s) selected.
```
+
See <<lower_function,LOWER Function>>.
+
For examples of when to use the UPPER function,
see <<upshift_function,UPSHIFT Function>>.
<<<
[[upshift_function]]
=== UPSHIFT Function
The UPSHIFT function up-shifts alphanumeric characters. For
non-alphanumeric characters, UCASE returns the same character. UPSHIFT
can appear anywhere in a query where a value can be used, such as in a
select list, an ON clause, a WHERE clause, a HAVING clause, a LIKE
predicate, an expression, or as qualifying a new value in an UPDATE or
INSERT statement. The result returned by the UPSHIFT function is equal
to the result returned by the <<upper_function,UPPER Function>> or
<<ucase_function,UCASE Function>>.
UPSHIFT returns a string of fixed-length or variable-length character
data, depending on the data type of the input string.
UPSHIFT is a {project-name} SQL extension.
```
UPSHIFT (character-expression)
```
* `_character-expression_`
+
is an SQL character value expression that specifies a string of
characters to upshift. See
<<character_value_expressions,Character Value Expressions>>.
[[examples_of_upshift]]
=== Examples of UPSHIFT
* Suppose that your CUSTOMER table includes an entry for Hotel Oregon.
Select the column CUSTNAME and return a result in uppercase and
lowercase letters by using the UPSHIFT, UPPER, and LOWER functions:
+
```
SELECT UPSHIFT(custname), UPPER(custname), UCASE(custname)
FROM sales.customer;
(EXPR) (EXPR) (EXPR)
----------------- ------------------- ------------------
... ... ...
HOTEL OREGON HOTEL OREGON HOTEL OREGON
--- 17 row(s) selected.
```
<<<
* Perform a case-insensitive search for the DataSpeed customer:
+
```
SELECT *
FROM sales.customer
WHERE UPSHIFT (custname) = 'DATASPEED';
CUSTNAME STREET CITY ...
---------- -------------------- --------- ...
DataSpeed 300 SAN GABRIEL WAY NEW YORK ...
--- 1 row(s) selected.
```
+
In the table, the name can be in lowercase, uppercase, or mixed case letters.
* Suppose that your database includes two department tables: DEPT1 and
DEPT2. Return all rows from the two tables in which the department names
have the same value regardless of case:
+
```
SELECT *
FROM persnl.dept1 D1, persnl.dept2 D2
WHERE UPSHIFT(D1.deptname) = UPSHIFT(D2.deptname);
```
<<<
[[rand_function]]
== RAND Function
The RAND function generates pseudo-random numbers that are uniformly distributed
in the range between 0 and 2^32^ (=4294967296),
using an integer as an optional seed value.
NOTE: The RAND function does not always behaves in predictive ways. It is non-deterministic
and may cause unexpected side effect. It evaluates only once or per row depending on the optimizer.
So it can only be used in very few places, please do not use it in a predicate.
```
RAND ([numeric-expression])
```
[[rand_function_syntax]]
=== Syntax Descriptions of RAND Function
* _numeric-expression_
+
is an SQL numeric value expression that specifies the value for the argument of the RAND function. For more information, see <<numeric_value_expressions,Numeric Value Expressions>>.
[[rand_function_considerations]]
=== Considerations for RAND Function
* When invoked with a seed value,
** the seed value should be an integer greater than or equal to 0, and cannot be NULL.
+
For example,
+
```
SQL>SELECT RAND(1-100) FROM DUAL;
*** ERROR[8432] A negative value cannot be converted to an unsigned numeric datatype. Source Type:SMALLINT SIGNED(MBIN16S) Source Value:-99 Target Type:LARGEINT(IBIN64S) Max Target Value:0. Instruction:RANGE_LOW_S16S64 Operation:RANGE_LOW. [2018-04-18 04:44:37]
SQL>SELECT RAND(NULL) FROM DUAL;
*** ERROR[4097] A NULL operand is not allowed in function (RANDOMNUM(NULL)). [2018-04-18 04:44:48]
```
** the RAND function will use the value to seed the random number generator.
** the RAND function will produce same series of numbers each time you seed the generator with an equal argument value
for each run of the program.
* When invoked with no seed value,
** the RAND function is automatically seeded with a value of system clock.
** the RAND function will produce different series of numbers even it is called consecutively within a query.
+
For example,
+
```
SQL>SELECT RAND(), RAND(), RAND() FROM DUAL;
(EXPR) (EXPR) (EXPR)
---------- ---------- ----------
1958129868 1958398780 1958482815
--- 1 row(s) selected.
```
* To get desired numbers within a specified range, you can use a combination of built-in functions, operators and clauses.
+
For example,
+
```
SQL>SELECT POWER(RAND(1)*10,3) FROM DUAL;
(EXPR)
------------------------
4.747561509943E15
--- 1 row(s) selected.
```
[[rand_function_example]]
=== Examples of RAND Function
* This example demostrates how to use the RAND function with built-in function (ABS function).
+
```
SQL>SELECT RAND(ABS(1-100)) FROM DUAL;
(EXPR)
----------
1663893
--- 1 row(s) selected.
```
* This example shows that the RAND function works with builts-in function (MOD function) to generate the number from 1 to 5 and gets
the randomized results sorted by ORDER BY clause. The 100,000 rows of c1 are neary evenly distributed for each number.
+
```
SQL>SELECT COUNT(*) FROM t1;
(EXPR)
--------------------
100000
--- 1 row(s) selected.
```
+
```
SQL>SHOWDDL t1;
CREATE TABLE TRAFODION.SEABASE.T1
(
IDX INT DEFAULT NULL NOT SERIALIZED
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
```
+
```
SQL>CREATE TABLE t2(idx int,c1 int);
--- SQL operation complete.
```
+
```
SQL>UPSERT USING LOAD INTO t2 SELECT idx, MOD(RAND(),5)+1 FROM t1;
--- 100000 row(s) inserted.
```
+
```
SQL>SELECT c1, COUNT(*) FROM t2 GROUP BY c1 ORDER BY c1;
C1 (EXPR)
----------- --------------------
1 19837
2 19951
3 20023
4 19913
5 20276
--- 5 row(s) selected.
```
+
At this point, the RAND function can also be used in the ORDER BY clause in conjunction with the LIMIT clause
to output rows.
+
```
SQL>SELECT c1, COUNT(*) FROM t2 GROUP BY c1 ORDER BY c1 LIMIT 3;
C1 (EXPR)
----------- --------------------
1 19837
2 19951
3 20023
--- 3 row(s) selected.
```
<<<
[[user_function]]
== USER Function
The USER function returns either the database user name associated with
the specified user ID number or the database user name of the current
user who invoked the function. The current user
is the authenticated user who started the session. That database
user name is used for authorization of SQL statements in the current
session.
```
USER [(user-id)]
```
* `_user-id_`
+
is the 32-bit number associated with a database user name.
+
The USER function is similar to the <<authname_function,AUTHNAME Function>>
and the <<current_user_function,CURRENT USER Function>>.
[[considerations_for_user]]
=== Considerations for USER
* This function can be specified only in the top level of a SELECT statement.
* The value returned is string data type VARCHAR(128) and is in ISO88591 encoding.
[[examples_of_user]]
=== Examples of USER
* This example shows the database user name of the current user who is
logged in to the session:
+
```
SELECT USER FROM (values(1)) x(a);
(EXPR)
-------------------------
TSHAW
--- 1 row(s) selected.
```
<<<
* This example shows the database user name associated with the user ID number, 33333:
+
```
SELECT USER (33333) FROM (values(1)) x(a);
(EXPR)
-------------------------
DB ROOT
--- 1 row(s) selected.
```
<<<
[[variance_function]]
== VARIANCE Function
VARIANCE is an aggregate function that returns the statistical variance
of a set of numbers. VARIANCE is a {project-name} SQL extension.
```
VARIANCE ([ALL | DISTINCT] expression [, weight])
```
* `ALL | DISTINCT`
+
specifies whether duplicate values are included in the computation of
the VARIANCE of the _expression_. The default option is ALL, which
causes duplicate values to be included. If you specify DISTINCT,
duplicate values are eliminated before the VARIANCE function is applied.
If DISTINCT is specified, you cannot specify _weight_.
* `_expression_`
+
specifies a numeric value expression that determines the values for
which to compute the variance. _expression_ cannot contain an aggregate
function or a subquery. The DISTINCT clause specifies that the VARIANCE
function operates on distinct values from the one-column table derived
from the evaluation of _expression_.
* `_weight_`
+
specifies a numeric value expression that determines the weights of the
values for which to compute the variance. _weight_ cannot contain an
aggregate function or a subquery. _weight_ is defined on the same table
as _expression_. The one-column table derived from the evaluation of
_expression_ and the one-column table derived from the evaluation of
_weight_ must have the same cardinality.
[[considerations_for_variance]]
=== Considerations for VARIANCE
[[definition_of_variance]]
==== Definition of VARIANCE
Suppose that _vi_ are the values in the one-column table derived from
the evaluation of _expression_. _N_ is the cardinality of this
one-column table that is the result of applying the _expression_ to each
row of the source table and eliminating rows that are null.
If _weight_ is specified, _wi_ are the values derived from the
evaluation of _weight_. _N_ is the cardinality of the two-column table
that is the result of applying the _expression_ and _weight_ to each row
of the source table and eliminating rows that have nulls in either
column.
===== Definition When Weight Is Not Specified
If _weight_ is not specified, the statistical variance of the values in
the one-column result table is defined as:
where _vi_ is the i-th value of _expression_, _v_ is the average value
expressed in the common data type, and N is the cardinality of the
result table.
Because the definition of variance has _N-1_ in the denominator of the
expression (when weight is not specified), {project-name} SQL returns a
default value of zero (and no error) if the number of rows in the table,
or a group of the table, is equal to 1.
===== Definition When Weight Is Specified
If _weight_ is specified, the statistical variance of the values in the
two-column result table is defined as:
where vi is the i-th value of _expression_, _wi_ is the i-th value of
_weight_, _vw_ is the weighted average value expressed in the common
data type, and N is the cardinality of the result table.
===== Weighted Average
The weighted average _vw_ of _vi_ and _wi_ is defined as:
where vi is the i-th value of _expression_, _wi_ is the i-th value of
_weight_, and N is the cardinality of the result table.
[[data_type_of_the_result]]
==== Data Type of the Result
The data type of the result is always DOUBLE PRECISION.
[[operands_of_the_expression]]
==== Operands of the Expression
The expression includes columns from the rows of the SELECT result
table &#8212; but cannot include an aggregate function. These expressions are
valid:
```
VARIANCE (SALARY) VARIANCE (SALARY * 1.1)
VARIANCE (PARTCOST * QTY_ORDERED)
```
[[variance_nulls]]
==== Nulls
VARIANCE is evaluated after eliminating all nulls from the set. If the
result table is empty, VARIANCE returns NULL.
[[float54_and_double_precision_data]]
==== FLOAT(54) and DOUBLE PRECISION Data
Avoid using large FLOAT(54) or DOUBLE PRECISION values as arguments to
VARIANCE. If SUM(x * x) exceeds the value of 1.15792089237316192e77 during
the computation of VARIANCE(x), then a numeric overflow occurs.
[[examples_of_variance]]
=== Examples of VARIANCE
* Compute the variance of the salary of the current employees:
+
```
SELECT VARIANCE(salary) AS Variance_Salary FROM persnl.employee;
VARIANCE_SALARY
-------------------------
1.27573263588496116E+009
--- 1 row(s) selected.
```
* Compute the variance of the cost of parts in the current inventory:
+
```
SELECT VARIANCE (price * qty_available) FROM sales.parts;
(EXPR)
-------------------------
5.09652410092950336E+013
--- 1 row(s) selected.
```
<<<
[[week_function]]
== WEEK Function
The WEEK function converts a DATE or TIMESTAMP expression into an
INTEGER value in the range 1 through 54 that represents the
corresponding week of the year. If the year begins on a Sunday, the
value 1 will be returned for any datetime that occurs in the first 7
days of the year. Otherwise, the value 1 will be returned for any
datetime that occurs in the partial week before the start of the first
Sunday of the year. The value 53 is returned for datetimes that occur in
the last full or partial week of the year except for leap years that
start on Saturday where December 31 is in the 54th full or partial week.
WEEK is a {project-name} SQL extension.
```
WEEK (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_week]]
=== Examples of WEEK
* Return an integer that represents the week of the year from the
START_DATE column in the PROJECT table:
+
```
SELECT start_date, ship_timestamp, WEEK(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- --------------
2008-04-10 2008-04-21 08:15:00.000000 |15
```
<<<
[[year_function]]
== YEAR Function
The YEAR function converts a DATE or TIMESTAMP expression into an
INTEGER that represents the year.
YEAR is a {project-name} SQL extension.
```
YEAR (datetime-expression)
```
* `_datetime-expression_`
+
is an expression that evaluates to a datetime value of type DATE or
TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[examples_of_year]]
=== Examples of YEAR
* Return an integer that represents the year from the start date column in
the project table:
+
```
SELECT start_date, ship_timestamp, YEAR(start_date)
FROM persnl.project
WHERE projcode = 1000;
Start/Date Time/Shipped (EXPR)
---------- -------------------------- ------
2008-04-10 2008-04-21 08:15:00.000000 2008
```
<<<
[[zeroifnull_function]]
== ZEROIFNULL Function
The ZEROIFNULL function returns a value of zero if the expression if
NULL. Otherwise, it returns the value of the expression.
```
ZEROIFNULL (expression)
```
* `_expression_`
+
specifies a value expression. It must be a numeric data type.
[[examples_of_zeroifnull]]
=== Examples of ZEROIFNULL
* ZEROIFNULL returns the value of the column named salary whenever the
column value is not NULL and it returns 0 whenever the column value is
NULL.
+
```
ZEROIFNULL (salary)
```