You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by sv...@apache.org on 2016/01/14 19:18:33 UTC
[29/42] incubator-trafodion git commit: Completed functions and
expressions.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/9bcd0e2b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 3267dcf..5bbf66d 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -53,7 +53,7 @@ of the expression argument of the function.
*) 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).
-| <<max/maximum_function,MAX/MAXIMUM Function>> | Determines a maximum value from the group of values derived from the
+| <<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.
@@ -131,7 +131,7 @@ expression.
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/substr_function,SUBSTRING/SUBSTR Function>> | Extracts a substring from a character string.
+| <<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.
@@ -298,7 +298,6 @@ Offset sequence function
[cols="25%,75%"]
|===
-| <<rank/runningrank_function,RANK/RUNNINGRANK Function>> | Returns the rank of the given value of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement
| <<runningavg_function,RUNNINGAVG Function>> | Returns the average of nonnull 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.
@@ -766,8 +765,8 @@ 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:
+The expression includes columns from the rows of the SELECT result table but
+cannot include an aggregate function. These expressions are valid:
```
AVG (SALARY)
@@ -775,7 +774,7 @@ AVG (SALARY * 1.1)
AVG (PARTCOST * QTY_ORDERED)
```
-[[nulls]]
+[[avg_nulls]]
==== Nulls
All nulls are eliminated before the function is applied to the set of
@@ -1393,6 +1392,7 @@ CODE_VALUE is a Trafodion SQL extension.
```
CODE_VALUE(character-value-expression)
character-set
+```
* `_character-value-expression_`
+
@@ -1439,7 +1439,7 @@ See <<character_value_expressions,Character Value Expressions>>.
[[concatenation_operator]]
-=== Concatenation Operator (\|\|)
+=== Concatenation Operator (||)
The concatenation operator, denoted by two vertical bars (||),
concatenates two string values to form a new string value. To indicate
@@ -1471,7 +1471,6 @@ 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
@@ -1495,26 +1494,20 @@ 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))));
+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)));
+VALUES (002, 'Executed at ' || CAST (CURRENT_TIMESTAMP AS CHAR(26)));
+```
<<<
[[converttohex_function]]
@@ -1525,7 +1518,9 @@ hexadecimal for display purposes.
CONVERTTOHEX is a Trafodion SQL extension.
-CONVERTTOHEX (_expression_)
+```
+CONVERTTOHEX (expression)
+```
_expression_
@@ -1538,7 +1533,7 @@ 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
+=== Considerations for CONVERTTOHEX
Although CONVERTTOHEX is usable on datetime and interval expressions,
the displayed output shows the internal value and is, consequently, not
@@ -1547,6 +1542,7 @@ future releases.
CONVERTTOHEX returns ASCII characters in ISO8859-1 encoding.
+<<<
[[examples_of_converttohex]]
=== Examples of CONVERTTOHEX
@@ -1593,8 +1589,9 @@ SELECT CONVERTTOHEX(D1), CONVERTTOHEX(T1), CONVERTTOHEX(TS1), CONVERTTOHEX(TS2)
(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:
+
@@ -1610,3 +1607,6279 @@ SELECT CONVERTTOHEX(IV1), CONVERTTOHEX(IV2), CONVERTTOHEX(IV3) from IVT;
------ -------- -----------------------
0001 0026 0000027C2F9CB780
```
+
+<<<
+[[converttimestamp_function]]
+== CONVERTTIMESTAMP Function
+
+The CONVERTTIMESTAMP function converts a Julian timestamp to a value
+with data type TIMESTAMP.
+
+CONVERTTIMESTAMP is a Trafodion 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 Trafodion 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 Trafodion 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.
+```
+
+<<<
+[[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 username of the current
+user who invoked the function. The current user is the authenticated
+user who started the session. That database username 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 ISO8859-1 encoding.
+
+
+[[examples_of_current_user]]
+=== Examples of CURRENT_USER
+
+* This example retrieves the database username 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 Trafodion 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 Trafodion 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 Trafodion 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 Trafodion 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 Trafodion 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 value for the number of
+_datepart_ units of time between _startdate_ and _enddate_. If
+_enddate_ precedes _startdate_, the return value is negative or zero.
+
+DATEDIFF is a Trafodion SQL extension.
+
+```
+DATEDIFF (datepart, startdate, enddate)
+```
+
+* `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.
+See <<datetime_value_expressions,Datetime Value Expressions>>.
+
+* `enddate`
++
+may be of type DATE or TIMESTAMP.
+See <<datetime_value_expressions,Datetime Value Expressions>>.
+
+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 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 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. The DATEDIFF 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_datediff]]
+=== Examples of DATEDIFF
+
+* This function returns the value of 0 because no one-second boundaries
+are crossed.
++
+```
+DATEDIFF( SECOND
+ , TIMESTAMP '2006-09-12 11:59:58.999998'
+ , TIMESTAMP '2006-09-12 11:59:58.999999'
+ )
+```
+
+* This function returns the value 1 because a one-second boundary is
+crossed even though the two timestamps differ by only one microsecond.
++
+```
+DATEDIFF( SECOND
+ , TIMESTAMP '2006-09-12 11:59:58.999999'
+ , TIMESTAMP '2006-09-12 11:59:59.000000'
+ )
+```
+
+* This function returns the value of 0.
++
+```
+DATEDIFF( YEAR
+ , TIMESTAMP '2006-12-31 23:59:59.999998'
+ , TIMESTAMP '2006-12-31 23:59:59.999999'
+ )
+```
+
+* This function returns the value of 1 because a year boundary is
+crossed.
++
+```
+DATEDIFF( YEAR
+ , TIMESTAMP '2006-12-31 23:59:59.999999'
+ , TIMESTAMP '2007-01-01 00:00:00.000000'
+ )
+```
+
+* This function returns the value of 2 because two WEEK boundaries are
+crossed.
++
+```
+DATEDIFF(WEEK, DATE '2006-01-01', DATE '2006-01-09')
+```
+
+* This function returns the value of -29.
++
+```
+DATEDIFF(DAY, DATE '2008-03-01', DATE '2008-02-01')
+```
+
+<<<
+[[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 Trafodion 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 ISO8859-1
+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 Trafodion 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 Trafodion
+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 Trafodion extension.
+
+```
+DATEPART(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 201 07.
++
+```
+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 Trafodion 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', …) cannot be used on years less than 10.
+* DATE_TRUNC( 'CENTURY', …) 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 Trafodion 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 Trafodion 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 ISO8859-1.
+
+[[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 value 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 Trafodion 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 ValueE xpressions>>.
+
+[[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 value 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 Trafodion 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 value in the range 1 through 366 that represents the
+corresponding day of the year.
+
+DAYOFYEAR is a Trafodion 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 Trafodion SQL extension.
+
+```
+DECODE (expr, test-expr, retval [, test-expr2, retval2 ... ] [ , default ] )
+```
+
+* `_expr_`
++
+is an SQL expression.
+
+* `_test-expr_, _test-expr_, …`
++
+are each an SQL expression of a type comparable to that of _expr_.
+
+* `_retval_`
++
+is an SQL expression.
+
+* `_default_, _retval2_, …`
++
+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 nonpadded 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 Trafodion SQL encounters
+this situation, it assumes that the return value will be NUMERIC(18,6).
+Once Trafodion 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 Trafodion 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 Trafodion 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, Trafodion 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, Trafodion 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 Trafodion 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, Trafodion 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, Trafodion 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.
+```
+
+<<<
+[[exp_function]]
+== EXP Function
+
+This function returns the exponential value (to the base e) of a numeric
+value expression. EXP is a Trafodion 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
+
+Trafodion 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 Trafodion 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 — 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 Trafodion 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.
+
+<<<
+[[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.
+
+
+<<<
+[[hour_function]]
+=== HOUR Function
+
+The HOUR function converts a TIME or TIMESTAMP expression into an
+INTEGER value in the range 0 through 23 that represents the
+corresponding hour of the day.
+
+HOUR is a Trafodion 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 multibyte
+characters, is treated as one character.
+
+INSERT is a Trafodion 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.
+```
+
+<<<
+[[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 Trafodion 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 Trafodion 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, Trafodion 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
+nonnull 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 Trafodion 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 nonnull 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 downshifts 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 Trafodion SQL extension.
+
+```
+LCASE (character-expression)
+```
+
+* `_character-expression_`
++
+is an SQL character value expression that specifies a string of
+characters to downshift. 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 multibyte
+characters, is treated as one character.
+
+LEFT is a Trafodion 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, Trafodion SQL returns the character
+position of the substring within the string. Every character, including
+multibyte 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 Trafodion 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>>.
+
+Trafodion SQL returns the result as a 2-byte signed integer with a scale
+of zero. If substring-expression is not found in source-expression , Trafodion
+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, Trafodion SQL returns 0.
+* If the length of _substring-expression_ is zero, Trafodion SQL returns 1.
+* If the length of _substring-expression_ is greater than the length of
+_source-expression_, Trafodion SQL returns 0.
+* If _source-expression_ is a null value, Trafodion 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 Trafodion 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 Trafodion 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 downshifts 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 downshift.
+See <<character_value_expressions,Character Value Expressions>>.
+
+[[considerations_for_lower]]
+=== Considerations for LOWER
+
+For a UTF8 character expression, the LOWER function downshifts 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 multibyte characters,
+is treated as one character.
+
+LPAD is a Trafodion 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 Trafodion 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.
+```
+
+<<<
+[[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 — 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 value, in the range 0 through 59, that represents the
+corresponding minute of the hour.
+
+MINUTE is a Trafodion 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 Trafodion 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 Trafodion 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 Trafodion 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 ISO8859-1.
+
+[[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 nonnull 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 Trafodion 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 nonnull 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 nonnull 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 Trafodion 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
<TRUNCATED>