* (MULTIPLICATION)

Syntax

expr1 * expr2

Description

It returns the result of multiplying expr1 and expr2.

The type of multiplication operation and the resulting type are as follows in the table.
For more information, refer to Type Conversion.

Numeric * operation
expr1 (expr2)	expr2 (expr1)	Result type
NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_BIGINT
NUMBER	NUMBER	NUMBER
NATIVE DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

INTERVAL * operation
expr1 (expr2)	expr2 (expr1)	Result type
INTERVAL YEAR TO MONTH	Numeric type	INTERVAL YEAR TO MONTH (The result type is an interval type.)
INTERVAL DAY TO SECOND	Numeric type	INTERVAL DAY TO SECOND (The result type is an interval type.)
Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

INTERVAL type specified in the table includes the following detailed INTERVAL subtypes
INTERVAL YEAR TO MONTH	INTERVAL DAY TO SECOND
INTERVAL YEAR INTERVAL MONTH INTERVAL YEAR TO MONTH	INTERVAL DAY INTERVAL HOUR INTERVAL MINUTE INTERVAL SECOND INTERVAL DAY TO HOUR INTERVAL DAY TO MINUTE INTERVAL DAY TO SECOND INTERVAL HOUR TO MINUTE INTERVAL HOUR TO SECOND INTERVAL MINUTE TO SECOND

Example

gSQL> SELECT INTERVAL'1-2'YEAR TO MONTH * 2 AS RESULT FROM DUAL;
RESULT    
----------
+000002-04
1 row selected.

gSQL> SELECT INTERVAL'1 01:02:03.400000'DAY TO SECOND * 2 AS RESULT 
      FROM DUAL;
RESULT                 
-----------------------
+000002 02:04:06.800000
1 row selected.

+ (ADDITION)

Syntax

expr1 + expr2

Description

It returns the result of adding expr1 and expr2.

The type of addition operation and the resulting types are as follows in the table.
For more information, refer to Type Conversion.

Numeric + operation
expr1 (expr2)	expr2 (expr1)	Result type
NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_BIGINT
NUMBER	NUMBER	NUMBER
NATIVE DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

(DATETIME/INTERVAL) + operation
expr1 (expr2)	expr2 (expr1)	Result type
DATE	NUMERIC	DATE
DATE	INTERVAL YEAR TO MONTH	DATE
DATE	INTERVAL DAY	DATE
DATE	INTERVAL DAY TO SECOND	TIMESTAMP
TIME	NUMERIC	TIME
TIME	INTERVAL YEAR TO MONTH	TIME
TIME	INTERVAL DAY TO SECOND	TIME
TIME WITH TIME ZONE	NUMERIC	TIME WITH TIME ZONE
TIME WITH TIME ZONE	INTERVAL YEAR TO MONTH	TIME WITH TIME ZONE
TIME WITH TIME ZONE	INTERVAL DAY TO SECOND	TIME WITH TIME ZONE
TIMESTAMP	NUMERIC	TIMESTAMP
TIMESTAMP	INTERVAL YEAR TO MONTH	TIMESTAMP
TIMESTAMP	INTERVAL DAY TO SECOND	TIMESTAMP
TIMESTAMP WITH TIME ZONE	NUMERIC	TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE	INTERVAL YEAR TO MONTH	TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE	INTERVAL DAY TO SECOND	TIMESTAMP WITH TIME ZONE
INTERVAL YEAR TO MONTH	INTERVAL YEAR TO MONTH	INTERVAL YEAR TO MONTH (The result type includes all the interval range of expr1 and expr2.)
INTERVAL DAY TO SECOND	INTERVAL DAY TO SECOND	INTERVAL DAY TO SECOND (The result type includes all the interval range of expr1 and expr2.)
Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

Example

gSQL> SELECT TO_DATE( '2012-05-05', 'YYYY-MM-DD' ) + 5 AS RESULT FROM DUAL;
RESULT    
----------
2012-05-10
1 row selected.

gSQL> SELECT 
      TO_DATE( '2012-05-05', 'YYYY-MM-DD' ) + INTERVAL'01-01'YEAR TO MONTH
      AS RESULT 
      FROM DUAL;
RESULT    
----------
2013-06-05
1 row selected.

gSQL> SELECT 
      INTERVAL'01-01'YEAR TO MONTH + INTERVAL'02-10'YEAR TO MONTH 
      AS RESULT 
      FROM DUAL;
RESULT    
----------
+000003-11
1 row selected.

+ (POSITIVE)

Syntax

+ expr

Description

It displays the + sign on expr.

Example

gSQL> SELECT +3 AS RESULT1, +(-3) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
      3      -3
1 row selected.

- (NEGATIVE)

Syntax

- expr

Description

It displays the - sign on expr.

Example

gSQL> SELECT -3 AS RESULT1, -(-3) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
     -3       3
1 row selected.

- (SUBTRACTION)

Syntax

expr1 - expr2

Description

It returns the result of subtracting expr1 and expr2.

The type of subtraction operation and the resulting types are as follows in the table.
For more information, refer to Type Conversion.

Numeric - operation
expr1	expr2	Result type
NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_BIGINT
NUMBER	NUMBER	NUMBER
NATIVE DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

(DATETIME/INTERVAL) - operation
expr1	expr2	Result type
DATE	DATE	NUMBER
DATE	Numeric type	DATE
DATE	INTERVAL YEAR TO MONTH	DATE
DATE	INTERVAL DAY	DATE
DATE	INTERVAL DAY TO SECOND	TIMESTAMP
TIME	TIME	INTERVAL DAY TO SECOND
TIME	Numeric type	TIME
TIME	INTERVAL YEAR TO MONTH	TIME
TIME	INTERVAL DAY TO SECOND	TIME
TIME WITH TIME ZONE	Numeric type	TIME WITH TIME ZONE
TIME WITH TIME ZONE	INTERVAL YEAR TO MONTH	TIME WITH TIME ZONE
TIME WITH TIME ZONE	INTERVAL DAY TO SECOND	TIME WITH TIME ZONE
TIMESTAMP	TIMESTAMP	INTERVAL DAY TO SECOND
TIMESTAMP	Numeric type	TIMESTAMP
TIMESTAMP	INTERVAL YEAR TO MONTH	TIMESTAMP
TIMESTAMP	INTERVAL DAY TO SECOND	TIMESTAMP
TIMESTAMP WITH TIME ZONE	TIMESTAMP WITH TIME ZONE	INTERVAL DAY TO SECOND
TIMESTAMP WITH TIME ZONE	Numeric type	TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE	INTERVAL YEAR TO MONTH	TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH TIME ZONE	INTERVAL DAY TO SECOND	TIMESTAMP WITH TIME ZONE
INTERVAL YEAR TO MONTH	INTERVAL YEAR TO MONTH	INTERVAL YEAR TO MONTH (The result type includes all the interval range of expr1 and expr2.)
INTERVAL DAY TO SECOND	INTERVAL DAY TO SECOND	INTERVAL DAY TO SECOND (The result type includes all the interval range of expr1 and expr2.)
Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

Example

gSQL> SELECT 
      TO_DATE( '2012-05-05' ) - TO_DATE( '2012-05-01' ) AS RESULT 
      FROM DUAL;
RESULT
------
     4
1 row selected.

gSQL> SELECT TO_DATE( '2012-05-05' ) - 3 AS RESULT FROM DUAL;
RESULT    
----------
2012-05-02
1 row selected.

gSQL> SELECT 
      TO_DATE( '2012-05-05' ) - INTERVAL'01-02'YEAR TO MONTH AS RESULT 
      FROM DUAL;
RESULT    
----------
2011-03-05
1 row selected.

gSQL> SELECT
      INTERVAL'05-01'YEAR TO MONTH - INTERVAL'02-01'YEAR TO MONTH
      AS RESULT 
      FROM DUAL;
RESULT    
----------
+000003-00
1 row selected.

gSQL> SELECT INTERVAL'15 23:59:59.999999'DAY TO SECOND 
           - INTERVAL'10 23:59:59.999999'DAY TO SECOND AS RESULT 
      FROM DUAL;
RESULT                 
-----------------------
+000005 00:00:00.000000
1 row selected.

/ (DIVISION)

Syntax

expr1 / expr2

Description

It returns the result of dividing expr1 and expr2.

The type of division operation and the resulting types are as follows in the table.
For more information, refer to Type Conversion.

Numeric / operation
expr1	expr2	Result type
NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER	NUMBER
NATIVE_DOUBLE	NATIVE_DOUBLE	NATIVE_DOUBLE

(DATETIME/INTERVAL) / operation
expr1	expr2	Result type
INTERVAL YEAR TO MONTH	Numeric type	INTERVAL YEAR TO MONTH (The result type is an interval type.)
INTERVAL DAY TO SECOND	Numeric type	INTERVAL DAY TO SECOND (The result type is an interval type.)
Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

Example

gSQL> SELECT INTERVAL'20-10'YEAR TO MONTH / 2 AS RESULT FROM DUAL;
RESULT    
----------
+000010-05
1 row selected.

gSQL> SELECT   INTERVAL'02 02:04:06.800000'DAY TO SECOND / 2 AS RESULT 
      FROM DUAL;
RESULT                 
-----------------------
+000001 01:02:03.400000
1 row selected.

|| (CONCATENATE)

Syntax

str1 || str2

Description

CONCATENATE returns a string that is the result of concatenating str1 and str2.

If either str1 or str2 is NULL, the non-NULL string is returned. If both str1 and str2 are NULL, the result will also be NULL.

The argument can be a type that can be converted to either a character string type or a binary string type.
For more information, refer to Type Conversion.

It is an alias of CONCAT and CONCATENATE.

The result types are as follows.

The result types of || (CONCATENATE)
Data type	CHAR	VARCHAR	LONG VARCHAR
CHAR	CHAR	VARCHAR	LONG VARCHAR
VARCHAR	VARCHAR	VARCHAR	LONG VARCHAR
LONG VARCHAR	LONG VARCHAR	LONG VARCHAR	LONG VARCHAR
Data type	BINARY	VARBINARY	LONG VARBINARY
BINARY	BINARY	VARBINARY	LONG VARBINARY
VARBINARY	VARBINARY	VARBINARY	LONG VARBINARY
LONG VARBINARY	LONG VARBINARY	LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT 'DATA' || 'BASE' AS RESULT1,
             'DATA' || NULL   AS RESULT2,
               NULL || NULL   AS RESULT3 
       FROM DUAL;
RESULT1  RESULT2 RESULT3
-------- ------- -------
DATABASE DATA    null   
1 row selected.

ABS

Syntax

ABS( num )

Description

ABS returns the absolute value of num.

The num argument can be a numeric type or any type that can be converted to a number.
If num is NULL, the function returns NULL.

Example

gSQL> SELECT ABS(-1) AS RESULT1, ABS(1) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
      1       1
1 row selected.

ACOS

Syntax

ACOS( num )

Description

ACOS returns the arc cosine value of num.

The num argument must be in the range of -1 to 1. 
If num is NULL, the function returns NULL.

It returns a value in radians, which is in the range of 0 to pi.

Example

gSQL> SELECT ACOS( 1 ) FROM DUAL;
ACOS( 1 )
---------
        0
1 row selected.

ADDDATE

Syntax

ADDDATE( date, INTERVAL expr unit  )
ADDDATE( expr, days )

Description

ADDDATE adds the second argument to the first argument and returns the result.

If either of the input arguments is NULL, the result will also be NULL.
The first argument can be of type DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE, while the second argument can be of type INTERVAL or numeric.

The result type is the same as (DATETIME/INTERVAL) + operation.

Example

gSQL> SELECT ADDDATE( TO_DATE( '2012-12-12', 'YYYY-MM-DD' ), 1 ) AS RESULT
        FROM DUAL;
RESULT    
----------
2012-12-13
1 row selected.

gSQL> SELECT ADDDATE( TO_DATE( '2012-11-11', 'YYYY-MM-DD' ),
                      INTERVAL'01-01'YEAR TO MONTH ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2013-12-11
1 row selected.

ADDTIME

Syntax

ADDTIME( expr1, expr2 )

Description

ADDTIME adds expr2 to expr1, and returns the result.

expr1 can be of type TIME, TIME WITH TIME ZONE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE TYPE, while expr2 can be of type INTERVAL DAY TO SECOND TYPE.

If either expr1 or expr2 is NULL, the result will also be NULL.

The result type is the same as (DATETIME/INTERVAL) + operation.

Example

gSQL> SELECT
      ADDTIME( TO_TIMESTAMP( '2001-05-05 06:00:00', 
                             'YYYY-MM-DD HH24:MI:SS' ),
               INTERVAL'0 00:06:06.666666'DAY TO SECOND ) AS RESULT
      FROM DUAL;
RESULT                    
--------------------------
2001-05-05 06:06:06.666666
1 row selected.

ADD_MONTHS

Syntax

ADD_MONTHS( date, number )

Description

ADD_MONTHS returns the date obtained by adding the specified number of months to the given date. If the resulting date is later than the last day of the month, it is adjusted to the last day of that month.

The data type of the date argument can be DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE, and the number argument can be a numeric type.
If any of the input arguments is NULL, the result will also be NULL.

The result type is always DATE, regardless of the input argument's date type.

Example

gSQL> SELECT 
      ADD_MONTHS( TO_DATE( '2001-07-31', 'YYYY-MM-DD' ), 1 ) AS RESULT1,
      ADD_MONTHS( TO_DATE( '2001-07-31', 'YYYY-MM-DD' ), 2 ) AS RESULT2
      FROM DUAL;
RESULT1    RESULT2   
---------- ----------
2001-08-31 2001-09-30
1 row selected.

APPROX_COUNT_DISTINCT

Syntax

APPROX_COUNT_DISTINCT( expr [, expr, ...] ) [ FILTER ( [ WHERE ] condition ) ]

Description

As an aggregation function, it estimates the approximate number of rows with non-NULL values among the distinct values of expr.

APPROX_COUNT_DISTINCT provides results that are nearly identical to those of COUNT(DISTINCT expr), while processing large volumes of data much faster.

If a FILTER is specified, aggregation is performed only on values that satisfy the specified condition.

Example

gSQL> SELECT APPROX_COUNT_DISTINCT( c1 ) FROM t1;

APPROX_COUNT_DISTINCT( C1 )
---------------------------
                         99

1 row selected.

gSQL> SELECT APPROX_COUNT_DISTINCT( c1 ) FILTER ( c2 > 50 ) FROM t1;

APPROX_COUNT_DISTINCT( C1 ) FILTER ( C2 > 50 )
----------------------------------------------
                                            49

1 row selected.

The APPROX_COUNT_DISTINCT function estimates the number of distinct values based on the HyperLogLog algorithm.

ASCII

Syntax

ASCII( char )

Description

It returns the database character set code of the first character of char in decimal form.

The data type of char can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or any type that can be converted to a character type, and the return type is NUMBER.
If char is NULL, the function returns NULL.

Example

gSQL> SELECT ASCII( 'G' ) AS RESULT FROM DUAL;
RESULT
------
    71
1 row selected.

ASIN

Syntax

ASIN( num )

Description

ASIN returns the arc sin value of num.

The num argument must be in the range of -1 to 1.
If num is NULL, the function returns NULL.

It returns a radian value in the range of -pi/2 to pi/2.

Example

gSQL> SELECT ASIN( 0 ) FROM DUAL;
ASIN( 0 )
---------
        0
1 row selected.

ATAN

Syntax

ATAN( num )

Description

ATAN returns the arc tangent value of num.

There is no range limitation for the value of num, and it returns a radian value in the range of -pi/2 to pi/2. 
If num is NULL, it returns NULL.

Example

gSQL> SELECT ATAN(0.5) FROM DUAL;
       ATAN(0.5)
----------------
.463647609000806
1 row selected.

ATAN2

Syntax

ATAN2( num1, num2 )

Description

ATAN2 returns the arc tangent value of num1 and num2.

There is no range limitation for the value of num1 argument, and it returns a radian value in the range of -pi to pi.
If either num1 or num2 is NULL, the function returns NULL.

Example

gSQL> SELECT ATAN2(1,2) FROM DUAL; 
      ATAN2(1,2)
----------------
.463647609000806
1 row selected.

AVG

Syntax

AVG( [ ALL | DISTINCT ] num ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is used as an aggregation function to calculate the average value of exprs.

When ALL is specified, aggregation is performed on all values.
When DISTINCT is specified, aggregation is performed on the values with duplicates removed.
When neither ALL nor DISTINCT is specified, it is treated the same as if ALL were specified.

If FILTER is specified, aggregation is performed only for values that satisfy the condition.

Example

gSQL> SELECT AVG( c1 ) FROM t1;

AVG(C1)
-------
      2

1 row selected.


gSQL> SELECT AVG( c1 ) FILTER( WHERE c1 > 1 ) FROM t1;

AVG(C1)
-------
      3

1 row selected.

AVG() OVER

Syntax

AVG ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function AVG calculates the average value of expr.
NULL values are excluded from the calculation.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , AVG( min_price ) OVER ( ORDER BY min_price ) AS "AVG"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE              AVG
--------- ----------------
       73               73
      247              160
      731 350.333333333333
     null 350.333333333333
     null 350.333333333333

5 rows selected.

BITAND

Syntax

BITAND( num1, num2 )

Description

It returns the result of the AND operation on the bits of num1 and num2.

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or any data type that can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT, the decimal point is truncated.
If any of the input argument values is NULL, the result will also be NULL.

The result type is NATIVE_BIGINT.

Example

gSQL> SELECT BITAND( 5, 3 ) AS RESULT FROM DUAL;
RESULT
------
     1
1 row selected.

BITNOT

Syntax

BITNOT( num )

Description

It returns the result of the NOT operation on the num bit.

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or any data type that can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT, the decimal point is truncated.
If the input argument is NULL, the result will also be NULL.

The result type is as follows.
• If the input argument is NATIVE_SMALLINT, the result type is NATIVE_SMALLINT.
• If the input argument is NATIVE_INTEGER, the result type is NATIVE_INTEGER.
• If the input argument is NATIVE_BIGINT, the result type is NATIVE_BIGINT.

Example

gSQL> SELECT BITNOT( 5 ) AS RESULT FROM DUAL;
RESULT
------
    -6
1 row selected.

BITOR

Syntax

BITOR( num1, num2 )

Description

It returns the result of the OR operation on the bits of num1 and num2.

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or any data type that can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT, the decimal point is truncated.
If any input argument is NULL, the result will also be NULL.

The result type is NATIVE_BIGINT.

Example

gSQL> SELECT BITOR( 5, 3 ) FROM DUAL;

BITOR( 5, 3 )
-------------
            7
1 row selected.

BITXOR

Syntax

BITXOR( num1, num2 )

Description

It returns the result of the XOR operation result on the bits of num1 and num2.

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or any data type that can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT, the decimal point is truncated.
If any input argument is NULL, the result will be NULL.

The result type is NATIVE_BIGINT.

Example

gSQL> SELECT BITXOR( 5, 3 ) FROM DUAL;
BITXOR( 5, 3 )
--------------
             6
1 row selected.

BIT_LENGTH

Syntax

BIT_LENGTH( str )

Description

BIT_LENGTH returns the number of bits for the given str.
If str is NULL, it returns NULL.

Example

gSQL> SELECT BIT_LENGTH( 'LIKE' ) AS RESULT FROM DUAL;
     RESULT   
     ---------------
             32
    1 row selected.

BYTE_LENGTH

Syntax

BYTE_LENGTH( str )

Description

It is an alias of OCTET_LENGTH.
For more information, refer to OCTET_LENGTH, LENGTHB.

Example

Multi-byte character set (e.g. UTF8): 1-byte character

gSQL> SELECT BYTE_LENGTH( 'OCTET_LENGTH' ) AS RESULT_1BYTE_CHARACTERS 
        FROM DUAL;
RESULT_1BYTE_CHARACTERS
-----------------------
                     12
1 row selected.

Multi-byte character set (e.g. UTF8): 2-byte character

gSQL> SELECT BYTE_LENGTH( 'αβ' ) AS RESULT_2BYTE_CHARACTERS FROM DUAL;
RESULT_2BYTE_CHARACTERS
-----------------------
                      4
1 row selected.

CASE2

Syntax

CASE2( condition1, result1
      [, condition2, result2
       , ...
       , conditionN, resultN ]
      [, default ] )

Description

CASE2 evaluates the conditions in the specified order.
If the comparison result is FALSE, it continues evaluating until TRUE is found.
If the comparison result is TRUE, it returns the corresponding result and stops further evaluation.
If all the comparison results are FALSE, it returns the default value. If no default is specified, it returns NULL.

If multiple types are used in the result, the result type is determined according to the Result Type Combination Rule.

CASE2 can be expressed using CASE as follows.

CASE2( condition1, res1, condition2, res2 )

CASE WHEN condition1 THEN res1
     WHEN condition2 THEN res2
     ELSE NULL
  END

CASE2( condition1, res1, condition2, res2, default )

CASE WHEN condition1 THEN res1
     WHEN condition2 THEN res2
     ELSE default
  END

Example

gSQL> SELECT I1,
        CASE2( I1 = 1, 'ONE', I1 = 2, 'TWO' ) AS CASE2_RESULT1,
        CASE2( I1 = 1, 'ONE', I1 = 2, 'TWO', 'NUMBER' ) AS CASE2_RESULT2
      FROM T1;
I1 CASE2_RESULT1 CASE2_RESULT2
-- ------------- -------------
 1 ONE           ONE          
 2 TWO           TWO          
 3 null          NUMBER       
3 rows selected.

CBRT

Syntax

CBRT( num )

Description

It returns the cube root of num.
If num is NULL, the result will also be NULL.

Example

gSQL> SELECT CBRT( 27 ) FROM DUAL;
CBRT( 27 )
----------
         3
1 row selected.

CEIL

Syntax

CEIL( num )
CEILING( num )

Description

CEIL returns the smallest integer that is greater than or equal to num.
If num is NULL, it returns NULL.

Example

gSQL> SELECT CEIL( 3.5 ) AS RESULT1, CEIL( -3.5 ) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
      4      -3
1 row selected.

CHAR_LENGTH

Syntax

CHAR_LENGTH( str )            
CHARACTER_LENGTH( str )

Description

CHAR_LENGTH returns the number of characters in str according to the character set.

The str can be a character type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING, or it can be a data type that can be converted to a character type. The return type is NATIVE_BIGINT.

If the data type of str is CHARACTER, trailing blanks are included in the calculation.
If str is NULL, it returns NULL.

It is an alias of LENGTH.

Example

Multi-byte character set: (e.g. UTF8)

gSQL> SELECT CHAR_LENGTH( 'αβ-SUMMER' ) AS RESULT FROM DUAL;
     RESULT   
     ---------------
              9
    1 row selected.

CHR

Syntax

CHR( num )

Description

It returns the character in the database character set code corresponding to num.

num is a numeric type.
If num is NULL, it returns NULL.

The return type is VARCHAR.

Example

gSQL> SELECT CHR(71) FROM DUAL;
CHR(71)
-------
G      
1 row selected.

CLOCK_DATE

Syntax

CLOCK_DATE()

Description

Whenever the CLOCK_DATE function is called, it returns the current date (DATE type).

The differences among the functions for obtaining the current date are as follows.

• TRANSACTION_DATE(): All date values within the transaction are the same.
• STATEMENT_DATE(): All date values within an SQL statement are the same.
• CLOCK_DATE(): Whenever the function is called, the current date value is returned.

Example

Each row can have a different date value.

gSQL> SELECT CLOCK_DATE() FROM t1;

CLOCK_DATE()
------------
2013-12-12  
2013-12-12  
2013-12-13  

3 rows selected.

CLOCK_LOCALTIME

Syntax

CLOCK_LOCALTIME()

Description

Whenever the CLOCK_LOCALTIME function is called, it returns the current time value without TIME ZONE (TIME WITHOUT TIME ZONE type).

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_LOCALTIME(): All time values within the transaction are the same.
• STATEMENT_LOCALTIME(): All time values within an SQL statement are the same.
• CLOCK_LOCALTIME(): Whenever the function is called, the current time value is returned.

Example

Each row can have a different time value.

gSQL> SELECT CLOCK_LOCALTIME() FROM t1;

CLOCK_LOCALTIME()
-----------------
14:42:05.470757  
14:42:05.470759  
14:42:05.470759  

3 rows selected.

CLOCK_LOCALTIMESTAMP

Syntax

CLOCK_LOCALTIMESTAMP()

Description

Whenever the CLOCK_LOCALTIMESTAMP() function is called, it returns the current TIMESTAMP value without TIME ZONE (TIMESTAMP WITHOUT TIME ZONE type).

The differences among the functions for obtaining the current TIMESTAMP are as follows.

• TRANSACTION_LOCALTIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• STATEMENT_LOCALTIMESTAMP(): All TIMESTAMP values within an SQL statement are the same.
• CLOCK_LOCALTIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

Each row can have a different timestamp value.

gSQL> SELECT CLOCK_LOCALTIMESTAMP() FROM t1;

CLOCK_LOCALTIMESTAMP()    
--------------------------
2013-12-12 14:46:17.309206
2013-12-12 14:46:17.309209
2013-12-12 14:46:17.309209

CLOCK_TIME

Syntax

CLOCK_TIME()

Description

Whenever the CLOCK_TIME() function is called, it returns the current time value with TIME ZONE (TIME WITH TIME ZONE type).

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_TIME(): All time values within the transaction are the same.
• STATEMENT_TIME(): All time values within an SQL statement are the same.
• CLOCK_TIME(): Whenever the function is called, the current time value is returned.

Example

Each row can have a different time value.

gSQL> SELECT CLOCK_TIME() FROM t1;

CLOCK_TIME()          
----------------------
14:48:21.052324 +09:00
14:48:21.052326 +09:00
14:48:21.052327 +09:00

3 rows selected.

CLOCK_TIMESTAMP

Syntax

CLOCK_TIMESTAMP()

Description

Whenever the CLOCK_TIMESTAMP() function is called, it returns the current TIMESTAMP value with TIME ZONE (TIMESTAMP WITH TIME ZONE type).

The differences among the functions for obtaining the current TIMESTAMP are as follows.

• TRANSACTION_TIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• STATEMENT_TIMESTAMP(): All TIMESTAMP values within an SQL statement are the same. 
• CLOCK_TIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

Each row can have a different timestamp value.

gSQL> SELECT CLOCK_TIMESTAMP() FROM t1;

CLOCK_TIMESTAMP()                
---------------------------------
2013-12-12 14:49:45.051709 +09:00
2013-12-12 14:49:45.051714 +09:00
2013-12-12 14:49:45.051714 +09:00

3 rows selected.

COALESCE

Syntax

COALESCE( expr1, ..., exprN )

Description

It returns the first non-null expr in the expr list.
If all expr in the expr list are null, it returns null.
There must be two or more exprs.

If multiple types are present in the expr list, the result type is determined by the Result Type Combination Rule.

COALESCE can be expressed using CASE as follows.

N/A
- COALESCE( expr1, expr2 )

CASE WHEN expr1 IS NOT NULL THEN expr1
       ELSE expr2
  END

N/A
- COALESCE( expr1, expr2, ..., exprN )

CASE WHEN expr1 IS NOT NULL THEN expr1
       ELSE COALESCE( expr2, ..., exprN )
  END

Example

gSQL> SELECT COALESCE( NULL, 1, 2 ) FROM DUAL;
COALESCE( NULL, 1, 2 )
----------------------
                     1
1 row selected.

gSQL> SELECT COALESCE( NULL, NULL, NULL ) FROM DUAL;
COALESCE( NULL, NULL, NULL )
----------------------------
null                        
1 row selected.

CONCAT

Syntax

CONCAT( str1, str2, ... )

Description

It is an alias of || ( CONCATENATE ).
It is an argument of the CONCAT function, and between 2 and 254 CONCATs can be specified.
For more information, refer to || (CONCATENATE) and CONCATENATE.

Example

gSQL> SELECT CONCAT( 'DATA', 'BASE' ) AS RESULT FROM DUAL;
RESULT  
--------
DATABASE
1 row selected.

CONCATENATE

Syntax

CONCATENATE( str1, str2, ... )

Description

It is an alias of || ( CONCATENATE ).
It is an argument of the CONCATENATE  function, and between  2  and 254 CONCATENATEs can be specified.
For more information, refer to CONCAT and || (CONCATENATE).

Example

gSQL> SELECT CONCATENATE( 'DATA', 'BASE' ) AS RESULT FROM DUAL;
RESULT  
--------
DATABASE
1 row selected.

CORR() OVER

Syntax

CORR( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function CORR calculates the coefficient of correlation for the pair of exprs.

If either expr1 or expr2 is NULL, it is excluded from the calculation.
If the number of rows for the pair of exprs is one or fewer, the result is NULL.

Example

gSQL> SELECT employee_id, TO_CHAR( hire_date, 'YYYY' ) AS hire_date, salary,
             CORR( TO_CHAR( hire_date, 'YYYY' ), salary ) OVER ( ORDER BY employee_id ) AS corr
      FROM employees
      WHERE department_id = 60;

EMPLOYEE_ID HIRE_DATE SALARY              CORR
----------- --------- ------ -----------------
        103 1990        9000              null
        104 1991        6000                -1
        105 1997        4800 -.805837379342809
        106 1998        4800 -.840210805972693
        107 1999        4200 -.875185734200534

5 rows selected.

COS

Syntax

COS(num)

Description

It returns the COSINE value of num.
If the num argument is NULL, the result will also be NULL.

Example

gSQL> SELECT COS( 0 ) FROM DUAL;
COS( 0 )
--------
       1
1 row selected.

COT

Syntax

COT(num)

Description

It returns the COTANGENT value of num.
If the num argument is NULL, the result will also be NULL.

Example

gSQL> SELECT COT( 1 ) FROM DUAL;
        COT( 1 )
----------------
.642092615934331
1 row selected.

COUNT

Syntax

COUNT( [ ALL | DISTINCT ] expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregate function. It returns the number of rows where expr is not NULL.

If ALL is explicitly specified, the aggregation is performed on all values.
If DISTINCT is explicitly specified, the aggregation is performed on the values excluding duplicates.
If neither ALL nor DISTINCT is explicitly specified, it is processed as if ALL were specified.

If FILTER is specified, the aggregation is performed only on the values that satisfy the condition.

Example

gSQL> SELECT COUNT( c1 ) FROM t1;

COUNT(C1)
---------
        3

1 row selected.


gSQL> SELECT COUNT( c1 ) FILTER( WHERE c1 > 1 ) FROM t1;

COUNT(C1)
---------
        2

1 row selected.

COUNT() OVER

Syntax

COUNT ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function COUNT counts the number of rows. 
NULL values are excluded from the calculation.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , COUNT( min_price ) OVER ( ORDER BY min_price ) AS "COUNT"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE COUNT
--------- -----
       73     1
      247     2
      731     3
     null     3
     null     3

5 rows selected.

COUNT(*)

Syntax

COUNT(*) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregate function that counts the number of rows. It doesn't consider whether the values are NULL or not, as no specific expression is explicitly provided.

If a FILTER is specified, the aggregation is performed only for values that satisfy the condition.

Example

gSQL> SELECT COUNT(*) FROM t1;

COUNT(*)
--------
       4

1 row selected.


gSQL> SELECT COUNT(*) FILTER( WHERE c1 > 1 ) FROM t1;

COUNT(*)
--------
       2

1 row selected.

COUNT(*) OVER

Syntax

COUNT(*) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function COUNT(*) counts the number of rows.
Since no expression is explicitly specified, it doesn't matter whether the value is NULL or not.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , COUNT(*) OVER ( ORDER BY min_price ) AS "COUNT(*)"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE COUNT(*)
--------- --------
       73        1
      247        2
      731        3
     null        5
     null        5

5 rows selected.

COVAR_POP() OVER

Syntax

COVAR_POP( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function COVAR_POP calculates the population covariance for a pair of exprs.

If either expr1 or expr2 is NULL, it is excluded from the calculation. 
If the number of rows for the pair of exprs is one or fewer, the result will be 0.

Example

gSQL> SELECT employee_id, TO_CHAR( hire_date, 'YYYY' ) AS hire_date, salary,
             COVAR_POP( TO_CHAR( hire_date, 'YYYY' ), salary ) OVER ( ORDER BY employee_id ) AS covar_pop
      FROM employees
      WHERE department_id = 60;

EMPLOYEE_ID HIRE_DATE SALARY COVAR_POP
----------- --------- ------ ---------
        103 1990        9000         0
        104 1991        6000      -750
        105 1997        4800     -4400
        106 1998        4800     -5100
        107 1999        4200     -5640

5 rows selected.

COVAR_SAMP() OVER

Syntax

COVAR_SAMP( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function COVAR_SAMP calculates the sample covariance for a pair of exprs.

If either expr1 or expr2 is NULL, it is excluded from the calculation. 
If the number of rows for the pair of exprs is one or fewer, the result will be NULL.

Example

gSQL> SELECT employee_id, TO_CHAR( hire_date, 'YYYY' ) AS hire_date, salary,
             COVAR_SAMP( TO_CHAR( hire_date, 'YYYY' ), salary ) OVER ( ORDER BY employee_id ) AS covar_samp
      FROM employees
      WHERE department_id = 60;

EMPLOYEE_ID HIRE_DATE SALARY COVAR_SAMP
----------- --------- ------ ----------
        103 1990        9000       null
        104 1991        6000      -1500
        105 1997        4800      -6600
        106 1998        4800      -6800
        107 1999        4200      -7050

5 rows selected.

CUME_DIST() OVER

Syntax

CUME_DIST( ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function CUME_DIST calculates the cumulative distribution based on the relative position of the current row's value.

The result of the CUME_DIST function is a number between 0 and 1.
If the values of the rows are the same, it returns the same result, which is the largest cumulative distribution value.

A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
             CUME_DIST() OVER ( ORDER BY salary ) AS cume_dist
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY CUME_DIST
------------- ------ ---------
           60   4200        .2
           60   4800        .6
           60   4800        .6
           60   6000        .8
           60   9000         1

5 rows selected.

CURRENT_CATALOG

Syntax

CURRENT_CATALOG [()]

Description

The catalog name (i.e., the database name) is obtained.

Example

gSQL> SELECT CURRENT_CATALOG FROM dual;

CURRENT_CATALOG
---------------
TEST_DB        

1 row selected.

CURRENT_DATE

Syntax

CURRENT_DATE [()]
STATEMENT_DATE()

Description

The current date (DATE type) is obtained.

CURRENT_DATE is an SQL standard function.

The differences among the functions for obtaining the current date are as follows.

• TRANSACTION_DATE(): All date values within the transaction are the same.
• CURRENT_DATE, STATEMENT_DATE(): All date values within an SQL statement are the same.
• CLOCK_DATE(): Whenever the function is called, the current date value is returned.

Example

gSQL> SELECT CURRENT_DATE FROM t1;

CURRENT_DATE
------------
2013-12-12  
2013-12-12  
2013-12-12  

3 rows selected.

CURRENT_ROLE

Syntax

CURRENT_ROLE [()]

Description

It returns the role of the current session.

Example

% gsql sys gliese

gSQL> SELECT current_user, current_role FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
SYS          null        

1 row selected.

gSQL> SET ROLE sysdba;

Session set.

gSQL> SELECT current_user, current_role FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
SYS          SYSDBA      

1 row selected.

gSQL> SET ROLE NONE;

Session set.


gSQL> SELECT current_user, current_role FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
SYS          null        

1 row selected.

CURRENT_SCHEMA

Syntax

CURRENT_SCHEMA [()]

Description

The user's current SCHEMA is obtained.

Example

gSQL> SELECT CURRENT_SCHEMA FROM dual;

CURRENT_SCHEMA
--------------
PUBLIC        

1 row selected.

CURRENT_TIME

Syntax

CURRENT_TIME [()]
STATEMENT_TIME()

Description

The current TIME WITH TIME ZONE type value, based on the session time, is obtained.

CURRENT_TIME is an SQL standard function.

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_TIME(): All time values within the transaction are the same.
• CURRENT_TIME, STATEMENT_TIME(): All time values within an SQL statement are the same.
• CLOCK_TIME(): Whenever the function is called, the current time value is returned.

Example

All rows contain the same value.

gSQL> SELECT CURRENT_TIME FROM t1;

CURRENT_TIME          
----------------------
16:27:10.116396 +09:00
16:27:10.116396 +09:00
16:27:10.116396 +09:00

3 rows selected.

CURRENT_TIMESTAMP

Syntax

CURRENT_TIMESTAMP [()]
STATEMENT_TIMESTAMP()

Description

The TIMESTAMP WITH TIME ZONE type value, based on the session time, is obtained.

CURRENT_TIMESTAMP is an SQL standard function.

The differences among the functions for obtaining the current TIMESTAMP are as follows.

• TRANSACTION_TIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• CURRENT_TIMESTAMP, STATEMENT_TIMESTAM(): All TIMESTAMP values within an SQL statement are the same.
• CLOCK_TIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

All rows contain the same value.

gSQL> SELECT CURRENT_TIMESTAMP FROM t1;

CURRENT_TIMESTAMP                
---------------------------------
2013-12-12 16:34:55.649632 +09:00
2013-12-12 16:34:55.649632 +09:00
2013-12-12 16:34:55.649632 +09:00

3 rows selected.

CURRENT_USER

Syntax

CURRENT_USER [()]

Description

It returns the current user.

User information is managed in three types, as follows.

Logon user: The user who performed the login, and their identity is maintained until the connection is closed.
Session user: It is the same as the initial logon user, but it can be changed using the SET SESSION AUTHORIZATION statement.
Current user: Generally the same as the session user, but it may be temporarily altered internally by the system for access control when using PSM, views, or similar features.
- The distinction between the session user and current user is similar to the difference between the real user and effective user in Unix systems.

Example

% gsql sys gliese

gSQL> SET SESSION AUTHORIZATION test;

Session set.

gSQL> SELECT 
        LOGON_USER() AS result1, 
        SESSION_USER() AS result2, 
        CURRENT_USER() AS result3 
      FROM DUAL;

RESULT1 RESULT2 RESULT3
------- ------- -------
SYS     TEST    TEST

1 row selected.

CURRVAL

Syntax

seq_name.CURRVAL
CURRVAL(seq_name)

Description

The current value of the sequence object is obtained.

A sequence value must be set using NEXTVAL(seq_name) at least once.

Example

gSQL> SELECT seq.CURRVAL FROM dual;

SEQ.CURRVAL
-----------
          1

1 row selected.

DATEADD

Syntax

DATEADD( datepart, number, date )

Description

It adds a specified number to the given datepart of a date and returns the result.

If the number has a decimal point, it is not rounded.
The supported date data types are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, and TIME WITH TIME ZONE.
If either the number or date is NULL, the result will also be NULL.

The result will have the same data type as the input date argument.

Available format string for datepart
datepart	Description
YEAR	Year
QUARTER	Quarter
MONTH	Month
DAYOFYEAR	Day of year
DAY	Day
WEEK	Week
WEEKDAY	Weekday
HOUR	Hour
MINUTE	Minute
SECOND	Second
MILLISECOND	Millisecond
MICROSECOND	Microsecond

Example

gSQL> SELECT 
      DATEADD( YEAR, 1, TO_DATE( '2013-05-14', 'YYYY-MM-DD' ) ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2014-05-14
1 row selected.

gSQL> SELECT 
      DATEADD( MONTH, 13, TO_DATE('2013-05-14', 'YYYY-MM-DD') ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2014-06-14
1 row selected.

gSQL> SELECT 
      DATEADD( DAY, 397, TO_DATE('2013-05-14', 'YYYY-MM-DD') ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2014-06-15
1 row selected.

DATEDIFF

Syntax

DATEDIFF( datepart, startdate, enddate )

Description

It substracts startdate from enddate, and returns the result in the specified datepart.

If either startdate or enddate is NULL, the result will also be NULL.
The data types of startdate and enddate can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIME.

The result type is a NUMBER.

Available format string for datepart
datepart	Description
YEAR	Year
QUARTER	Quarter
MONTH	Month
DAYOFYEAR	Day of year
DAY	Day
HOUR	Hour
MINUTE	Minute
SECOND	Second
MILLISECOND	Millisecond
MICROSECOND	Microsecond

Example

gSQL>  SELECT 
       DATEDIFF( YEAR, 
                 TO_DATE( '2013-05-14', 'YYYY-MM-DD' ), 
                 TO_DATE( '2014-06-15', 'YYYY-MM-DD' ) ) AS RESULT 
       FROM DUAL;
RESULT
------
     1
1 row selected.

gSQL> SELECT 
      DATEDIFF( MONTH, 
                TO_DATE( '2013-05-14', 'YYYY-MM-DD' ), 
                TO_DATE( '2014-06-15', 'YYYY-MM-DD' ) ) AS RESULT 
      FROM DUAL;
RESULT
------
    13
1 row selected.

gSQL> SELECT 
      DATEDIFF( DAY, 
                TO_DATE( '2013-05-14', 'YYYY-MM-DD' ), 
                TO_DATE( '2014-06-15', 'YYYY-MM-DD' ) ) AS RESULT 
      FROM DUAL;
RESULT
------
   397
1 row selected.

DATE_ADD

Syntax

DATE_ADD( date, INTERVAL expr unit  )

Description

It is the same function as ADDDATE (date, INTERVAL expr unit).

Example

gSQL> SELECT 
      DATE_ADD( TO_DATE( '2012-01-02', 'YYYY-MM-DD' ),
                INTERVAL '2-2' YEAR TO MONTH ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2014-03-02
1 row selected.

DATE_PART

Syntax

DATE_PART( field, datetime )

Description

The result of DATE_PART is the same as that of the EXTRACT function. It retrieves the specified field from the given datetime type and returns it.

The field argument must be a text literal, and valid values such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE can be specified as text literals.
The datetime argument can be of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, or INTERVAL data types.

If the field is not within the range of the datetime, an error will be returned. 
For the DATE type, the field must be YEAR, MONTH, or DAY; otherwise, an error will be returned.
If the datatime is NULL, the function will return NULL.

The return type is a NUMBER.

For more information, refer to EXTRACT.

Example

gSQL> SELECT 
      DATE_PART( 'DAY', TO_DATE( '2012-01-02', 'YYYY-MM-DD' ) ) AS RESULT
      FROM DUAL;
RESULT
------
     2
1 row selected.

gSQL> SELECT 
      DATE_PART( 'YEAR', INTERVAL'9-11'YEAR TO MONTH ) AS RESULT 
      FROM DUAL;
RESULT
------
     9
1 row selected.

DECODE

Syntax

DECODE( expr, comparison_expr1, result1
           [, comparison_expr2, result2
            , ...
            , comparison_exprN, resultN ]
           [, default ] )

Description

It evaluates expr and comparison_expr in the specified order in the DECODE statement using the equal operation.
If the comparison result is FALSE, the evaluation continues until a TRUE result is found.
When the comparison result is TRUE, it returns the corresponding value and stops further evaluation.

If expr and comparison_expr are equal, or if both expr and comparison_expr are NULL ( i.e., null = null ), the result is evaluated as TRUE, and the corresponding value is returned.
If all evaluated results are FALSE, it returns the default value. If the default is omitted, NULL is returned.

Comparison of expr and comparison_expr
All expr, comparison_expr1, ..., comparison_exprN are converted to the data type of comparison_expr1 (the first comparison_expr) for comparison.
If comparison_expr1 (the first comparison_expr) is a character type or a numeric type, it will be converted to a type that can include the ranges of all the types specified in expr, comparison_expr1, ..., comparison_exprN.
If all types specified in expr, comparison_expr1, ..., comparison_exprN are of the CHAR type, a VARCHAR type comparison is performed.

Result type
The result type is determined by the data type of result1 (the first result).
If the data type of result1 (the first result) is a character type or a numeric type, the result type becomes the type that includes the range of types specified in result1, ..., resultN.
If result1 (the first result) is CHAR or NULL, the result type will be VARCHAR.

DECODE can be expressed using CASE as follows.

DECODE( expr, comp_expr1, res1, comp_expr2, res2 )

CASE WHEN (expr = comp_expr1) OR (expr IS NULL AND comp_expr1 IS NULL ) THEN res1
     WHEN (expr = comp_expr2) OR (expr IS NULL AND comp_expr2 IS NULL ) THEN res2
     ELSE NULL
  END

DECODE( expr, comp_expr1, res1, comp_expr2, res2, default )

CASE WHEN (expr = comp_expr1) OR (expr IS NULL AND comp_expr1 IS NULL ) THEN res1
     WHEN (expr = comp_expr2) OR (expr IS NULL AND comp_expr2 IS NULL ) THEN res2
     ELSE default
  END

Example

gSQL> SELECT I1,
             DECODE( I1, 1, 'ONE', 
                         2, 'TWO', 
                         NULL, 'NULL VALUE', 
                         'DEFAULT VALUE' ) AS DECODE_RESULT
      FROM T1;
  I1 DECODE_RESULT
---- -------------
   1 ONE          
   2 TWO          
null NULL VALUE   
   3 DEFAULT VALUE
4 rows selected.

DEGREES

Syntax

DEGREES( radians )

Description

It returns the value of the angle radians converted from radians to degrees.
If radians is NULL, the result will also be NULL.

Example

gSQL> SELECT DEGREES( PI() ) AS RESULT FROM DUAL;
RESULT
------
   180
1 row selected.

DENSE_RANK() OVER

Syntax

DENSE_RANK( ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function DENSE_RANK calculates the ranking.

The ranking is a consecutive integer starting from 1, and rows with the same value have the same rank.
However, unlike RANK, when rows with the same value appear, the ranking is not skipped.

A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
             DENSE_RANK() OVER ( ORDER BY salary ) AS d_rank
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY D_RANK
------------- ------ ------
           60   4200      1
           60   4800      2
           60   4800      2
           60   6000      3
           60   9000      4

5 rows selected.

DIGEST

Syntax

DIGEST( data, type )

Description

It hashes the data to the specified type and returns the result as VARBINARY.

An implicit conversion may occur when inputting a data type based on the following rules.

• BINARY and VARBINARY type data are input as VARBINARY.
• LONG VARBINARY type data is input as LONG VARBINARY.
• LONG VARCHAR type data is input as LONG VARCHAR.
• All other data types are implicitly converted to VARCHAR before being input.

The DIGEST function supports the following hash types.

• The result of 'SHA1' is a 20-byte varbinary.
• The result of 'SHA224' is a 28-byte varbinary.
• The result of 'SHA256' is a 32-byte varbinary.
• The result of 'SHA384' is a 48-byte varbinary.
• The result of 'SHA512' is a 64-byte varbinary.

Since the result is returned in VARBINARY type, the HEX function must be used to view it as a hexadecimal string. In this case, the length will be twice the original.

Example

gSQL> SELECT HEX( DIGEST( 'my password', 'SHA256' ) ) AS RESULT FROM DUAL;

RESULT                                                          
----------------------------------------------------------------
BB14292D91C6D0920A5536BB41F3A50F66351B7B9D94C804DFCE8A96CA1051F2

1 row selected.

DUMP

Syntax

DUMP( expr )

Description

It returns the internal representation information of expr. 
This information includes the data type, byte length, and data content.

expr can be of any data type.
If expr is NULL, it returns NULL.

The return type is CHARACTER VARYING.

Example

gSQL> SELECT DUMP( 'DUMP' ) AS RESULT FROM DUAL;
RESULT                           
---------------------------------
Type=CHAR Len=4 : Str=68,85,77,80
1 row selected.

EXP

Syntax

EXP( num )

Description

It returns the value of e (the base of the natural logarithm) raised to the power of num.
If num is NULL, it returns NULL.

Example

gSQL> SELECT EXP( 1 ) AS RESULT FROM DUAL;
          RESULT
----------------
2.71828182845905
1 row selected.

EXTRACT

Syntax

EXTRACT( <field> FROM datetime )

<field> ::= 
        YEAR
      | MONTH
      | DAY
      | HOUR
      | MINUTE
      | SECOND
      | TIMEZONE_HOUR
      | TIMEZONE_MINUTE

Description

It searches for the specified field within an input datetime type and returns the result.

The datetime argument can be of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, or INTERVAL data types.

If the field is not within the range of the datetime, an error is returned. 
For the DATE type, the field must be YEAR, MONTH, or DAY; otherwise an error is returned.
The return type is NUMBER.

The result of EXTRACT is the same as the result of the DATE_PART function.

Example

gSQL> SELECT 
      EXTRACT( SECOND FROM TO_TIMESTAMP( '2012-12-13 01:23:44.5', 
                                         'YYYY-MM-DD HH24:MI:SS.FF1' )  ) 
              AS RESULT 
      FROM DUAL;
RESULT
------
  44.5
1 row selected.

gSQL> SELECT
      EXTRACT( YEAR FROM CAST('2-3' AS INTERVAL YEAR TO MONTH) ) AS RESULT
      FROM DUAL;
RESULT
------
     2
1 row selected.

FACTORIAL

Syntax

FACTORIAL( num )

Description

It returns the result of successively multiplying the natural numbers from 1 to num.
If num is NULL, it returns NULL.

Example

gSQL> SELECT FACTORIAL( 5 ) AS RESULT FROM DUAL;
RESULT
------
   120
1 row selected.

FIRST() OVER

Syntax

aggregation_function KEEP ( DENSE_RANK FIRST ORDER BY <sort specification list> ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function FIRST sorts the sort specification list written in the order by within the KEEP clause, and then returns the aggregation function value of the rows with a DENSE_RANK of 1.

aggregation_functions includes AVG, COUNT, COUNT(*), SUM, MAX, MIN, STDDEV, and VARIANCE.

order by is not allowed within the window clause.
A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
           DENSE_RANK() OVER ( ORDER BY department_id ) AS "DENSE_RANK",
           MAX( salary ) KEEP ( DENSE_RANK FIRST ORDER BY department_id ) OVER () AS "MAX_FIRST"
  FROM employees
 WHERE department_id BETWEEN 90 AND 100;
    
DEPARTMENT_ID SALARY DENSE_RANK MAX_FIRST
------------- ------ ---------- ---------
           90  24000          1     24000
           90  17000          1     24000
           90  17000          1     24000
          100  12000          2     24000
          100   9000          2     24000
          100   8200          2     24000
          100   7700          2     24000
          100   7800          2     24000
          100   6900          2     24000

9 rows selected.

FIRST_VALUE() OVER

Syntax

FIRST_VALUE ( expr ) [ RESPECT NULLS | IGNORE NULLS ] OVER < window name or specification >

FIRST_VALUE ( expr [ RESPECT NULLS | IGNORE NULLS ] ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function FIRST_VALUE returns the first value of expr.

RESPECT NULLS returns the first value of rows, including NULL.
IGNORE NULLS returns the first value of row, excluding NULL.
If not specified, the default is RESPECT NULLS.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , FIRST_VALUE( min_price ) OVER ( ORDER BY min_price NULLS FIRST ) AS "FIRST_VALUE"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE FIRST_VALUE
--------- -----------
     null        null
     null        null
       73        null
      247        null
      731        null

5 rows selected.

The following is an example of specifying IGNORE NULLS in null_treatment.

gSQL> SELECT min_price AS "MIN_PRICE"
             , FIRST_VALUE( min_price IGNORE NULLS ) OVER ( ORDER BY min_price NULLS FIRST )
                                                       AS "FIRST_VALUE"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE FIRST_VALUE
--------- -----------
     null        null
     null        null
       73          73
      247          73
      731          73

5 rows selected.

FLOOR

Syntax

FLOOR( num )

Description

It returns the largest integer that is less than or equal to num.
If num is NULL, it returns NULL.

Example

gSQL> SELECT FLOOR(42.8) AS RESULT1, FLOOR(-42.8) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
     42     -43
1 row selected.

FROM_BASE64

Syntax

FROM_BASE64( str )

Description

FROM_BASE64 takes a character encoded in base64 as input and returns the decoded binary string.

The input argument data type can be a character type, such as CHARACTER VARYING or CHARACTER LONG VARYING, and the result type is a binary character type, such as BINARY VARYING or BINARY LONG VARYING.

If str is NULL, the result will also be NULL.
If str contains characters that are not within the base64 character range, an error will be returned.
Newlines, carriage returns, tabs, and spaces in str are ignored during decoding.

For more information, refer to TO_BASE64.

Example

gSQL> SELECT FROM_BASE64( TO_BASE64( 'abc' ) ),
             FROM_BASE64( TO_BASE64( 'abcd' ) ) 
        FROM DUAL;
FROM_BASE64( TO_BASE64( 'abc' ) ) FROM_BASE64( TO_BASE64( 'abcd' ) )
--------------------------------- ----------------------------------
616263                            61626364                          
1 row selected.

FROM_TZ

Syntax

FROM_TZ( timestamp, timezone )

Description

The FROM_TZ function converts the timestamp and the timezone in the specified format to the TIMESTAMP WITH TIME ZONE type and returns it.

The timestamp argument must be of TIMESTAMP type or a type convertible to TIMESTAMP. 
If the timestamp argument is NULL, the result will also be NULL.

The timezone argument must be of CHARACTER type, such as CHARACTER or CHARACTER VARYING, and the format must be 'TZH:TZM'. 
If the timezone argument is NULL, the result will also be NULL.

The result type is TIMESTAMP(6) WITH TIME ZONE.

Example

gSQL> SELECT 
      FROM_TZ( TIMESTAMP'2021-01-01 10:10:20.000000', '+06:00' ) AS RESULT
        FROM DUAL;
RESULT
-----------------------------------
2021-01-01 10:10:20.000000 +06:00
1 row selected.

GREATEST

Syntax

GREATEST( expr1 [, expr2, ... exprn ] )

Description

It returns the largest value among the expr arguments provided.

If any expr argument is NULL, the result will be NULL.

The result type is the data type of expr1 (the first expr).
If the data type of expr1 is numeric or character, the result type is determined to be a type that can include the range of expr1, ..., exprN.
If all of expr1, ..., exprN are defined as CHAR type, then all exprs are compared as VARCHAR type, and the result type is determined to be VARCHAR.

Example

gSQL> SELECT GREATEST( 100, 0, 200, 150, 1 ) AS RESULT FROM DUAL;
RESULT
------
   200
1 row selected.

GROUPING

Syntax

GROUPING( expr [, expr]... )

Description

The GROUPING function should be used together with GROUP BY.
expr is an argument where each expr represents a single bit, and it returns the corresponding number. If it is used as a GROUPING KEY, the value is 0; otherwise, it is 1.
The result datatype is NATIVE_INTEGER. The maximum number of bits that can be represented as a positive integer is 31, so the maximum number of GROUPING() arguments is 31.

The GROUPING function works as follows when used together with GROUP BY ROLLUP(a,b).

Grouping Set	Bit vector	GROUPING
a, b	0 0	0
a	0 1	1
null	1 1	3

The GROUPING function works as follows when used together with GROUP BY CUBE(a,b).

Grouping Set	Bit vector	GROUPING
a,b	0 0	0
a	0 1	1
b	1 0	2
null	1 1	3

Example

\EXPLAIN PLAN
SELECT 
       calendar_year as year 
     , calendar_quarter_desc as quarter
     , calendar_month_desc as month
     , GROUPING( calendar_year, calendar_quarter_desc, calendar_month_desc ) as grouping_id_func
  FROM sales, times
 WHERE sales.time_id=times.time_id 
   AND times.calendar_year = 2001
   AND sales.cust_id < 1000 AND sales.prod_id > 142 AND sales.channel_id > 2
 GROUP BY ROLLUP(calendar_year, calendar_quarter_desc, calendar_month_desc)
 ORDER BY 1, 2, 3;    2     3     4     5     6     7     8     9    10    11    12 

YEAR QUARTER MONTH   GROUPING_ID_FUNC
---- ------- ------- ----------------
2001 2001-01 2001-01                0
2001 2001-01 2001-02                0
2001 2001-01 2001-03                0
2001 2001-01 null                   1
2001 2001-02 2001-04                0
2001 2001-02 2001-05                0
2001 2001-02 2001-06                0
2001 2001-02 null                   1
2001 2001-03 2001-07                0
2001 2001-03 2001-08                0
2001 2001-03 2001-09                0
2001 2001-03 null                   1
2001 2001-04 2001-10                0
2001 2001-04 2001-11                0
2001 2001-04 2001-12                0
2001 2001-04 null                   1
2001 null    null                   3
null null    null                   7

18 rows selected.

GROUPING_ID

Syntax

GROUPING_ID( expr [, expr]... )

Description

It is the same as the GROUPING function. In other words, it is an alias for GROUPING.

Example

\EXPLAIN PLAN
SELECT 
       calendar_year as year 
     , calendar_quarter_desc as quarter
     , calendar_month_desc as month
     , GROUPING_ID( calendar_year, calendar_quarter_desc, calendar_month_desc ) as grouping_id_func
  FROM sales, times
 WHERE sales.time_id=times.time_id 
   AND times.calendar_year = 2001
   AND sales.cust_id < 1000 AND sales.prod_id > 142 AND sales.channel_id > 2
 GROUP BY ROLLUP(calendar_year, calendar_quarter_desc, calendar_month_desc)
 ORDER BY 1, 2, 3;  

YEAR QUARTER MONTH   GROUPING_ID_FUNC
---- ------- ------- ----------------
2001 2001-01 2001-01                0
2001 2001-01 2001-02                0
2001 2001-01 2001-03                0
2001 2001-01 null                   1
2001 2001-02 2001-04                0
2001 2001-02 2001-05                0
2001 2001-02 2001-06                0
2001 2001-02 null                   1
2001 2001-03 2001-07                0
2001 2001-03 2001-08                0
2001 2001-03 2001-09                0
2001 2001-03 null                   1
2001 2001-04 2001-10                0
2001 2001-04 2001-11                0
2001 2001-04 2001-12                0
2001 2001-04 null                   1
2001 null    null                   3
null null    null                   7

18 rows selected.

GSI_PHYSICAL_STATS

Syntax

GSI_PHYSICAL_STATS( [schema_name.]table_name [,sampling_ratio_value] )

Description

GSI_PHYSICAL_STATS is a function that returns page fragmentation information for the global secondary index of a table in a cluster environment.

The input parameter table_name must be specified as an identifier, and an error is raised if the corresponding object is not a base table.

The input parameter sampling_ratio_value represents the percentage (%) of the total pages owned by the object that will be accessed for analysis.
By randomly sampling and analyzing only a subset of pages instead of processing all pages, the operation can be performed more quickly and efficiently.
The Used and Fragmented values in the result represent the sizes analyzed based on the sampled pages, not the total allocated pages.
If this parameter is omitted, a default value of 100% is applied, and all pages are analyzed.
The valid range of this value is 1 to 100. An error is returned if the value is outside this range.

The result type is VARCHAR and includes the fields Page, Used, and Fragmented.
• Page: The total number of pages allocated to the global secondary index.
• Used: The size of the used space, which is the sum of the page header size and the size of the stored data. The unit is bytes.
• Fragmented: The size of the fragmented space, in bytes.

This function provides valid information only in a cluster system.

In addition, GLOBAL_DUAL can be used to retrieve this information from all nodes in the cluster.

Example

When using DUAL
- Returns the object information of the connected node.
- In a cluster environment, a cluster domain can be specified.

gSQL> SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(T1) FROM DUAL;
CLUSTER_MEMBER_NAME GSI_PHYSICAL_STATS(T1)                      
------------------- --------------------------------------------
G1N1                Page: 384, Used: 1703308, Fragmented: 766659
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(PUBLIC.T1) FROM DUAL;
CLUSTER_MEMBER_NAME GSI_PHYSICAL_STATS(PUBLIC.T1)               
------------------- --------------------------------------------
G1N1                Page: 384, Used: 1703308, Fragmented: 766659
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(PUBLIC.T1, 50) FROM DUAL;
CLUSTER_MEMBER_NAME GSI_PHYSICAL_STATS(PUBLIC.T1, 50)          
------------------- -------------------------------------------
G1N1                Page: 384, Used: 821311, Fragmented: 368207
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(T1) FROM DUAL@G1N1
      UNION ALL
      SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(T1) FROM DUAL@G2N1
      UNION ALL
      SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(T1) FROM DUAL@G3N1;
CLUSTER_MEMBER_NAME GSI_PHYSICAL_STATS(T1)                         
------------------- -----------------------------------------------
G1N1                Page: 384, Used: 1703308, Fragmented: 766659   
G2N1                Page: 1088, Used: 5115758, Fragmented: 2300000 
G3N1                Page: 2080, Used: 10226411, Fragmented: 4600000
3 rows selected.

When using GLOBAL_DUAL
- Returns the object information from all nodes in the cluster environment.
- A cluster domain can be specified.

gSQL> SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(PUBLIC.T1, 50)
        FROM GLOBAL_DUAL
      ORDER BY CLUSTER_MEMBER_NAME;
CLUSTER_MEMBER_NAME GSI_PHYSICAL_STATS(PUBLIC.T1, 50)             
------------------- ----------------------------------------------
G1N1                Page: 384, Used: 887714, Fragmented: 398199   
G1N2                Page: 384, Used: 775345, Fragmented: 347438   
G2N1                Page: 1088, Used: 2603073, Fragmented: 1175875
G2N2                Page: 1088, Used: 2669476, Fragmented: 1205867
G3N1                Page: 2080, Used: 5021814, Fragmented: 2265086
G3N2                Page: 2080, Used: 5129068, Fragmented: 2313547
6 rows selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, GSI_PHYSICAL_STATS(PUBLIC.T1, 50)
        FROM GLOBAL_DUAL@G1N1|G2N1|G3N1
      ORDER BY CLUSTER_MEMBER_NAME;
CLUSTER_MEMBER_NAME GSI_PHYSICAL_STATS(PUBLIC.T1, 50)             
------------------- ----------------------------------------------
G1N1                Page: 384, Used: 851955, Fragmented: 382053   
G2N1                Page: 1088, Used: 2700097, Fragmented: 1219736
G3N1                Page: 2080, Used: 5384404, Fragmented: 2428961
3 rows selected.

HASH32

Syntax

HASH32( expr [, expr]... )

Description

The HASH32 function calculates and returns the hash value of the provided expr arguments.

At least one argument must be specified, and up to a maximum of 32 arguments can be provided.
If any of the input arguments is NULL, the result will be NULL.

The return type is NATIVE_INTEGER.

Example

CREATE TABLE t1 ( c_int INTEGER, c_vchar VARCHAR(10), c_date DATE );
INSERT INTO t1 VALUES ( 100, 'GOLDILOCKS', sysdate );
INSERT INTO t1 VALUES ( 200, null, sysdate );

gSQL> SELECT * FROM t1;

C_INT C_VCHAR    C_DATE    
----- ---------- ----------
  100 GOLDILOCKS 2026-02-26
  200 null       2026-02-26

2 rows selected.

gSQL> SELECT HASH32( c_int, c_vchar, c_date ) FROM t1;

HASH32( C_INT, C_VCHAR, C_DATE )
--------------------------------
                      1116649224
                            null

2 rows selected.

HEX

Syntax

HEX( str )

Description

It returns the str argument as a hexadecimal character.
The str argument can be a character type such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING, a type that can be converted to a character type, or a binary character type such as BINARY, BINARY VARYING, or BINARY LONG VARYING.
The result type is a character type, such as CHARACTER VARYING or CHARACTER LONG VARYING.

If str is NULL, the result will also be NULL.

If the argument of the HEX function is a numeric type, it returns an error. 
To convert a decimal number to a hexadecimal number, use the TO_CHAR() function with the 'X' number format.
e.g. TO_CHAR( 255, 'XX' )

For more information, refer to UNHEX.

Example

gSQL> SELECT HEX( 'abc' ) FROM DUAL;
HEX( 'abc' )
------------
616263      
1 row selected.

INDEX_PHYSICAL_STATS

Syntax

INDEX_PHYSICAL_STATS( [schema_name.]index_name [,sampling_ratio_value] )

Description

INDEX_PHYSICAL_STATS is a function that returns fragmentation information for the pages allocated to an index.

The input parameter index_name must be specified as an identifier, and an error is raised if the corresponding object is not an index.

The input parameter sampling_ratio_value represents the percentage (%) of the total pages owned by the object that will be accessed for analysis. 
By randomly sampling and analyzing only a subset of pages instead of processing all pages, the operation can be performed more quickly and efficiently. 
The Used and Fragmented values in the result represent the sizes analyzed based on the sampled pages, not the total allocated pages. 
If this parameter is omitted, a default value of 100% is applied, and all pages are analyzed.
The valid range of this value is 1 to 100. An error is returned if the value is outside this range.

The result type is VARCHAR and includes the fields Page, Used, and Fragmented.
• Page: The total number of pages allocated to the index.
• Used: The size of the used space, which is the sum of the page header size and the size of the stored data. The unit is bytes.
• Fragmented: The size of the fragmented space, in bytes.

GLOBAL_DUAL can be used to retrieve this information from all nodes in the cluster.

Example

When using DUAL
- Returns the object information of the connected node.
- In a cluster environment, a cluster domain can be specified.

gSQL> SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(T1_UIDX1) FROM DUAL;
CLUSTER_MEMBER_NAME INDEX_PHYSICAL_STATS(T1_UIDX1)              
------------------- --------------------------------------------
G1N1                Page: 480, Used: 2026050, Fragmented: 892587
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1) FROM DUAL;
CLUSTER_MEMBER_NAME INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1)       
------------------- --------------------------------------------
G1N1                Page: 480, Used: 2026050, Fragmented: 892587
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1, 50) FROM DUAL;
CLUSTER_MEMBER_NAME INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1, 50)   
------------------- --------------------------------------------
G1N1                Page: 480, Used: 1136487, Fragmented: 506293
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(T1_UIDX1) FROM DUAL@G1N1
      UNION ALL
      SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(T1_UIDX1) FROM DUAL@G2N1
      UNION ALL
      SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(T1_UIDX1) FROM DUAL@G3N1;
CLUSTER_MEMBER_NAME INDEX_PHYSICAL_STATS(T1_UIDX1)                 
------------------- -----------------------------------------------
G1N1                Page: 480, Used: 2026050, Fragmented: 892587   
G2N1                Page: 1280, Used: 6097450, Fragmented: 2697980 
G3N1                Page: 2464, Used: 12182935, Fragmented: 5395960
3 rows selected.

When using GLOBAL_DUAL
- Returns the object information from all nodes in the cluster environment.
- A cluster domain can be specified.

gSQL> SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1, 50)
        FROM GLOBAL_DUAL
      ORDER BY CLUSTER_MEMBER_NAME;
CLUSTER_MEMBER_NAME INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1, 50)     
------------------- ----------------------------------------------
G1N1                Page: 480, Used: 1146717, Fragmented: 510879  
G1N2                Page: 480, Used: 1115998, Fragmented: 497144  
G2N1                Page: 1280, Used: 3110836, Fragmented: 1371868
G2N2                Page: 1280, Used: 3142877, Fragmented: 1390133
G3N1                Page: 2464, Used: 6072430, Fragmented: 2687620
G3N2                Page: 2464, Used: 6177784, Fragmented: 2737856
6 rows selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1, 50)
        FROM GLOBAL_DUAL@G1N1|G2N1|G3N1
      ORDER BY CLUSTER_MEMBER_NAME;
CLUSTER_MEMBER_NAME INDEX_PHYSICAL_STATS(PUBLIC.T1_UIDX1, 50)     
------------------- ----------------------------------------------
G1N1                Page: 480, Used: 1213355, Fragmented: 540555  
G2N1                Page: 1280, Used: 3102215, Fragmented: 1369573
G3N1                Page: 2464, Used: 6347994, Fragmented: 2810966
3 rows selected.

INITCAP

Syntax

INITCAP( str )

Description

It converts the first letter of each word in the string str to uppercase and all other letters to lowercase, then returns the result.

The str data type can be a character type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

Each word in the string is separated by whitespace or characters that are not alphanumeric.
If str is NULL, the result will also be NULL.

The return type is the same as the str argument's datatype.

Example

gSQL> SELECT INITCAP( 'hi GLIESE' ) AS RESULT FROM DUAL;
RESULT   
---------
Hi Gliese
1 row selected.

INSTR

Syntax

INSTR( str, substr [, position [, occurrence ] ] )

Description

It search for the occurrence-th substr starting from the position in str and returns its location.

The data types of the str and substr arguments can be character types such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or binary character types such as BINARY, BINARY VARYING, or BINARY LONG VARYING.

The position and occurrence arguments can be of a numeric data type.

If position and occurrence are omitted, the default value is 1.
Both position and occurrence start from 1, and they are calculated in character units according to character set (not in byte units).

The position represents the first position to search substr in str, it must be a positive integer and cannot be zero.

If the position is positive: It searches for the position of substr by comparing from the beginning of str to the right until it finds the substring.
If the position is negative: It searches for the position of substr by comparing from the end of str to the left until it finds the substring.
If the position is 0: The result is 0.

The occurrence refers to the number of times the substr repeats in str, and it must be a positive integer.

If any of the input arguments is NULL, the result will also be NULL.

Example

gSQL> SELECT INSTR( 'ABCD ABCD ABCDABCD', 'BC' ) AS RESULT1,
             INSTR( 'ABCD ABCD ABCDABCD', 'BC', 4 ) AS RESULT2
      FROM DUAL;
RESULT1 RESULT2
------- -------
      2       7
1 row selected.

gSQL> SELECT INSTR( 'ABCD ABCD ABCDABCD', 'BC', 5 , 3 ) AS RESULT1,
             INSTR( 'ABCD ABCD ABCDABCD', 'BC', -5,  3 ) AS RESULT2
      FROM DUAL;
RESULT1 RESULT2
------- -------
     16       2
1 row selected.

JSON_ARRAY

Syntax

JSON_ARRAY( [ value_expression [, ...] ]
            [<JSON constructor null clause>]
            [<JSON output clause>]
          )

For more information about the <JSON constructor null clause>, refer to the JSON Constructor Null Clause section.

For more information about the <JSON output clause>, refer to the JSON Output Clause section.

Description

JSON_ARRAY returns zero or more expressions as a JSON array string.

The JSON constructor null clause is an option that specifies how to handle SQL null values.
If not specified, the default is ABSENT ON NULL.

The JSON output clause is an option that allows control over the data type and output format of the string generated by the function.
The result type can be specified by declaring a data type. If not specified, the default is VARCHAR(4000).
The output format of the JSON string can be changed by specifying the PRETTY option.

Example

CREATE TABLE accounts ( name VARCHAR(20), balances INTEGER );
INSERT INTO accounts VALUES ( 'Alice', 50000 );
INSERT INTO accounts VALUES ( 'Bob', NULL );
INSERT INTO accounts VALUES ( 'Chris', 1000 );

gSQL> SELECT JSON_ARRAY( name, balances ) AS res_json_array
        FROM accounts;

RES_JSON_ARRAY 
---------------
["Alice",50000]
["Bob"]        
["Chris",1000] 

3 rows selected.

JSON_ARRAYAGG

Syntax

JSON_ARRAYAGG( value_expression
               [<JSON array aggregate order by clause>]
               [<JSON constructor null clause>]
               [<JSON output clause>]
             ) [ FILTER ( [ WHERE ] condition )

For more information about the <JSON array aggregate order by clause> refer to the JSON Array Aggregate Order By Clause section.

For more information about the <JSON constructor null clause>, refer to the JSON Constructor Null Clause section.

For more information about the <JSON output clause>, refer to the JSON Output Clause section.

If a FILTER is specified, aggregation is performed only on values that satisfy the specified condition.

Description

JSON_ARRAYAGG is an aggregation function that concatenates value expressions and returns a single JSON array string row.

The JSON array aggregate order by clause is an option that sorts JSON array values for output.

The JSON constructor null clause is an option that specifies how to handle SQL null values. 
If not specified, the default is ABSENT ON NULL.

The JSON output clause is an option that allows control over the data type and output format of the string generated by the function.
The result type can be specified by declaring a data type. If not specified, the default is VARCHAR(4000).
The output format of the JSON string can be changed by specifying the PRETTY option.

Example

CREATE TABLE accounts ( name VARCHAR(20), balances INTEGER );
INSERT INTO accounts VALUES ( 'Alice', 50000 );
INSERT INTO accounts VALUES ( 'Bob', NULL );
INSERT INTO accounts VALUES ( 'Chris', 1000 );

gSQL> SELECT JSON_ARRAYAGG( name ) AS name_arrayagg,
             JSON_ARRAYAGG( balances ) AS balances_arrayagg
        FROM accounts; 

NAME_ARRAYAGG           BALANCES_ARRAYAGG
----------------------- -----------------
["Alice","Bob","Chris"] [50000,1000]     

1 row selected.

gSQL> SELECT JSON_ARRAYAGG( name ) 
             FILTER ( balances > 10000 ) AS name_arrayagg,
             JSON_ARRAYAGG( balances ) 
             FILTER ( balances > 10000 ) AS balances_arrayagg
        FROM accounts; 

NAME_ARRAYAGG BALANCES_ARRAYAGG
------------- -----------------
["Alice"]     [50000]          

1 row selected.

JSON_ARRAYAGG() OVER

Syntax

JSON_ARRAYAGG( value_expression
               [<JSON array aggregate order by clause>]
               [<JSON constructor null clause>]
               [<JSON output clause>]
             ) OVER < window name or specification >

For more information about the <JSON array aggregate order by clause> refer to the JSON Array Aggregate Order By Clause section.

For more information about the <JSON constructor null clause>, refer to the JSON Constructor Null Clause section.

For more information about the <JSON output clause>, refer to the JSON Output Clause section.

For more information about the <window name or specification>, refer to the window clause section.

Description

JSON_ARRAYAGG is a window function that concatenates value expressions within the window frame to generate a JSON array string.

The JSON array aggregate order by clause is an option that sorts JSON array values for output.

The JSON constructor null clause is an option that specifies how to handle SQL null values. 
If not specified, the default is ABSENT ON NULL.

The JSON output clause is an option that allows control over the data type and output format of the string generated by the function.
The result type can be specified by declaring a data type. If not specified, the default is VARCHAR(4000). 
The output format of the JSON string can be changed by specifying the PRETTY option.

Example

CREATE TABLE accounts ( name VARCHAR(20), balances INTEGER );
INSERT INTO accounts VALUES ( 'Alice', 50000 );
INSERT INTO accounts VALUES ( 'Bob', NULL );
INSERT INTO accounts VALUES ( 'Chris', 1000 );

gSQL> SELECT JSON_ARRAYAGG( name ) OVER ( ORDER BY balances DESC NULLS LAST ) AS name_arrayagg_over,
             JSON_ARRAYAGG( balances ) OVER ( ORDER BY balances DESC NULLS LAST ) AS balances_arrayagg_over
        FROM accounts;

NAME_ARRAYAGG_OVER      BALANCES_ARRAYAGG_OVER
----------------------- ----------------------
["Alice"]               [50000]               
["Alice","Chris"]       [50000,1000]          
["Alice","Chris","Bob"] [50000,1000]          

3 rows selected.

JSON_OBJECT

Syntax

JSON_OBJECT( [ <JSON name and value> [, ...] ]
             [ <JSON constuctor null clause> ]
             [ <JSON key uniqueness constraint> ]
             [ <JSON output clause> ]
           )

<JSON name and value> ::=
    [KEY] <JSON name> VALUE <value_expression>
  | <JSON name> : <value_expression>

For more information about the <JSON constructor null clause>, refer to the JSON Constructor Null Clause section.

For more information about the < JSON key uniqueness constraint >, refer to the JSON Key Uniqueness Constraint section.

For more information about the <JSON output clause>, refer to the JSON Output Clause section.

Description

JSON_OBJECT returns zero or more JSON name and values as a JSON object string.
A JSON name must be an expression that can be represented as a character string.

The JSON constructor null clause is an option that specifies how to handle SQL null values. 
If not specified, the default is NULL ON NULL.

The JSON key uniqueness constraint is an option that determines whether duplicate keys are allowed in a JSON object. 
If not specified, the default is WITHOUT UNIQUE KEYS.

The JSON output clause is an option that allows control over the data type and output format of the string generated by the function.
The result type can be specified by declaring a data type. If not specified, the default is VARCHAR(4000). 
The output format of the JSON string can be changed by specifying the PRETTY option.

Example

CREATE TABLE accounts ( name VARCHAR(20), balances INTEGER );
INSERT INTO accounts VALUES ( 'Alice', 50000 );
INSERT INTO accounts VALUES ( 'Bob', NULL );
INSERT INTO accounts VALUES ( 'Chris', 1000 );

gSQL> SELECT JSON_OBJECT( name VALUE balances ) AS res_json_object
       FROM accounts;

RES_JSON_OBJECT
---------------
{"Alice":50000}
{"Bob":null}   
{"Chris":1000} 

3 rows selected.

JSON_OBJECTAGG

Syntax

JSON_OBJECTAGG( <JSON name and value>
                [ <JSON constructor null clause> ]
                [ <JSON key uniqueness constraint> ]
                [ <JSON output clause> ]
              ) [ FILTER ( [ WHERE ] condition ) ]

<JSON name and value> ::=
    [KEY] <JSON name> VALUE <value_expression>
  | <JSON name> : <value_expression>

For more information about the <JSON constructor null clause>, refer to the JSON Constructor Null Clause section.

For more information about the < JSON key uniqueness constraint >, refer to the JSON Key Uniqueness Constraint section.

For more information about the <JSON output clause>, refer to the JSON Output Clause section.

If a FILTER is specified, aggregation is performed only on values that satisfy the specified condition.

Description

JSON_OBJECTAGG is an aggregation function that concatenates JSON name-value pairs and returns a single JSON object string row.
A JSON name must be an expression that can be represented as a character string.

The JSON constructor null clause is an option that specifies how to handle SQL null values. 
If not specified, the default is NULL ON NULL.

The JSON key uniqueness constraint is an option that determines whether duplicate keys are allowed in a JSON object. 
If not specified, the default is WITHOUT UNIQUE KEYS.

The JSON output clause is an option that allows control over the data type and output format of the string generated by the function.
The result type can be specified by declaring a data type. If not specified, the default is VARCHAR(4000). 
The output format of the JSON string can be changed by specifying the PRETTY option.

Example

CREATE TABLE accounts ( name VARCHAR(20), balances INTEGER );
INSERT INTO accounts VALUES ( 'Alice', 50000 );
INSERT INTO accounts VALUES ( 'Bob', NULL );
INSERT INTO accounts VALUES ( 'Chris', 1000 );

gSQL> SELECT JSON_OBJECTAGG( name VALUE balances ) AS res_json_objectagg
        FROM accounts;

RES_JSON_OBJECTAGG                     
---------------------------------------
{"Alice":50000,"Bob":null,"Chris":1000}

1 row selected.

gSQL> SELECT JSON_OBJECTAGG( name VALUE balances ) 
                    FILTER ( balances > 10000 ) AS res_json_objectagg
       FROM accounts;   

RES_JSON_OBJECTAGG
------------------
{"Alice":50000}   

1 row selected.

JSON_OBJECTAGG() OVER

Syntax

JSON_OBJECTAGG( <JSON name and value>
                [ <JSON constructor null clause> ]
                [ <JSON key uniqueness constraint> ]
                [ <JSON output clause> ]
              ) OVER < window name or specification >

<JSON name and value> ::=
    [KEY] <JSON name> VALUE <value_expression>
  | <JSON name> : <value_expression>

For more information about the <JSON constructor null clause>, refer to the JSON Constructor Null Clause section.

For more information about the < JSON key uniqueness constraint >, refer to the JSON Key Uniqueness Constraint section.

For more information about the <JSON output clause>, refer to the JSON Output Clause section.

For more information about the <window name or specification>, refer to the window clause section.

Description

JSON_OBJECTAGG is a window function that concatenates JSON name-value pairs within the window frame to generate a JSON object string.
A JSON name must be an expression that can be represented as a character string.

The JSON constructor null clause is an option that specifies how to handle SQL null values. 
If not specified, the default is NULL ON NULL.

The JSON key uniqueness constraint is an option that determines whether duplicate keys are allowed in a JSON object. 
If not specified, the default is WITHOUT UNIQUE KEYS.

The JSON output clause is an option that allows control over the data type and output format of the string generated by the function.
The result type can be specified by declaring a data type. If not specified, the default is VARCHAR(4000). 
The output format of the JSON string can be changed by specifying the PRETTY option.

Example

CREATE TABLE accounts ( name VARCHAR(20), balances INTEGER );
INSERT INTO accounts VALUES ( 'Alice', 50000 );
INSERT INTO accounts VALUES ( 'Bob', NULL );
INSERT INTO accounts VALUES ( 'Chris', 1000 );

gSQL> SELECT JSON_OBJECTAGG( name VALUE balances ) OVER ( ORDER BY balances DESC NULLS LAST ) AS res_json_objectagg_over
        FROM accounts;
RES_JSON_OBJECTAGG_OVER                
---------------------------------------
{"Alice":50000}                        
{"Alice":50000,"Chris":1000}           
{"Alice":50000,"Chris":1000,"Bob":null}

3 rows selected.

LAG() OVER

Syntax

LAG ( expr [, offset [, default ] ] ) [ RESPECT NULLS | IGNORE NULLS ] OVER < window name or specification >

LAG ( expr [ RESPECT NULLS | IGNORE NULLS ] [, offset [, default ] ] ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function LAG returns the value of the row ahead of the current row by the specified offset.
If the offset is outside the window range, it returns the default value.

If the offset and default values are not specified, they are set to their default values.  
The default offset value is 1, and the default value for the default is NULL.

RESPECT NULLS returns the value of the row ahead by the specified offset, including NULLs.
IGNORE NULLS returns the value of the row ahead by the specified offset, excluding NULLs.
If not specified, the default value is RESPECT NULLS.

A window frame can not be used.

Example

gSQL> SELECT department_id, employee_id, manager_id,
             LAG( manager_id ) OVER ( ORDER BY employee_id ) AS lag
        FROM employees
       WHERE department_id = 90;

DEPARTMENT_ID EMPLOYEE_ID MANAGER_ID  LAG
------------- ----------- ---------- ----
           90         100       null null
           90         101        100 null
           90         102        100  100

3 rows selected.

The following is an example of how to specify the offset.

gSQL> SELECT department_id, employee_id, manager_id,
             LAG( manager_id, 2 ) OVER ( ORDER BY employee_id ) AS lag
        FROM employees
       WHERE department_id = 90;

DEPARTMENT_ID EMPLOYEE_ID MANAGER_ID  LAG
------------- ----------- ---------- ----
           90         100       null null
           90         101        100 null
           90         102        100 null

3 rows selected.

The following is an example of how to specify the offset and default values.

gSQL> SELECT department_id, employee_id, manager_id,
             LAG( manager_id, 2, 0 ) OVER ( ORDER BY employee_id ) AS lag
        FROM employees
       WHERE department_id = 90;

DEPARTMENT_ID EMPLOYEE_ID MANAGER_ID  LAG
------------- ----------- ---------- ----
           90         100       null    0
           90         101        100    0
           90         102        100 null

3 rows selected.

LAST() OVER

Syntax

aggregation_function KEEP ( DENSE_RANK LAST ORDER BY <sort specification list> ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function LAST sorts the sort specification list used in the order by clause within the KEEP clause, then returns the aggregation function value of rows whose DENSE_RANK is last.

The aggregation_functions are AVG, COUNT, COUNT(*), SUM, MAX, MIN, STDDEV, and VARIANCE.

order by is not allowed within the window clause. 
A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
           DENSE_RANK() OVER ( ORDER BY department_id ) AS "DENSE_RANK",
           MAX( salary ) KEEP ( DENSE_RANK LAST ORDER BY department_id ) OVER () AS "MAX_LAST"
  FROM employees
 WHERE department_id BETWEEN 90 AND 100;

DEPARTMENT_ID SALARY DENSE_RANK MAX_LAST
------------- ------ ---------- --------
           90  24000          1    12000
           90  17000          1    12000
           90  17000          1    12000
          100  12000          2    12000
          100   9000          2    12000
          100   8200          2    12000
          100   7700          2    12000
          100   7800          2    12000
          100   6900          2    12000

9 rows selected.

LAST_DAY

Syntax

LAST_DAY( date )

Description

It returns the last day of the month that is included in the date.

The data type of the date argument can be DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.
The return type is always DATE, regardless of the data type of the date argument.

If the date is NULL, it returns NULL.

Example

gSQL> SELECT 
      LAST_DAY( TO_DATE( '2012-07-10', 'YYYY-MM-DD' ) ) AS RESULT FROM DUAL;
RESULT    
----------
2012-07-31
1 row selected.

LAST_IDENTITY_VALUE

Syntax

LAST_IDENTITY_VALUE()

Description

It is the most recent value automatically generated for an identity column in the current session, and the result type is NATIVE_BIGINT.

If no automatically generated value exists, it returns NULL.

This function is similar to @@IDENTITY in MS-SQL and LAST_INSERT_ID() in MySQL. Be cautious when using it, as the last altered table determines the value when performing DML on multiple tables, as shown below.

gSQL> INSERT INTO t1(name) VALUES ( 'leekmo' );

1 row created.

gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

LAST_IDENTITY_VALUE()
---------------------
                   12

1 row selected.


gSQL> INSERT INTO t2(name) VALUES ( 'leekmo' );

1 row created.



gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

LAST_IDENTITY_VALUE()
---------------------
                    2

1 row selected.

To obtain the identity column value created during an INSERT, use the INSERT INTO name RETURNING .. INTO statement, as shown below.

gSQL> CREATE TABLE t1 ( id INTEGER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR(32) );

Table created.

gSQL> \var v1 integer
gSQL> INSERT INTO t1(name) VALUES ( 'leekmo' ) RETURN id INTO :v1;

V1
--
 1

1 row created.

Example

The following is an example of how to use the LAST_IDENTITY_VALUE() function.

gSQL> CREATE TABLE t1 ( id   INTEGER GENERATED BY DEFAULT AS IDENTITY,
                        name VARCHAR(32) ); 

Table created.

gSQL> COMMIT;

Commit complete.

There is no identity value created in the current session.

gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

LAST_IDENTITY_VALUE()
---------------------
                 null

1 row selected.

An identity value (1) is automatically generated.

gSQL> INSERT INTO t1(name) VALUES ( 'leekmo' ); 
1 row created.

Result: 1

gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

LAST_IDENTITY_VALUE()
---------------------
                    1

1 row selected.

An identity value (2) is automatically generated as the default value.

gSQL> UPDATE t1 SET id = DEFAULT;

1 row updated.

Result: 2

gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

LAST_IDENTITY_VALUE()
---------------------
                    2

1 row selected.

The user input value does not automatically generate an identity value.

INSERT INTO t1 VALUES ( 100, 'jhkim' );

1 row updated.

Result: 2

SELECT LAST_IDENTITY_VALUE() FROM dual;

LAST_IDENTITY_VALUE()
---------------------
                    2

1 row selected.

LAST_VALUE() OVER

Syntax

LAST_VALUE ( expr ) [ RESPECT NULLS | IGNORE NULLS ] OVER < window name or specification >

LAST_VALUE ( expr [ RESPECT NULLS | IGNORE NULLS ] ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function LAST_VALUE returns the last value of the expr.

RESPECT NULLS returns the last value of rows, including NULLs. 
IGNORE NULLS returns the last value of rows, excluding NULLs. 
If not specified, the default value is RESPECT NULLS.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , LAST_VALUE( min_price ) OVER ( ORDER BY min_price ) AS "LAST_VALUE"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE LAST_VALUE
--------- ----------
       73         73
      247        247
      731        731
     null       null
     null       null

5 rows selected.

The following is an example of specifying IGNORE NULLS for null_treatment.

gSQL> SELECT min_price AS "MIN_PRICE"
             , LAST_VALUE( min_price IGNORE NULLS ) OVER ( ORDER BY min_price ) AS "LAST_VALUE"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE LAST_VALUE
--------- ----------
       73         73
      247        247
      731        731
     null        731
     null        731

5 rows selected.

LEAD() OVER

Syntax

LEAD ( expr [, offset [, default ] ] ) [ RESPECT NULLS | IGNORE NULLS ] OVER < window name or specification >

LEAD ( expr [ RESPECT NULLS | IGNORE NULLS ] [, offset [, default ] ] ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function LEAD returns the value of the row behind the current row by the specified offset. 
If the offset is outside of the window range, it returns the default value.

If the offset and default values are not specified, they are set to their default values.  
The default offset value is 1, and the default value for the default is NULL.

RESPECT NULLS returns the value of the row behind by the specified offset, including NULLs.
IGNORE NULLS returns the value of the row behind by the specified offset, excluding NULLs.
If not specified, the default value is RESPECT NULLS.

A window frame can not be used.

Example

gSQL> SELECT department_id, employee_id, manager_id,
             LEAD( manager_id ) OVER ( ORDER BY employee_id DESC ) AS lead
        FROM employees
       WHERE department_id = 90;

DEPARTMENT_ID EMPLOYEE_ID MANAGER_ID LEAD
------------- ----------- ---------- ----
           90         102        100  100
           90         101        100 null
           90         100       null null

3 rows selected.

The following is an example of how to specify the offset.

gSQL> SELECT department_id, employee_id, manager_id,
             LEAD( manager_id, 2 ) OVER ( ORDER BY employee_id DESC ) AS lead
        FROM employees
       WHERE department_id = 90;

DEPARTMENT_ID EMPLOYEE_ID MANAGER_ID LEAD
------------- ----------- ---------- ----
           90         102        100 null
           90         101        100 null
           90         100       null null

3 rows selected.

The following is an example of how to specify the offset and default values.

gSQL> SELECT department_id, employee_id, manager_id,
             LEAD( manager_id, 2, 0 ) OVER ( ORDER BY employee_id DESC ) AS lead
        FROM employees
       WHERE department_id = 90;

DEPARTMENT_ID EMPLOYEE_ID MANAGER_ID LEAD
------------- ----------- ---------- ----
           90         102        100 null
           90         101        100    0
           90         100       null    0

3 rows selected.

LEAST

Syntax

LEAST( expr1 [, expr2, ... exprn ] )

Description

It returns the smallest value among the expr arguments provided.

If any expr argument is NULL, the result will be NULL.

The result type is determined based on the data type of expr1 (the first expr). 
If the data type of expr1 is numeric or character, the result type is determined to be a type that can include the range of expr1, ..., exprN each.
If all of expr1, ..., exprN are defined as CHAR type, then all exprs are compared as VARCHAR type, and the result type is determined to be VARCHAR.

Example

gSQL> SELECT LEAST( 100, 0, 200, 150, 1 ) AS RESULT FROM DUAL;
RESULT
------
     0
1 row selected.

LENGTH

Syntax

LENGTH( str )

Description

It is an alias of CHAR_LENGTH.

Example

Multi-byte character set: (e.g.UTF8)

gSQL> SELECT LENGTH( 'αβ-SUMMER' ) AS RESULT FROM DUAL;
     RESULT   
     ---------------
              9
    1 row selected.

LENGTHB

Syntax

LENGTHB( str )

Description

It is an alias of OCTET_LENGTH.
For more information, refer to BYTE_LENGTH.

Example

Multi-byte character set (e.g.UTF8): 1 byte character

gSQL> SELECT LENGTHB( 'OCTET_LENGTH' ) AS RESULT_1BYTE_CHARACTERS 
        FROM DUAL;
RESULT_1BYTE_CHARACTERS
-----------------------
                     12
1 row selected.

Multi-byte character set (e.g.UTF8): 2 byte character

gSQL> SELECT LENGTHB( 'αβ' ) AS RESULT_2BYTE_CHARACTERS FROM DUAL;
RESULT_2BYTE_CHARACTERS
-----------------------
                      4
1 row selected.

LISTAGG() OVER

Syntax

LISTAGG( str [, delimiter] ) WITHIN GROUP ( ORDER BY <sort specification list> ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function LISTAGG concatenates str in their sorted order within each group.

Only the PARTITION BY clause can be used in the OVER() clause of LISTAGG.
It divides the query result set into groups using the OVER() clause.

It sorts the records within the group using WITHIN GROUP ( ORDER BY <sort specification list> ).

It concatenates str in the order of the records sorted within each group. 
If str is NULL, it is excluded.

A delimiter is str connection delimiter, and if omitted, the default value is NULL.

The str can be either a character string or a binary string. 
If str is a character string, the result type is varchar. 
If str is a binary string, the result type is varbinary.

Example

gSQL> 
SELECT regionkey,
       name,
       LISTAGG( name ) WITHIN GROUP ( ORDER BY nationkey ) 
                       OVER ( PARTITION BY regionkey ) 
       AS "LISTAGG( name ) RESULT",
       LISTAGG( name, ', ' ) WITHIN GROUP ( ORDER BY nationkey ) 
                             OVER ( PARTITION BY regionkey ) 
       AS "LISTAGG( name, ', ' ) RESULT"
  FROM nation;

REGIONKEY NAME    LISTAGG( name ) RESULT    LISTAGG( name, ', ' ) RESULT
--------- ------- ------------------------- ----------------------------
        1 BRAZIL  BRAZILCANADAPERU          BRAZIL, CANADA, PERU        
        1 CANADA  BRAZILCANADAPERU          BRAZIL, CANADA, PERU        
        1 PERU    BRAZILCANADAPERU          BRAZIL, CANADA, PERU        
        1 null    BRAZILCANADAPERU          BRAZIL, CANADA, PERU        
        2 null    INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 INDIA   INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 null    INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 null    INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 JAPAN   INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 CHINA   INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 null    INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        2 VIETNAM INDIAJAPANCHINAVIETNAM    INDIA, JAPAN, CHINA, VIETNAM
        3 EGYPT   EGYPTIRANIRAQ             EGYPT, IRAN, IRAQ           
        3 IRAN    EGYPTIRANIRAQ             EGYPT, IRAN, IRAQ           
        3 IRAQ    EGYPTIRANIRAQ             EGYPT, IRAN, IRAQ           

15 rows selected.

LN

Syntax

LN( num )

Description

It returns the natural logarithm value of num.

num must be greater than 0.
If num is NULL, it returns NULL.

Example

gSQL> SELECT LN( 2.71828182845905 ) AS RESULT FROM DUAL;
RESULT
------
     1
1 row selected.

LNNVL

Syntax

LNNVL( expr )

Description

The Logical Not Null VaLue (LNNVL) function is similar to the NOT logical operator, but the difference is that it returns TRUE when the input value is NULL, as shown in the following example.

Example

gSQL> SELECT c1, c2, (c1 = c2), NOT(c1 = c2), LNNVL(c1 = c2) FROM t1;

C1   C2 (C1 = C2) NOT(C1 = C2) LNNVL(C1 = C2)
-- ---- --------- ------------ --------------
 1    1 TRUE      FALSE        FALSE         
 1    2 FALSE     TRUE         TRUE          
 1 null null      null         TRUE          

3 rows selected.

LOCAL_GROUP_ID

Syntax

LOCAL_GROUP_ID()

Description

It returns the cluster group ID for the server that processes a user's query.

It is valid information in a cluster system.

Example

All rows contain the same value.

gSQL> SELECT LOCAL_GROUP_ID() FROM DUAL;

LOCAL_GROUP_ID()
----------------
               1

1 row selected.

LOCAL_GROUP_NAME

Syntax

LOCAL_GROUP_NAME()

Description

It returns the cluster group name for the server that processes a user's query.

It is valid information in a cluster system.

Example

All rows contain the same value.

gSQL> SELECT LOCAL_GROUP_NAME() FROM DUAL;

LOCAL_GROUP_NAME()
------------------
G1                

1 row selected.

LOCAL_MEMBER_ID

Syntax

LOCAL_MEMBER_ID()

Description

It returns the cluster member ID for the server that processes a user's query.

It is valid information in a cluster system.

Example

All rows contain the same value.

gSQL> SELECT LOCAL_MEMBER_ID() FROM DUAL;

LOCAL_MEMBER_ID()
-----------------
                1

1 row selected.

LOCAL_MEMBER_NAME

Syntax

LOCAL_MEMBER_NAME()

Description

It returns the cluster member name for the server that processes a user's query.

It is valid information in a cluster system.

Example

All rows contain the same value.

gSQL> SELECT LOCAL_MEMBER_NAME() FROM DUAL;

LOCAL_MEMBER_NAME()
-------------------
G1N1               

1 row selected.

LOCAL_MEMBER_POSITION

Syntax

LOCAL_MEMBER_POSITION()

Description

It returns the cluster member position for the server that processes a user's query.

It is valid information in a cluster system.

Example

All rows contain the same value.

gSQL> SELECT LOCAL_MEMBER_POSITION() FROM DUAL;

LOCAL_MEMBER_POSITION()
-----------------------
                      0

1 row selected.

LOCALTIME

Syntax

LOCALTIME [()]
STATEMENT_LOCALTIME()

Description

The current TIME WITHOUT TIME ZONE type value is obtained based on the session time.

LOCALTIME is an SQL standard function.

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_LOCALTIME(): All time values within the transaction are the same.
• LOCALTIME, STATEMENT_LOCALTIME(): All time values within an SQL statement are the same.
• CLOCK_LOCALTIME(): Whenever the function is called, the current time value is returned.

Example

All the rows have the same value.

gSQL>  SELECT LOCALTIME FROM t1;

LOCALTIME      
---------------
16:17:08.592459
16:17:08.592459
16:17:08.592459

3 rows selected.

LOCALTIMESTAMP

Syntax

LOCALTIMESTAMP [()]
STATEMENT_LOCALTIMESTAMP()

Description

The current value of the TIMESTAMP WITHOUT TIME ZONE type is obtained based on the session time.

LOCALTIMESTAMP is an SQL standard function.

The differences among the functions for obtaining the current TIMESTAMP are as follows.

• TRANSACTION_LOCALTIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• LOCALTIMESTAMP, STATEMENT_LOCALTIMESTAMP(): All TIMESTAMP values within an SQL statement are the same.
• CLOCK_LOCALTIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

All rows contain the same value.

gSQL> SELECT LOCALTIMESTAMP FROM t1;

LOCALTIMESTAMP            
--------------------------
2013-12-12 16:21:51.790614
2013-12-12 16:21:51.790614
2013-12-12 16:21:51.790614

3 rows selected.

LOG

Syntax

LOG( num2 )
LOG( num1, num2 )

Description

It returns the logarithm of num2 with base num1.
If num1 is omitted, the value is calculated with base 10.

num1 must be a positive number, except for 1 and 0, and num2 must be a positive number.

If num1 or num2 is NULL, it returns NULL.

Example

gSQL> SELECT LOG( 100 ) AS RESULT1, LOG( 4, 16 ) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
      2       2
1 row selected.

LOGON_USER

Syntax

LOGON_USER()

Description

It returns the currently logged-in user.

User information is managed in three types, as follows.

Logon user: The user who performed the login, and their identity is maintained until the connection is closed.
Session user: It is the same as the initial logon user, but it can be changed using the SET SESSION AUTHORIZATION statement.
Current user: Generally the same as the session user, but it may be temporarily altered internally by the system for access control when using PSM, views, or similar features.
- The distinction between the session user and current user is similar to the difference between the real user and effective user in Unix systems.

Example

% gsql test test

gSQL> SELECT LOGON_USER() AS result FROM DUAL;

RESULT
------
TEST  

1 row selected.

LOWER

Syntax

LOWER( str )

Description

It returns the lowercase version of str.

The data type of the str argument can be a character type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
If str is NULL, the result will also be NULL.

The return type is the same as the data type of the str argument.

Example

gSQL> SELECT LOWER( 'SPRING' ) AS RESULT FROM DUAL;
RESULT
------
spring
1 row selected.

LPAD

Syntax

LPAD( str, length, [, fill] )

Description

It returns the value of str with the specified fill string added to the left side until the length of the string reaches length.

The data type of the str argument can be a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type, such as BINARY, BINARY VARYING, or BINARY LONG VARYING.

The length argument is a numeric type.

length represents the number of characters, and its maximum range is the maximum PRECISION of the result type.
If fill is omitted, a whitespace character is added.
If str is longer than length, str is truncated to the specified length before being returned.
If any of str, length, or fill is NULL, the result will also be NULL.
If length is 0 or a negative number, the result will also be NULL.

The following table describes the result types.

Result type of LPAD
str type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT LPAD('AA', 5) AS RESULT1,
             LPAD('AA', 5, 'X' ) AS RESULT2,
             LPAD('AA', 1 ) AS RESULT3
      FROM DUAL;
RESULT1 RESULT2 RESULT3
------- ------- -------
   AA   XXXAA   A      
1 row selected.

LTRIM

Syntax

LTRIM( trim_source [, trim_character ] )

Description

It removes the characters that match trim_character from the left side of trim_source, continuing until there are no more matching characters, and then returns the result.

The data type of the trim_character and trim_source arguments can be a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type, such as BINARY, BINARY VARYING, or BINARY LONG VARYING.

If either trim_character or trim_source is NULL, the result will be NULL.
If trim_character is omitted, a single blank space (' ') is used by default.

The following table describes the result types.

Result type of LTRIM
trim_source, trim_character type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT LTRIM( '_____LTRIM', '_' ) AS RESULT FROM DUAL;
RESULT
------
LTRIM 
1 row selected.

MAX

Syntax

MAX( [ ALL | DISTINCT ] expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregate function that returns the maximum value from the exprs of all rows.

If ALL is explicitly specified, the aggregation is performed for all values.
If DISTINCT is explicitly specified, aggregation is performed for the values excluding duplicates.
If neither ALL nor DISTINCT is specified, it is treated as if ALL were specified.

The MAX function returns the same result, unaffected by ALL or DISTINCT.

If FILTER is specified, aggregation is performed only on the values that satisfy the condition.

Example

gSQL> SELECT MAX( c1 ) FROM t1;

MAX(C1)
-------
      3

1 row selected.


gSQL> SELECT MAX( c1 ) FILTER( WHERE c1 < 3 ) FROM t1;

MAX(C1)
-------
      2

1 row selected.

MAX() OVER

Syntax

MAX ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function MAX returns the maximum value of exprs.
NULL values are excluded from the calculation.

Example

gSQL> SELECT product_id AS "PRODUCT_ID", min_price AS "MIN_PRICE"
             , MAX( min_price ) OVER ( ORDER BY product_id ) AS "MAX"
        FROM product_information
       WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE  MAX
---------- --------- ----
      1769      null null
      1770        73   73
      2378       247  247
      2382       731  731
      3355      null  731

5 rows selected.

MEDIAN() OVER

Syntax

MEDIAN ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function MEDIAN returns the median value of rows.
NULL values are excluded from the calculation.

order by is not allowed within the window clause.
A window frame can not be used.

Example

gSQL> SELECT supplier_id, min_price
             , MEDIAN( min_price ) OVER ( PARTITION BY supplier_id ) AS "MEDIAN"
        FROM product_information
       WHERE supplier_id = 102050;

SUPPLIER_ID MIN_PRICE MEDIAN
----------- --------- ------
     102050        73    247
     102050       247    247
     102050       731    247
     102050      null    247
     102050      null    247

5 rows selected.

MIN

Syntax

MIN( [ ALL | DISTINCT ] expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregate function that returns the minimum value from the exprs of all rows.

If ALL is explicitly specified, the aggregation is performed for all values.
If DISTINCT is explicitly specified, aggregation is performed for the values excluding duplicates.
If neither ALL nor DISTINCT is specified, it is treated as if ALL were specified.

The MIN function returns the same result, unaffected by ALL or DISTINCT.

If FILTER is specified, aggregation is performed only on the values that satisfy the condition.

Example

gSQL> SELECT MIN( c1 ) FROM t1;

MIN(C1)
-------
      1

1 row selected.


gSQL> SELECT MIN( c1 ) FILTER( WHERE c1 > 1 ) FROM t1;

MIN(C1)
-------
      2

1 row selected.

MIN() OVER

Syntax

MIN ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function MIN returns the minimum value of exprs. 
NULL values are excluded from the calculation.

Example

gSQL> SELECT product_id AS "PRODUCT_ID", min_price AS "MIN_PRICE"
             , MIN( min_price ) OVER ( ORDER BY product_id DESC ) AS "MIN"
        FROM product_information
       WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE  MIN
---------- --------- ----
      3355      null null
      2382       731  731
      2378       247  247
      1770        73   73
      1769      null   73

5 rows selected.

MOD

Syntax

MOD( num1, num2 )

Description

It divides num1 by num2 and returns the remainder.

Both num1 and num2 arguments can be of a numeric data type.
If num2 is 0, an error is returned.
If either the num1 or num2 argument is NULL, the result will also be NULL.

Example

gSQL> SELECT MOD(5, 4) AS RESULT1, MOD(-5, 4) AS RESULT2 FROM DUAL;
RESULT1 RESULT2
------- -------
      1      -1
1 row selected.

MONTHS_BETWEEN

Syntax

MONTHS_BETWEEN( date1, date2 )

Description

MONTHS_BETWEEN returns the number of months by dividing the number of days between date2 and date1 by 31.

If either date1 or date2 is NULL, the result will also be NULL.
The date1 and date2 arguments can be of type DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.

The result type is NUMBER.

If the same date (e.g., 2014-01-15 and 2014-02-15) or the last day of the month (e.g., 2014-08-31 and 2014-09-30) is included in both date1 and date2, the result will be an integer, regardless of any discrepancy in the timestamp portion (if present).

Example

gSQL> SELECT
        MONTHS_BETWEEN('2018-01-18', '2018-01-17')
  FROM DUAL;

MONTHS_BETWEEN('2018-01-18', '2018-01-17')
------------------------------------------
                      3.225806451612903E-2
1 row selected.

gSQL> SELECT
        MONTHS_BETWEEN('2018-02-17', '2018-01-17')
  FROM DUAL;

MONTHS_BETWEEN('2018-02-17', '2018-01-17')
------------------------------------------
                                         1
1 row selected.

gSQL> SELECT
        MONTHS_BETWEEN('2018-02-28', '2018-01-31')
  FROM DUAL;

MONTHS_BETWEEN('2018-02-28', '2018-01-31')
------------------------------------------
                                         1
1 row selected.

NEXT_DAY

Syntax

NEXT_DAY( date, day )

Description

It returns the date of the first day of the week that comes after the given date (argument).

The second argument, day, can be a string or a number that represents a day.
• String: SUNDAY ~ SATURDAY  or SUN ~ SAT
• Number: 1 (sunday) ~ 7 (saturday)

If any of the input argument is NULL, the result will also be NULL.

The return type is always DATE, regardless of the input type of the date.
The hour, minute, and second of the result will be the same as those of the input argument date.

Example

2020-08-11 is Tuesday.

gSQL> SELECT NEXT_DAY( TO_DATE( '2020-08-11', 'YYYY-MM-DD'),
                       'SUNDAY' ) AS RESULT1
      FROM DUAL;
RESULT1   
----------
2020-08-16
1 row selected.

gSQL> SELECT NEXT_DAY( TO_DATE( '2020-08-11', 'YYYY-MM-DD' ),
                       'SUN' ) AS RESULT1
FROM DUAL;
RESULT1   
----------
2020-08-16
1 row selected.

gSQL> SELECT NEXT_DAY( TO_DATE( '2020-08-11', 'YYYY-MM-DD' ),
                       1 ) AS RESULT1
FROM DUAL;
RESULT1   
----------
2020-08-16
1 row selected.

gSQL> SELECT TO_CHAR( NEXT_DAY( TO_DATE( '2020-08-11', 'YYYY-MM-DD' ),
                                'SUNDAY' ),
                      'YYYY-MM-DD HH24:MI:SS' ) AS RESULT1
FROM DUAL;
RESULT1            
-------------------
2020-08-16 00:00:00
1 row selected.

NEXTVAL

Syntax

seq_name.NEXTVAL
NEXTVAL( seq_name )
NEXT VALUE FOR seq_name

Description

It retrieves the next value from the sequence object.

Example

gSQL> CREATE SEQUENCE seq;

Sequence created.

gSQL> COMMIT;

Commit complete.

gSQL> SELECT seq.NEXTVAL FROM dual;

SEQ.NEXTVAL
-----------
          1

1 row selected.

gSQL> SELECT NEXTVAL( seq ) FROM dual;

NEXTVAL( SEQ )
--------------
             2

1 row selected.

gSQL> SELECT NEXT VALUE FOR seq FROM dual;

NEXT VALUE FOR SEQ
------------------
                 3

1 row selected.

NTH_VALUE() OVER

Syntax

NTH_VALUE ( expr, n ) [ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ] OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function NTH_VALUE returns the value of expr for the n-th row.
If the number of rows in the window is less than n, it returns NULL.

The n argument can be a numeric type or a type that can be converted to a number.

FROM FIRST refers to the n-th row starting from the first row.
FROM LAST refers to the n-th row starting from the last row.
If not specified, the default value is FROM FIRST.

RESPECT NULLS returns the value of the n-th row, including NULL. 
IGNORE NULLS returns the value of the n-th row, excluding NULL. 
If not specified, the default value is RESPECT NULLS.

Example

gSQL> SELECT product_id, min_price,
             NTH_VALUE( min_price, 2 ) OVER ( ORDER BY product_id ) AS nth_value
      FROM product_information
      WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE NTH_VALUE
---------- --------- ---------
      1769      null      null
      1770        73        73
      2378       247        73
      2382       731        73
      3355      null        73

5 rows selected.

The following is an example when FROM LAST is specified.

gSQL> SELECT product_id, min_price,
             NTH_VALUE( min_price, 2 ) FROM LAST OVER ( ORDER BY product_id ) AS nth_value
      FROM product_information
      WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE NTH_VALUE
---------- --------- ---------
      1769      null      null
      1770        73      null
      2378       247        73
      2382       731       247
      3355      null       731

5 rows selected.

The following is an example when IGNORE NULLS is specified.

gSQL> SELECT product_id, min_price,
             NTH_VALUE( min_price, 2 ) IGNORE NULLS OVER ( ORDER BY product_id ) AS nth_value
      FROM product_information
      WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE NTH_VALUE
---------- --------- ---------
      1769      null      null
      1770        73      null
      2378       247       247
      2382       731       247
      3355      null       247

5 rows selected.

NTILE() OVER

Syntax

NTILE( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function NTILE returns the bucket number for each row.

The bucket numbers are consecutive integers starting from 1, and the number of buckets is the same as the number of expr.
If the number of buckets exceeds the number of rows, each row is assigned a bucket, and the remaining buckets will be empty.

expr must be a positive constant. If expr is not a fixed number, it must be the target of the window partition by.

A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
             NTILE(3) OVER ( ORDER BY salary ) AS ntile
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY NTILE
------------- ------ -----
           60   4200     1
           60   4800     1
           60   4800     2
           60   6000     2
           60   9000     3

5 rows selected.

The following is an example where the number of buckets (the number of expr) is greater than the number of rows.

gSQL> SELECT department_id, salary,
             NTILE(6) OVER ( ORDER BY salary ) AS ntile
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY NTILE
------------- ------ -----
           60   4200     1
           60   4800     2
           60   4800     3
           60   6000     4
           60   9000     5

5 rows selected.

NULLIF

Syntax

NULLIF( expr1, expr2 )

Description

If expr1 is equal to expr2, it returns NULL. If they are not equal, it returns expr1, the first argument.

If the data types of expr1 and expr2 are different, the result type is determined by the Result Type Combination Rule.

NULLIF can be expressed using CASE as follows.

NULLIF( expr1, expr2 )

CASE WHEN expr1 = expr2 THEN NULL 
       ELSE expr1 
  END

Example

gSQL> SELECT NULLIF( 'SUN', 'SUN' ) AS RESULT1, 
             NULLIF( 'SUN', 'MOON' ) AS RESULT2 
       FROM DUAL;
RESULT1 RESULT2
------- -------
null    SUN    
1 row selected.

NUMTODSINTERVAL

Syntax

NUMTODSINTERVAL( num, interval_indicator )

Description

It converts the number in the interval_indicator unit to an interval day to second type and returns it.

The argument number is of a numeric type.

The argument interval_indicator is a character type, like CHAR or VARCHAR, and it must be one of 'DAY', 'HOUR', 'MINUTE', or 'SECOND', case-insensitive.

If any argument is NULL, the result will also be NULL.

The result is returned as an interval day(6) to second(6) type, and the user cannot arbitrarily modify the precision. If the leading precision of the converted result exceeds the default precision, an error is returned. If the fraction precision exceeds the default, the rounded value is returned.

Example

It converts 1 Day to an interval day to second type.

gSQL> SELECT NUMTODSINTERVAL(1, 'DAY') FROM DUAL;

NUMTODSINTERVAL(1, 'DAY')
-------------------------
+000001 00:00:00.000000  

1 row selected.

It converts 36 Hour to an interval day to second type.

gSQL> SELECT NUMTODSINTERVAL(36, 'HOUR') FROM DUAL;

NUMTODSINTERVAL(36, 'HOUR')
---------------------------
+000001 12:00:00.000000    

1 row selected.

It converts 1530 Minute to an interval day to second type.

gSQL> SELECT NUMTODSINTERVAL(1530, 'MINUTE') FROM DUAL;

NUMTODSINTERVAL(1530, 'MINUTE')
-------------------------------
+000001 01:30:00.000000        

1 row selected.

It converts 90100.1234567 Second to an interval day to second type.

gSQL> SELECT NUMTODSINTERVAL(90100.1234567, 'SECOND') FROM DUAL;

NUMTODSINTERVAL(90100.1234567, 'SECOND')
----------------------------------------
+000001 01:01:40.123457                 

1 row selected.

NUMTOYMINTERVAL

Syntax

NUMTOYMINTERVAL( num, interval_indicator )

Description

It converts the number in the interval_indicator unit to an interval year to month type and returns it.

The argument number is of a numeric type.

The argument interval_indicator is a character type, like CHAR or VARCHAR, and it must be one of 'YEAR', or 'MONTH', case-insensitive.

If any argument is NULL, the result will also be NULL.

The result is returned as an interval year(6) to month type, and the user cannot arbitrarily modify the precision. If the leading precision of the converted result exceeds the default precision, an error is returned.

Example

It converts 1 Year to an interval year to month type.

gSQL> SELECT NUMTOYMINTERVAL(1, 'YEAR') FROM DUAL;

NUMTOYMINTERVAL(1, 'YEAR')
--------------------------
+000001-00                

1 row selected.

It converts 13.5 Month to an interval year to month type.

gSQL> SELECT NUMTOYMINTERVAL(13.5, 'MONTH') FROM DUAL;

NUMTOYMINTERVAL(13.5, 'MONTH')
------------------------------
+000001-02                    

1 row selected.

NVL

Syntax

NVL( expr1, expr2 )

Description

If expr1 is not NULL, it returns expr1. If expr1 is NULL, it returns expr2.

The result type is determined by the data type of expr1.
If NULL is specified in expr1, the result type is determined by the data type of expr2. 
If the data type of expr1 is numeric or character type, the result type is determined to include the ranges of expr1 and expr2 respectively.
If both expr1 and expr2 are of the CHAR type, the result type is determined to be VARCHAR.

Example

gSQL> SELECT I1, NVL( I1, 0 ) FROM T1;
  I1 NVL( I1, 0 )
---- ------------
   1            1
null            0
2 rows selected.

NVL2

Syntax

NVL2( expr1, expr2, expr3 )

Description

If expr1 is not null, it returns expr2. If expr1 is NULL, it returns expr3.

The result type is determined by the data type of expr2. 
If NULL is specified in expr2, the result type is determined by the data type of expr3.
If the data type of expr2 is numeric or character type, the result type is determined to include the ranges of expr2 and expr3 respectively.
If both expr2 and expr3 are of the CHAR type, the result type is determined to be VARCHAR.

Example

gSQL> SELECT I1, NVL2( I1, I1 * 1000, 0 ) FROM T1;
  I1 NVL2( I1, I1 * 1000, 0 )
---- ------------------------
   1                     1000
null                        0
2 rows selected.

OCTET_LENGTH

Syntax

OCTET_LENGTH( str )  

BYTE_LENGTH( str )     

LENGTHB( str )

Description

It returns the number of bytes in the str.

The str argument can be of a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type, such as BINARY, BINARY VARYING, BINARY LONGVARYING.

If the str data type is CHARACTER, white spaces are included in the calculation.
If str is NULL, the result will also be NULL.

It is an alias of BYTE_LENGTH and LENGTHB.

Example

Multi-byte character set (e.g. UTF8): 1 byte character

gSQL> SELECT OCTET_LENGTH( 'OCTET_LENGTH' ) AS RESULT_1BYTE_CHARACTERS 
        FROM DUAL;
RESULT_1BYTE_CHARACTERS
-----------------------
                     12
1 row selected.

Multi-byte character set (e.g. UTF8): 2 byte character

gSQL> SELECT OCTET_LENGTH( 'αβ' ) AS RESULT_2BYTE_CHARACTERS FROM DUAL;
RESULT_2BYTE_CHARACTERS
-----------------------
                      4
1 row selected.

OVERLAY

Syntax

OVERLAY( str1 PLACING str2 FROM start_position  [ FOR string_length ] )

Description

It overlays the characters in the range between str1's start_position and string_length with str2.

The data types of str1 and str2 arguments can be character types, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or binary character types, such as BINARY, BINARY VARYING, BINARY LONG VARYING

The start_position and string_length arguments can be of a numeric data type.

The result of the OVERLAY function is as follows.
- When FOR is specified.
  SUBSTRING( str1 FROM 1 FOR (start_position - 1) )
  || str2
  || SUBSTRING( str1 FROM (start_position + string_length )
- When FOR is omitted.
  SUBSTRING( str1 FROM 1 FOR (start_position - 1) )
  || str2
  || SUBSTRING( str1 FROM (start_position + CHAR_LENGTH(str2))

For more information, refer to SUBSTRING.

The following table describes the result types.

Result type of OVERLAY
str1, str2 types	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT
         OVERLAY( 'RESULT_OF_XXX_FUNC' PLACING 'OVERLAY' FROM 11 )
         AS RESULT1,
         OVERLAY( 'RESULT_OF_XXX_FUNC' PLACING 'OVERLAY' FROM 11 FOR 3 )
         AS RESULT2
      FROM DUAL;
RESULT1            RESULT2               
------------------ ----------------------
RESULT_OF_OVERLAYC RESULT_OF_OVERLAY_FUNC
1 row selected.

PERCENT_RANK() OVER

Syntax

PERCENT_RANK( ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function PERCENT_RANK calculates the ranking ratio of each row relative to the total number of rows.

The result of the PERCENT_RANK function is a number between 0 and 1, and if the row values are the same, the ratio value is also the same.

A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
             PERCENT_RANK() OVER ( ORDER BY salary ) AS p_rank
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY P_RANK
------------- ------ ------
           60   4200      0
           60   4800    .25
           60   4800    .25
           60   6000    .75
           60   9000      1

5 rows selected.

PERCENTILE_CONT() OVER

Syntax

PERCENTILE_CONT( expr ) WITHIN GROUP ( ORDER BY <sort specification> ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function PERCENTILE_CONT is an inverse distribution function that assumes a continuous distribution model.

It calculates the value corresponding to the specified percentile score for not null values sorted within the group. 
The result of the calculation may differ from the specific value sorted within the group.

expr must be the percentile score, which is a value between 0 and 1.

Only the PARTITION BY clause is available in the OVER() clause. 
It divides the query result set into groups using the PARTITION BY clause.

It sorts the records within the group using WITHIN GROUP ( ORDER BY <sort specification> ). 
Only one <sort specification> can be specified in the ORDER BY clause.

NULL values are excluded from the sorted values.

The following is the calculation formula.

P : Percentile score 
N : The number of the records of not null values sorted within the group
RN = ( 1 + ( P * (N-1) ) )
CRN = CEILING( RN )
FRM = FLOOR( RN )

* if ( CRN = FRN = RN )
     sort expression value of RN
* else
     sort expression value of ( CRN - RN ) * FRN + sort expression value of ( RN - FRN ) * CRN

The window function MEDIAN is a specific case of the PERCENTILE_CONT window function, with a default percentile score of 0.5.

For more information, refer to the following.

Example

gSQL> 
SELECT item_no, 
       sales,
       PERCENTILE_CONT( 0.5 ) WITHIN GROUP ( ORDER BY sales ) 
                              OVER ( PARTITION BY item_no ) 
       AS PERCENTILE_CONT
  FROM store;

ITEM_NO SALES PERCENTILE_CONT
------- ----- ---------------
    100    50             125
    100    90             125
    100    90             125
    100   100             125
    100   120             125
    100   130             125
    100   150             125
    100   150             125
    100   170             125
    100   200             125
    235    50              90
    235    70              90
    235    90              90
    235   130              90
    235   190              90

15 rows selected.

PERCENTILE_DISC() OVER

Syntax

PERCENTILE_DISC( expr ) WITHIN GROUP ( ORDER BY <sort specification> ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model.

It calculates the percentile score for not null values sorted within the group. 
It determines the smallest value among those greater than or equal to the specified percentile.
The function then returns the value corresponding to the determined percentile.

expr must be the percentile score, which is a value between 0 and 1.

Only the PARTITION BY clause is available in the OVER() clause. 
It divides the query result set into groups using the PARTITION BY clause.

It sorts the records within the group using WITHIN GROUP ( ORDER BY <sort specification> ). 
Only one <sort specification> can be specified in the ORDER BY clause.

It calculates the CUME_DIST for the sort expression value based on the sorted records.
When calculating CUME_DIST, NULL values are excluded.

It determines the smallest CUME_DIST value that is greater than or equal to the percentile specified by the expr.
It then returns the determined CUME_DIST value.

The result type is the same as the type of the sort expression value.

For more information, refer to CUME_DIST() OVER.

Example

gSQL> 
SELECT item_no, 
       sales,
       CUME_DIST() OVER ( PARTITION BY item_no ORDER BY sales ) 
       AS CUME_DIST,
       PERCENTILE_DISC( 0.5 ) WITHIN GROUP ( ORDER BY sales ) 
                              OVER ( PARTITION BY item_no ) 
       AS PERCENTILE_DISC
  FROM store;

ITEM_NO SALES CUME_DIST PERCENTILE_DISC
------- ----- --------- ---------------
    100    50        .1             120
    100    90        .3             120
    100    90        .3             120
    100   100        .4             120
    100   120        .5             120
    100   130        .6             120
    100   150        .8             120
    100   150        .8             120
    100   170        .9             120
    100   200         1             120
    235    50        .2              90
    235    70        .4              90
    235    90        .6              90
    235   130        .8              90
    235   190         1              90

15 rows selected.

PHYSICAL_LENGTH

Syntax

PHYSICAL_LENGTH( expr )

Description

The PHYSICAL_LENGTH function returns the number of bytes of internal expression representation for the given expr.

The expr argument can be of any data type.

If an input argument is NULL, the result will be 0.

Example

When an input argument is NULL

gSQL> SELECT PHYSICAL_LENGTH( NULL ) AS RESULT FROM DUAL;
RESULT
------
     0
1 row selected.

The following is an example that shows the NUMBER type byte count for 1, 123, and 12345.

gSQL> SELECT PHYSICAL_LENGTH( 1 ) AS RESULT FROM DUAL;
RESULT
------
     2
1 row selected.

gSQL> SELECT PHYSICAL_LENGTH( 123 ) AS RESULT FROM DUAL;
RESULT
------
     3
1 row selected.

gSQL> SELECT PHYSICAL_LENGTH( 12345 ) AS RESULT FROM DUAL;
RESULT
------
     4
1 row selected.

PI

Syntax

PI()

Description

It returns the constant "π".

Example

gSQL> SELECT PI() AS RESULT FROM DUAL;
              RESULT
--------------------
3.141592653589793E+0
1 row selected.

POSITION

Syntax

POSITION( str1 IN str2 )

Description

It searches for the first occurrence of str1 within str2 and returns its position.

The data type of the str1 and str2 arguments can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type such as BINARY, BINARY VARYING, or BINARY LONG VARYING.

If str1 cannot be found within str2, the return value is 0.
If str1 is found within str2, the position of str1 is returned, starting from 1.
The returned position value is calculated in character units (not in byte units).
If either str1 or str2 is NULL, the return value is also NULL.

Example

gSQL> SELECT POSITION( 'CHAR' IN 'LONG CHAR 2000' ) AS RESULT FROM DUAL;
RESULT
------
     6
1 row selected.

POWER

Syntax

POWER( num1, num2 )

Description

It returns the value of num1 raised to the power of num2.

The num1 and num2 arguments can be of a numeric data type.

If num1 is a negative number, num2 must be an integer.
If either num1 or num2 is NULL, the result will also be NULL.

Example

gSQL> SELECT POWER( 2, 3 ) AS RESULT FROM DUAL;
RESULT
------
     8
1 row selected.

RADIANS

Syntax

RADIANS( degrees )

Description

It returns the radians of the given degrees.

The degrees argument can be a numeric data type.
If the degrees argument is NULL, the result will also be NULL.

Example

gSQL> SELECT RADIANS( 180 ) AS RESULT FROM DUAL;
          RESULT
----------------
3.14159265358979
1 row selected.

RANDOM

Syntax

RANDOM( min, max )

Description

It returns a random value greater than or equal to min and less than or equal to max.

The min and max arguments can be of a numeric data type.
If either the min or max argument is NULL, the result will also be NULL.

Example

gSQL> SELECT RANDOM( 1, 100 ) AS RESULT FROM DUAL;
          RESULT
----------------
34.1870528003201
1 row selected.

RANK() OVER

Syntax

RANK( ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function RANK calculates the ranking.

The ranking is an integer starting from 1, and rows with the same value have the same rank.

A window frame can not be used.

Example

gSQL> SELECT department_id, salary,
             RANK() OVER ( ORDER BY salary ) AS rank
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY RANK
------------- ------ ----
           60   4200    1
           60   4800    2
           60   4800    2
           60   6000    4
           60   9000    5

5 rows selected.

RATIO_TO_REPORT() OVER

Syntax

RATIO_TO_REPORT ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function RATIO_TO_REPORT calculates the ratio of each row value relative to the total sum of the expr values.
If the row value is NULL, the result will also be NULL.

order by cannot be used in the window clause.
A window frame cannot be used.

Example

gSQL> SELECT supplier_id, min_price
             , RATIO_TO_REPORT( min_price ) OVER ( PARTITION BY supplier_id ) AS "RATIO"
        FROM product_information
       WHERE supplier_id = 102050;

SUPPLIER_ID MIN_PRICE             RATIO
----------- --------- -----------------
     102050       731  .695528068506185
     102050      null              null
     102050        73 .0694576593720266
     102050       247  .235014272121789
     102050      null              null

5 rows selected.

REGEXP_COUNT

Syntax

REGEXP_COUNT ( source_string, pattern [, position [, match_param ] ] )

Description

It returns the number of times the pattern is matched in the source_string.
If no match is found, it returns 0.

source_string
It is the character expression of the search target, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

pattern
It is the regular expression, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING. 
It can be described up to 512 bytes.
For more information about the operators that can be specified in the pattern, refer to the Regular Expression Operators.

position
It is the positive integer that indicates the starting character position for searching in the source_string.
The default value is 1, meaning the search starts from the first character of the source_string.

match_param
It is the character expression that can alter the default matching operation of a function, and it can be of a character type such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

'i', 'c', 'n', 'm', 'x' can be specified in match_param, and one or more can be used.

'i': It is case-insensitive.
'c': It is case-sensitive.
'n': The dot operator ( . ) allows matching with the newline character.
'm': It treats the source_string as multiple lines. It interprets ^ ( Beginning-of-Line Anchor ) and $ ( End-of-Line Anchor ) for each line.
'x': It ignores whitespaces within the pattern.

If a character other than 'i', 'c', 'n', 'm', or 'x' appears in match_param, an error is returned. 
If contradictory uppercase and lowercase matching options, such as 'ic', are listed in match_param, an error is returned.

If match_param is omitted:
 • It is case sensitive. 
 • The dot operator ( . ) does not allow matching with the newline character. 
 • It treats the source_string as a single line.

Example

gSQL> 
SELECT reason_desc, REGEXP_COUNT( reason_desc, '\w+' ) AS word_cnt 
  FROM reason;
REASON_DESC                     WORD_CNT
------------------------------- --------
Wrong size                             2
Did not like the color                 5
No service location in my area         6
Found a better price in a store        7
4 rows selected.

### position
gSQL> 
SELECT start_date,
       REGEXP_COUNT( start_date, '-\d{2}', 3 ) AS RESULT
  FROM call_center;
START_DATE  RESULT
----------- ------
1998-01-01       2
1999-JAN-03      1
2000-02-21       2
2001-03-31       2
29-NOV-23        1
5 rows selected.

REGEXP_INSTR

Syntax

REGEXP_INSTR ( source_string, pattern [, position [, occurrence [, return_opt [, match_param [, subexpr ] ] ] ] ] )

Description

It returns the starting position of the string where the pattern is matched, or the position of the next character after the matched string in source_string as a number.
If the matched string does not exist, it returns 0.

source_string
It is the character expression of the search target, and it can be a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

pattern
It is the regular expression, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
It can be described up to 512 bytes.
For more information about the operators that can be specified in the pattern, refer to the Regular Expression Operators.

position
It is the positive integer that indicates the starting character position for searching in the source_string.
The default value is 1, meaning the search starts from the first character of the source_string.

occurrence
It is the positive integer that indicates the ordinal number of the matched pattern to search for in source_string.
The default value is 1, meaning it searches for the first matching in source_string.

return_opt
It indicates which result to return for the string where the pattern is matched in the source_string.

If return_opt is 0,
- It is the default value, and it returns the position of the first character of the matched string.
If return_opt is 1,
- It returns the position of the character following the matched string.

match_param
It is the character expression that can alter the default matching operation of a function, and it can be of a character type such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

'i', 'c', 'n', 'm', 'x' can be specified in match_param, and one or more can be used.

'i': It is case-insensitive.
'c': It is case-sensitive.
'n': The dot operator ( . ) allows matching with the newline character.
'm': It treats the source_string as multiple lines. It interprets ^ ( Beginning-of-Line Anchor ) and $ ( End-of-Line Anchor ) for each line.
'x': It ignores whitespaces within the pattern.

If a character other than 'i', 'c', 'n', 'm', or 'x' appears in match_param, an error is returned. 
If contradictory uppercase and lowercase matching options, such as 'ic', are listed in match_param, an error is returned.

If match_param is omitted:
 • It is case sensitive.
 • The dot operator ( . ) does not allow matching with the newline character.
 • It treats the source_string as a single line.

subexpr
It is a positive number from 0 to 9 that indicates the subexpression described in the pattern.
For more information about subexpr, refer to the Regular Expression Operators.

Example

gSQL> 
SELECT reason_desc, 
       REGEXP_INSTR( reason_desc, '[[:space:]]' ) AS RESULT
  FROM reason;
REASON_DESC                     RESULT
------------------------------- ------
Wrong size                           6
Did not like the color               4
No service location in my area       3
Found a better price in a store      6
4 rows selected.

### position, occurrence
gSQL> 
SELECT reason_desc, REGEXP_INSTR( reason_desc, '\w+', 1, 2 ) AS RESULT
  FROM reason;
REASON_DESC                     RESULT
------------------------------- ------
Wrong size                           7
Did not like the color               5
No service location in my area       4
Found a better price in a store      7
4 rows selected.

### return_opt
gSQL> 
SELECT reason_desc, 
       REGEXP_INSTR( reason_desc, '\w+', 1, 2, 0 ) AS RESULT_RETURN_OPT_0,
       REGEXP_INSTR( reason_desc, '\w+', 1, 2, 1 ) AS RESULT_RETURN_OPT_1
  FROM reason;
REASON_DESC                     RESULT_RETURN_OPT_0 RESULT_RETURN_OPT_1
------------------------------- ------------------- -------------------
Wrong size                                        7                  11
Did not like the color                            5                   8
No service location in my area                    4                  11
Found a better price in a store                   7                   8
4 rows selected.

### subexpr
gSQL> 
SELECT REGEXP_INSTR( '123456789', '(12)(3456(789))', 1, 1, 0, 'i', 3 ) 
       AS RESULT
  FROM dual;
RESULT
------
     7
1 row selected.

REGEXP_REPLACE

Syntax

REGEXP_REPLACE ( source_string, pattern [, replace_string [, position [, occurrence [, match_param ] ] ] ]  )

Description

It returns a string in which the matched pattern in the source_string is replaced with the replace_string.

source_string
It is the character expression of the search target, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

pattern
It is the regular expression, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
It can be described up to 512 bytes.
For more information about the operators that can be specified in the pattern, refer to the Regular Expression Operators.

replace_string
It is the character expression that replaces the string matching the pattern in source_string, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
replace_string may include backrefercence in the form of \n, where n is an integer from 1 to 9.
To include a backslash (\) as a character in replace_string, describe it together with the escape character backslash (\\).

position
It is the positive integer that indicates the starting character position for searching in the source_string.
The default value is 1, meaning the search starts from the first character of the source_string.

occurrence
It is the positive integer that indicates the ordinal number of the matched pattern to search for in source_string.
If it is 0, it replaces all strings that match the pattern with replace_string.
If it is a positive integer n, it replaces the n-th string that matches the pattern with replace_string.
If it is omitted, the default value is 0.

match_param
It is the character expression that can alter the default matching operation of a function, and it can be of a character type such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

'i', 'c', 'n', 'm', 'x' can be specified in match_param, and one or more can be used.

'i': It is case-insensitive.
'c': It is case-sensitive.
'n': The dot operator ( . ) allows matching with the newline character.
'm': It treats the source_string as multiple lines. It interprets ^ ( Beginning-of-Line Anchor ) and $ ( End-of-Line Anchor ) for each line.
'x': It ignores whitespaces within the pattern.

If a character other than 'i', 'c', 'n', 'm', 'x' appears in match_param, an error is returned. 
If contradictory uppercase and lowercase matching options, such as 'ic', are listed in match_param, an error is returned.

If match_param is omitted, 
• It is case sensitive. 
• The dot operator ( . ) does not allow matching with the newline character. 
• It treats the source_string as a single line.

Example

gSQL> 
SELECT reason_desc, 
       REGEXP_REPLACE( reason_desc, '\s', '[ ]' ) AS RESULT
  FROM reason;
REASON_DESC                     RESULT                                     
------------------------------- -------------------------------------------
Wrong size                      Wrong[ ]size                               
Did not like the color          Did[ ]not[ ]like[ ]the[ ]color             
No service location in my area  No[ ]service[ ]location[ ]in[ ]my[ ]area   
Found a better price in a store Found[ ]a[ ]better[ ]price[ ]in[ ]a[ ]store
4 rows selected.


### It includes the backreference in the replace string.
gSQL> 
SELECT start_date,
       REGEXP_REPLACE( start_date, 
                       '([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})',
                       '\3/\2/\1' ) AS RESULT
  FROM call_center;
START_DATE RESULT    
---------- ----------
1998-01-01 01/01/1998
2000-02-21 21/02/2000
2001-03-31 31/03/2001
3 rows selected.

REGEXP_SUBSTR

Syntax

REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, match_param [, subexpr ] ] ] ] )

Description

It returns the string that matches the pattern in source_string.

source_string
It is the character expression of the search target, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

pattern
It is the regular expression, and it can be of a character type or a type that can be converted to a character, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
It can be described up to 512 bytes.
For more information about the operators that can be specified in the pattern, refer to the Regular Expression Operators.

position
It is the positive integer that indicates the starting character position for searching in the source_string.
The default value is 1, meaning the search starts from the first character of the source_string.

occurrence
It is the positive integer that indicates the ordinal number of the matched pattern to search for in source_string.
The default value is 1, meaning it searches for the first matching in source_string.

match_param
It is the character expression that can alter the default matching operation of a function, and it can be of a character type such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

'i', 'c', 'n', 'm', 'x' can be specified in match_param, and one or more can be used.

'i': It is case-insensitive.
'c': It is case-sensitive.
'n': The dot operator ( . ) allows matching with the newline character.
'm': It treats the source_string as multiple lines. It interprets ^ ( Beginning-of-Line Anchor ) and $ ( End-of-Line Anchor ) for each line.
'x': It ignores whitespaces within the pattern.

If a character other than 'i', 'c', 'n', 'm', or 'x' appears in match_param, an error is returned. 
If contradictory uppercase and lowercase matching options, such as 'ic', are listed in match_param, an error is returned.

If match_param is omitted:
 • It is case sensitive.
 • The dot operator ( . ) does not allow matching with the newline character.
 • It treats the source_string as a single line.

subexpr
It is a positive number from 0 to 9 that indicates the subexpression described in the pattern. 
A subexpression is a part of the pattern enclosed in parentheses ( ) .
For more information about subexpr, refer to the Regular Expression Operators.

Example

gSQL> 
SELECT reason_desc,
       REGEXP_SUBSTR( reason_desc, '[[:alpha:]]+' ) AS RESULT
  FROM reason;
REASON_DESC                     RESULT
------------------------------- ------
Wrong size                      Wrong 
Did not like the color          Did   
No service location in my area  No    
Found a better price in a store Found 
4 rows selected.


### position, occurrence
gSQL> 
SELECT start_date,
       REGEXP_SUBSTR( start_date, '[[:punct:]]\d+', 5, 2 ) AS RESULT
  FROM call_center;
START_DATE RESULT
---------- ------
1998-01-01 -01   
2000-02-21 -21   
2001-03-31 -31   
3 rows selected.

### subexpr
gSQL> 
SELECT start_date,
       REGEXP_SUBSTR( start_date, 
                      '([[:digit:]]{4})-([[:digit:]]{2})-([[:digit:]]{2})',
                      1,
                      1,
                      'i',
                      2 ) AS RESULT
  FROM call_center;
START_DATE RESULT
---------- ------
1998-01-01 01    
2000-02-21 02    
2001-03-31 03    
3 rows selected.

REGR_AVGX() OVER

Syntax

REGR_AVGX( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_AVGX is a linear regression function.
It calculates the average value of the independent variable expr2 from the least-squares-fit linear equation of the (X, Y) data set.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

AVG( expr2 )

The returned type is a numeric type.

The returned value is the result of the calculation or NULL.
If all records are excluded from the target because expr1 or expr2 is NULL, then it returns NULL.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       REGR_AVGX( price, year ) OVER ( PARTITION BY item_no )
       AS "REGR_AVGX(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_AVGX(price,year)
------- ----- ---- ---------------------
   3758 15000 2000                  2003
   3758 14700 2001                  2003
   3758 15300 2002                  2003
   3758 15200 2003                  2003
   3758 15100 2004                  2003
   3758 15300 2005                  2003
   3758 15350 2006                  2003

7 rows selected.

REGR_AVGY() OVER

Syntax

REGR_AVGY( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_AVGY is a linear regression function. 
It calculates the average value of the dependent variable expr1 from the least-squares-fit linear equation of the (X, Y) data set.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

AVG( expr1 )

The returned type is a numeric type.

The returned value is the result of the calculation or NULL. 
If all records are excluded from the target because expr1 or expr2 is NULL, then it returns NULL.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       round( REGR_AVGY( price, year ) OVER ( PARTITION BY item_no ), 5 )
       AS "REGR_AVGY(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_AVGY(price,year)
------- ----- ---- ---------------------
   3758 15000 2000           15135.71429
   3758 14700 2001           15135.71429
   3758 15300 2002           15135.71429
   3758 15200 2003           15135.71429
   3758 15100 2004           15135.71429
   3758 15300 2005           15135.71429
   3758 15350 2006           15135.71429

7 rows selected.

REGR_COUNT() OVER

Syntax

REGR_COUNT( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_COUNT is a linear regression function. 
It calculates the least-squares-fit linear equation of the (X, Y) data set.

It returns the number of ( X, Y ) pairs, the execution targets of the linear equation, both of which are not NULL.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The returned type is a numeric type.

It returns the number of pairs where both expr1 and expr2 are not NULL. 
If all records are excluded from the target because expr1 or expr2 is NULL, then it returns 0.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       REGR_COUNT( price, year ) OVER ( PARTITION BY item_no )
       AS "REGR_COUNT(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_COUNT(price,year)
------- ----- ---- ----------------------
   3758 15000 2000                      7
   3758 14700 2001                      7
   3758 15300 2002                      7
   3758 15200 2003                      7
   3758 15100 2004                      7
   3758 15300 2005                      7
   3758 15350 2006                      7

7 rows selected.

REGR_INTERCEPT() OVER

Syntax

REGR_INTERCEPT( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_INTERCEPT is a linear regression function. 
It calculates the y-intercept of the (X, Y) data set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

AVG( expr1 ) - REGR_SLOPE( expr1, expr2 ) * AVG( expr2 )

The returned type is a numeric type.

The returned value is the result of the calculation or NULL.

It returns NULL in the following cases:
• When all records are excluded from the target because expr1 or expr2 is NULL.
• When the result of REGR_SLOPE is null.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       REGR_INTERCEPT( price, year ) OVER ( PARTITION BY item_no )
       AS "REGR_INTERCEPT(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_INTERCEPT(price,year)
------- ----- ---- --------------------------
   3758 15000 2000                  -131512.5
   3758 14700 2001                  -131512.5
   3758 15300 2002                  -131512.5
   3758 15200 2003                  -131512.5
   3758 15100 2004                  -131512.5
   3758 15300 2005                  -131512.5
   3758 15350 2006                  -131512.5

7 rows selected.

REGR_R2() OVER

Syntax

REGR_R2( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_R2 is a linear regression function. 
It calculates the coefficient of determination (R-squared or fitness) of the (X, Y) data set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

If VAR_POP( expr2 ) = 0, then it is NULL. 
If VAR_POP( expr1 ) = 0 and VAR_POP( expr2 ) != 0, then it is 1.
If VAR_POP( expr1 ) > 0 and VAR_POP( expr2 ) != 0, then it is POWER( CORR( expr1, expr2), 2 ).

The returned type is a numeric type.

The returned value is the result of the calculation or NULL.

It returns NULL in the following cases:
• When all records are excluded from the target because expr1 or expr2 is NULL
• When the result of VAR_POP (expr2) is 0.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       round( REGR_R2( price, year ) OVER ( PARTITION BY item_no ), 10 )
       AS "REGR_R2(price,year)"
  FROM store
 WHERE item_no = 3758;

ITEM_NO PRICE YEAR REGR_R2(price,year)
------- ----- ---- -------------------
   3758 15000 2000         .4786446469
   3758 14700 2001         .4786446469
   3758 15300 2002         .4786446469
   3758 15200 2003         .4786446469
   3758 15100 2004         .4786446469
   3758 15300 2005         .4786446469
   3758 15350 2006         .4786446469

7 rows selected.

REGR_SLOPE() OVER

Syntax

REGR_SLOPE( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_SLOPE is a linear regression function. 
It calculates the slope of the (X, Y) data set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

COVAR_POP(expr1, expr2) / VAR_POP(expr2)

The returned type is a numeric type.

The returned value is the result of the calculation or NULL.

It returns NULL in the following cases:
• When all records are excluded from the target because expr1 or expr2 is NULL
• When the result of VAR_POP is 0.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       round( REGR_SLOPE( price, year ) OVER ( PARTITION BY item_no ), 10 )
       AS "REGR_SLOPE(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_SLOPE(price,year)
------- ----- ---- ----------------------
   3758 15000 2000          73.2142857143
   3758 14700 2001          73.2142857143
   3758 15300 2002          73.2142857143
   3758 15200 2003          73.2142857143
   3758 15100 2004          73.2142857143
   3758 15300 2005          73.2142857143
   3758 15350 2006          73.2142857143

7 rows selected.

REGR_SXX() OVER

Syntax

REGR_SXX( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_SXX is a linear regression function. 
It is an auxiliary function that calculates the diagnostic statistics of the (X, Y) data set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

REGR_COUNT( expr1, expr2 ) * VAR_POP( expr2 )

The returned type is a numeric type.

The returned value is the result of the calculation or NULL. 
If all records are excluded from the target because expr1 or expr2 is NULL, then it returns NULL.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       REGR_SXX( price, year ) OVER ( PARTITION BY item_no )
       AS "REGR_SXX(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_SXX(price,year)
------- ----- ---- --------------------
   3758 15000 2000                   28
   3758 14700 2001                   28
   3758 15300 2002                   28
   3758 15200 2003                   28
   3758 15100 2004                   28
   3758 15300 2005                   28
   3758 15350 2006                   28

7 rows selected.

REGR_SXY() OVER

Syntax

REGR_SXY( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_SXY is a linear regression function. 
It is an auxiliary function that calculates the diagnostic statistics of the (X, Y) data set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are of numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

REGR_COUNT( expr1, expr2 ) * COVAR_POP( expr1, expr2 )

The returned type is a numeric type.

The returned value is the result of the calculation or NULL. 
If all records are excluded from the target because expr1 or expr2 is NULL, then it returns NULL.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       REGR_SXY( price, year ) OVER ( PARTITION BY item_no )
       AS "REGR_SXY(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_SXY(price,year)
------- ----- ---- --------------------
   3758 15000 2000                 2050
   3758 14700 2001                 2050
   3758 15300 2002                 2050
   3758 15200 2003                 2050
   3758 15100 2004                 2050
   3758 15300 2005                 2050
   3758 15350 2006                 2050

7 rows selected.

REGR_SYY() OVER

Syntax

REGR_SYY( expr1, expr2 ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function REGR_SYY is a linear regression function. 
It is an auxiliary function that calculates the diagnostic statistics of the (X, Y) data set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeric types.

expr1 is the dependent variable ( y ), and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, it will be excluded from the target.

The following is the calculation formula.

REGR_COUNT( expr1, expr2 ) * VAR_POP( expr1 )

The returned type is a numeric type.

The returned value is the result of the calculation or NULL. 
If all records are excluded from the target because expr1 or expr2 is NULL, then it returns NULL.

Example

gSQL> 
SELECT item_no,
       price,
       year,
       round( REGR_SYY( price, year ) OVER ( PARTITION BY item_no ), 4 )
       AS "REGR_SYY(price,year)"
  FROM store;

ITEM_NO PRICE YEAR REGR_SYY(price,year)
------- ----- ---- --------------------
   3758 15000 2000          313571.4286
   3758 14700 2001          313571.4286
   3758 15300 2002          313571.4286
   3758 15200 2003          313571.4286
   3758 15100 2004          313571.4286
   3758 15300 2005          313571.4286
   3758 15350 2006          313571.4286

7 rows selected.

REPEAT

Syntax

REPEAT( str, num )

Description

The string repeats str as many times as specified by num, and returns the result.

The str argument can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type such as BINARY, BINARY VARYING, or BINARY LONG VARYING.

The num argument can be a numeric data type.

If either str or num is NULL, the result will also be NULL.
If num is 0 or a negative number, the result will be NULL.

The following table describes the result types.

Result type of REPEAT
str type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT REPEAT( 'ab', 3 ) AS RESULT FROM DUAL;
RESULT
------
ababab
1 row selected.

REPLACE

Syntax

REPLACE( str, from, to )

Description

It replaces all occurrences of the from string in the string str with the to string, and returns the result.

The str argument, the from argument, and the to argument can be character data types such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If str is NULL, the result will also be NULL.
If from is NULL, the str is returned without any replacement.
If the to value is omitted or NULL, the str with from removed is returned.

The following table describes the result types.

Result type of REPLACE
str type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR

Example

gSQL> SELECT REPLACE( 'HI GLIESE', 'HI', 'HELLO' ) AS RESULT FROM DUAL;
RESULT      
------------
HELLO GLIESE
1 row selected.

REVERSE

Syntax

REVERSE( str )

Description

REVERSE returns the characters of str in reverse order.

The str argument can be of types that are convertible to a character string type or a binary string type.
A character string type is processed in character units, while a binary string type is processed in byte units.

If str is NULL, then it returns NULL.

The following table describes the argument and result types.

Argument and result type of REVERSE
str	Result type
CHAR	CHAR
VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY	BINARY
VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT REVERSE( 'GOLDILOCKS' ) AS RESULT FROM DUAL;

RESULT      
----------
SKCOLIDLOG

1 row selected.

gSQL> SELECT REVERSE( '선재소프트 2018' ) AS RESULT FROM DUAL;

RESULT         
---------------
8102 트프소재선

1 row selected.

ROUND( number )

Syntax

ROUND( num [, scale ] )

Description

It rounds off num based on the scale, and returns the result.

The num argument and scale argument can be numeric data types.

If scale is omitted, the scale becomes 0 and the function is executed as if it were ROUND(num, 0).
If scale is a positive number, it is rounded based on the number of digits to the right of the decimal point.
If scale is a negative number, it is rounded based on the number of digits to the left of the decimal point.

If either num or scale is NULL, it returns NULL.

Example

gSQL> SELECT ROUND( 152.4282, 2 ) AS RESULT FROM DUAL;
RESULT
------
152.43
1 row selected.

gSQL> SELECT ROUND( 152.4282, -2 ) AS RESULT FROM DUAL;
RESULT
------
   200
1 row selected.

ROUND( date )

Syntax

ROUND( date [ , fmt ] )

Description

It rounds off the date in the specified fmt unit and returns the result.

The data type of the date argument can be DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE. 
The fmt argument can be a character type such as CHARACTER or CHARACTER VARYING. 
If either the date argument or the fmt argument is NULL, the result will also be NULL.

The result type is always DATE, regardless of the date argument's data type.

If fmt is omitted, the default is DAY.
The following table describes the available format strings.

Available format sting of fmt
String	Description
CC, SCC	It represents the year in four digits, rounded off starting from the 51st year. (e.g., XX01)
YYYY, YEAR, SYYYY, SYEAR, YYY, YY, Y	It is rounded off starting from July 1st.
IYYY, IYY, IY, I	It represents the year that embraces the calendar week defined by ISO 8601 standards, rounded off starting from July 1st.
Q	It is rounded off starting from the 16th day of the second month of the quarter.
MONTH, MON, MM, RM	It is rounded off starting from the 16th day.
WW	A week starts from January 1st of the year, and it is rounded off on Wednesday at 12 p.m. of the WEEK.
IW	It represents the calendar week defined by ISO 8601 standards (1 ~ 52 weeks or 1 ~ 53 weeks), rounded off on Thursday 12 p.m.
W	A week starts from the 1st day of the month, and it is rounded off on Wednesday 12 p.m. of the WEEK.
DDD, DD, J	It is rounded off at 12 p.m.
DAY, DY, D	It is rounded off on Wednesday at 12 p.m. of the WEEK.
HH, HH12, HH24	It is rounded off from 30 minutes.
MI	It is rounded off from 30 seconds.

Example

gSQL> SELECT 
      ROUND( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'CC' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2101-01-01
1 row selected.

gSQL> SELECT 
      ROUND( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'YYYY' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2052-01-01
1 row selected.

gSQL> SELECT 
      ROUND( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'MONTH' ) AS   RESULT 
      FROM DUAL;
RESULT    
----------
2051-08-01
1 row selected.

gSQL> SELECT 
      ROUND( TO_TIMESTAMP( '2001-05-05 15:22:33.999999', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ) ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2001-05-06
1 row selected.

ROW_NUMBER() OVER

Syntax

ROW_NUMBER( ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function ROW_NUMBER assigns a unique number to each row.
The unique number is a consecutive integer starting from 1.

A window frame cannot be used.

Example

gSQL> SELECT department_id, salary,
             ROW_NUMBER() OVER ( ORDER BY salary ) AS row_num
      FROM employees
      WHERE department_id = 60;

DEPARTMENT_ID SALARY ROW_NUM
------------- ------ -------
           60   4200       1
           60   4800       2
           60   4800       3
           60   6000       4
           60   9000       5

5 rows selected.

ROWID_GRID_BLOCK_ID

Syntax

ROWID_GRID_BLOCK_ID( rowid )

Description

It returns the GRID block ID.

It is valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_GRID_BLOCK_ID( ROWID ) FROM T1;
C1 ROWID_GRID_BLOCK_ID( ROWID )
-- ----------------------------
 1                           52
 2                           52
 3                           52

3 rows selected.

ROWID_GRID_BLOCK_SEQ

Syntax

ROWID_GRID_BLOCK_SEQ( rowid )

Description

It returns the GRID block sequence.

It is valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_GRID_BLOCK_SEQ( ROWID ) FROM T1;
C1 ROWID_GRID_BLOCK_SEQ( ROWID )
-- -----------------------------
 1                        747465
 2                        747466
 3                        747467

3 rows selected.

ROWID_MEMBER_ID

Syntax

ROWID_MEMBER_ID( rowid )

Description

It retrieves the member ID.

It is valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_MEMBER_ID( ROWID ) FROM T1;
C1 ROWID_MEMBER_ID( ROWID )
-- ------------------------
 1                        1
 2                        1
 3                        1

3 rows selected.

ROWID_OBJECT_ID

Syntax

ROWID_OBJECT_ID( rowid )

Description

It retrieves the object ID.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_OBJECT_ID( t1.ROWID ) FROM t1;
ROWID_OBJECT_ID( T1.ROWID )
---------------------------
                      22012
                      22012
                      22012
                      22012
4 rows selected.

ROWID_PAGE_ID

Syntax

ROWID_PAGE_ID( rowid )

Description

It retrieves the page ID.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_PAGE_ID( t1.ROWID ) FROM t1;
ROWID_PAGE_ID( T1.ROWID )
-------------------------
                     8227
                     8227
                     8227
                     8227
4 rows selected.

ROWID_ROW_NUMBER

Syntax

ROWID_ROW_NUMBER( rowid )

Description

It retrieves the row number.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_ROW_NUMBER( t1.ROWID ) FROM t1;
ROWID_ROW_NUMBER( T1.ROWID )
----------------------------
                           0
                           1
                           2
                           3
4 rows selected.

ROWID_SHARD_ID

Syntax

ROWID_SHARD_ID( rowid )

Description

It retrieves the shard ID.

It is valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_SHARD_ID( ROWID ) FROM T1;
C1 ROWID_SHARD_ID( ROWID )
-- -----------------------
 1                       0
 2                       1
 3                       2

3 rows selected.

ROWID_TABLESPACE_ID

Syntax

ROWID_TABLESPACE_ID( rowid )

Description

It retrieves the tablespace ID.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_TABLESPACE_ID( t1.ROWID ) FROM t1;
ROWID_TABLESPACE_ID( T1.ROWID )
-------------------------------
                              2
                              2
                              2
                              2
4 rows selected.

ROWNUM

Syntax

ROWNUM

Description

It sequentially allocates numbers starting from 1 to the rows that satisfy the WHERE condition.

It allows the use of ROWNUM in the WHERE clause for compatibility with Oracle.

However, to limit the number of query results, it is recommended to use the offset limit clause (the SQL standard) as follows.

Describing the number of results using (non-standard) ROWNUM

gSQL> SELECT * FROM t1 WHERE ROWNUM <= 3;

C1
--
A 
B 
C 

3 rows selected.

Describing the number of results using (SQL-standard) FETCH statement

gSQL> SELECT * FROM t1 FETCH 3;

C1
--
A 
B 
C 

3 rows selected.

To restrict the range of query results, it is recommended to use the OFFSET and FETCH statements as follows.

Describing the range of result counts using (non-standard) ROWNUM

gSQL> SELECT c1 
        FROM ( SELECT ROWNUM rn, c1 
                 FROM t1 )
        WHERE rn BETWEEN 2 AND 3;

C1
--
B 
C 

2 rows selected.

Describing the range of result counts using (SQL standard) ROWNUM OFFSET and FETCH statements

gSQL> SELECT c1 FROM t1 OFFSET 1 FETCH 2;

C1
--
B 
C 

2 rows selected.

It is not recommended to use ROWNUM in the WHERE clause for purposes other than restricting the number of results.

The results for the same query may vary depending on the execution method when using the ambiguous condition (WHERE c1 < ROWNUM + 3), as shown below.

Creating the data

CREATE TABLE t1 ( c1 INTEGER );
CREATE INDEX t1_idx ON t1(c1);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (5);
COMMIT;

In the case of Oracle

SQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

    ROWNUM       C1
---------- ----------
     1        1
     2        2

SQL> DROP INDEX t1_idx;

N/A
- The index has been deleted.

SQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

    ROWNUM       C1
---------- ----------
     1        1
     2        2
     3        3
     4        4
     5        5

In the case of GOLDILOCKS

gSQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

ROWNUM C1
------ --
     1  1
     2  2
     3  3
     4  4
     5  5

5 rows selected.

gSQL> DROP INDEX t1_idx;

Index dropped.

gSQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

ROWNUM C1
------ --
     1  1
     2  2
     3  3
     4  4
     5  5

5 rows selected.

Example

gSQL> SELECT ROWNUM, c1 FROM t1;

ROWNUM C1
------ --
     1 A 
     2 B 
     3 C 
     4 D 
     5 E 

5 rows selected.

RPAD

Syntax

RPAD( str, length, [, fill] )

Description

It adds a fill string to the right side of str until the string's length reaches the specified length, and then returns the result.

The str argument can be a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONGVARYING, or a binary character type, such as BINARY, BINARY VARYING, BINARY LONG VARYING.

The length argument can be a numeric type.

length refers to the number of characters, with its maximum range being the maximum PRECISION of the result type. 
If fill is omitted, a whitespace is added by default.
If str is longer than length, it will be truncated to fit the specified length before being returned.
If any of str, length, or fill is NULL, the result will also be NULL. 
If length is 0 or a negative number, the result will also be NULL.

The following table describes the result types.

Result type of RPAD
str type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT RPAD('AA', 5) AS RESULT1,
             RPAD('AA', 5, 'X') AS RESULT2,
             RPAD('AA', 1) AS RESULT3
      FROM DUAL; 
RESULT1 RESULT2 RESULT3
------- ------- -------
AA      AAXXX   A      
1 row selected.

RTRIM

Syntax

RTRIM( trim_source [, trim_character ] )

Description

It removes matching characters by comparing from the right side of trim_character in trim_source, continuing until no matching character is found. Then, it returns the result.

The trim_character and trim_source arguments can be of a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type, such as BINARY, BINARY VARYING, BINARY LONG VARYING.

If either trim_character or trim_source is NULL, the result will also be NULL.
If trim_character is omitted, a single blank space (' ') is specified by default.

The following table describes the result types.

Result type of RTRIM
trim_source type, trim_character type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT RTRIM('      rtrim      ') AS RESULT1,
             RTRIM('______rtrim______','_') AS RESULT2
      FROM DUAL;
RESULT1     RESULT2    
----------- -----------
      rtrim ______rtrim
1 row selected.

SESSION_ID

Syntax

SESSION_ID()

Description

It retrieves the current session ID.

Example

gSQL> SELECT SESSION_ID() FROM dual;

SESSION_ID()
------------
           4

1 row selected.

SESSION_SERIAL

Syntax

SESSION_SERIAL()

Description

It retrieves the serial number of the current session.

Example

gSQL> SELECT SESSION_SERIAL() FROM dual;

SESSION_SERIAL()
----------------
              16

1 row selected.

SESSION_USER

Syntax

SESSION_USER[()]

Description

It returns the session's user.

User information is managed in three types, as follows.

Logon user: It is a user who performed login, and it is maintained until the connection is closed.
Session user: It is the same as the first logon user, but it can be changed using the SET SESSION AUTHORIZATION statement.
Current user: It is generally the same as the session user, but it is temporarily changed internally in system to control access when using the PSM, view.
- The session user and current user is similar to the difference between the unix system's real user and the effective user.

Example

% gsql sys gliese
gSQL>  SET SESSION AUTHORIZATION test;

Session set.

gSQL> SELECT LOGON_USER() AS result1, SESSION_USER() AS result2 FROM DUAL;

RESULT1 RESULT2
------- -------
SYS     TEST   

1 row selected.

SESSIONTIMEZONE

Syntax

SESSIONTIMEZONE()

Description

SESSIONTIMEZONE returns the time zone of the current session. 
The returned value is in the '[+|-]TZH:TZM' format.
The returned type is varchar.

Example

Check the time zone of the current session.

gSQL> SELECT SESSIONTIMEZONE() FROM dual;

SESSIONTIMEZONE()
-----------------
+09:00           

1 row selected.

When modifying the time zone of the current session, it returns the updated time zone value.

gSQL> SET TIME ZONE '-05:00';

Session set.

gSQL> SELECT SESSIONTIMEZONE() FROM dual;

SESSIONTIMEZONE()
-----------------
-05:00           

1 row selected.

SHARD_GROUP_ID

Syntax

SHARD_GROUP_ID( table_name, shard_key_value [, ... ] )

Description

It returns the group ID managing the shard that stores the shard_key_value when the shard strategy is defined in table_name.

The table_name (an input argument) must be specified as an identifier. If the object corresponding to the table_name is not a base table, or if the shard strategy is not defined, an error will occur.

The shard_key_value (an input argument) must be listed in the same order as the shard key columns in the shard strategy defined in table_name. If the number of shard_key_value entries does not match the number of shard key columns, an error will occur.

The result type is NATIVE_BIGINT.

It is valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_GROUP_ID( T1, T1.C1 ) FROM T1;
C1 SHARD_GROUP_ID( T1, T1.C1 )
-- ---------------------------
A                            1
B                            2
C                            3

3 rows selected.


gSQL> SELECT SHARD_GROUP_ID( T1, 'B' ) FROM DUAL;
SHARD_GROUP_ID( T1, 'B' )
-------------------------
                        2

1 row selected.

SHARD_GROUP_NAME

Syntax

SHARD_GROUP_NAME( table_name, shard_key_value [, ... ] )

Description

It returns the group NAME managing the shard that stores the shard_key_value when the shard strategy is defined in table_name.

The table_name (an input argument) must be specified as an identifier. If the object corresponding to the table_name is not a base table, or if the shard strategy is not defined, an error will occur.

The shard_key_value (an input argument) must be listed in the same order as the shard key columns in the shard strategy defined in table_name. If the number of shard_key_value entries does not match the number of shard key columns, an error will occur.

The result type is VARCHAR.

It is valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_GROUP_NAME( T1, T1.C1 ) FROM T1;

C1 SHARD_GROUP_NAME( T1, T1.C1 )
-- -----------------------------
A  G1                           
B  G2                           
C  G3                           

3 rows selected.

gSQL> SELECT SHARD_GROUP_NAME( T1, 'B' ) FROM DUAL;

SHARD_GROUP_NAME( T1, 'B' )
---------------------------
G2                         

1 row selected.

SHARD_ID

Syntax

SHARD_ID( table_name, shard_key_value [, ... ] )

Description

It returns the ID of the shard that stores the shard_key_value when the shard strategy is defined in table_name.

The table_name (an input argument) must be specified as an identifier. If the object corresponding to the table_name is not a base table, or if the shard strategy is not defined, an error will occur.

The shard_key_value (an input argument) must be listed in the same order as the shard key columns in the shard strategy defined in table_name. If the number of shard_key_value entries does not match the number of shard key columns, an error will occur.

The result type is NATIVE_BIGINT.

It is valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_ID( T1, T1.C1 ) FROM T1;
C1 SHARD_ID( T1, T1.C1 )
-- ---------------------
A                      0
B                      1
C                      2

3 rows selected.


gSQL> SELECT SHARD_ID( T1, 'B' ) FROM DUAL;
SHARD_ID( T1, 'B' )
-------------------
                  1

1 row selected.

SHARD_NAME

Syntax

SHARD_NAME( table_name, shard_key_value [, ... ] )

Description

It returns the NAME of the shard that stores the shard_key_value when the shard strategy is defined in table_name.

The table_name (an input argument) must be specified as an identifier. If the object corresponding to the table_name is not a base table, or if the shard strategy is not defined, an error will occur.

The shard_key_value (an input argument) must be listed in the same order as the shard key columns in the shard strategy defined in table_name. If the number of shard_key_value entries does not match the number of shard key columns, an error will occur.

The result type is VARCHAR.

It is valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_NAME( T1, T1.C1 ) FROM T1;

C1 SHARD_NAME( T1, T1.C1 )
-- -----------------------
A  S1                     
B  S2                     
C  S3                     

3 rows selected.

gSQL> SELECT SHARD_NAME( T1, 'B' ) FROM DUAL;

SHARD_NAME( T1, 'B' )
---------------------
S2                   

1 row selected.

SHIFT_LEFT

Syntax

SHIFT_LEFT( num, cnt )

Description

It returns the value obtained by shifting num to the left by cnt bits.

The data types of the input num and cnt arguments can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or any type that can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT, the decimal point is truncated.

cnt is masked with 6 bits and is processed to a value within the 6-bit range.

If either num or cnt is NULL, the result will also be NULL.

The result type is NATIVE_BIGINT.

Example

gSQL> SELECT SHIFT_LEFT(1, 3) FROM DUAL;
SHIFT_LEFT(1, 3)
----------------
               8
1 row selected.

SHIFT_RIGHT

Syntax

SHIFT_RIGHT( num, cnt )

Description

It returns the value obtained by shifting num to the right by cnt bits.

The data types of the input num and cnt arguments can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or any type that can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT type, the decimal point is truncated.

cnt is masked with 6 bits and is processed to a value within the 6-bit range.

If either num or cnt is NULL, the result will also be NULL.

The result type is NATIVE_BIGINT.

Example

gSQL> SELECT SHIFT_RIGHT(8, 3) FROM DUAL;
SHIFT_RIGHT(8, 3)
-----------------
                1
1 row selected.

SIGN

Syntax

SIGN( num )

Description

It returns the sign of num.

The num argument can be of a numeric data type.

The return value is as follows. 
• If num < 0,  -1 is returned.
• If num = 0, 0 is returned.
• If num > 0, 1 is returned.

If num is NULL, the result will also be NULL.

Example

gSQL> SELECT SIGN(-10) AS RESULT1, 
             SIGN(0) AS RESULT2, 
             SIGN(10) AS RESULT3 FROM DUAL;
RESULT1 RESULT2 RESULT3
------- ------- -------
     -1       0       1
1 row selected.

SIN

Syntax

SIN( num )

Description

It returns the sine value of num.
If num is NULL, the result will also be NULL.

Example

gSQL> SELECT SIN( 0 ) AS RESULT FROM DUAL;
RESULT
------
     0
1 row selected.

SPLIT_PART

Syntax

SPLIT_PART( string, delimiter, field )

Description

It returns a string of the field, using the specified delimiter character within the string.

The data types of the string and delimiter arguments can be character data types, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

The field argument can be a numeric data type.

If either string, delimiter, or field is NULL, the result will also be NULL. The value of field must be a numeric value greater than 1; if it is 0 or a negative number, an error will be returned.

The following table describes the result types.

Result type of SPLIT_PART
string type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR

Example

gSQL> SELECT SPLIT_PART( 'AB;CD;EF;GH', ';', 3  ) AS RESULT FROM DUAL;
RESULT
------
EF    
1 row selected.

SQRT

Syntax

SQRT( num )

Description

It returns the square root of num.

The num argument can be a numeric type and must be a value greater than or equal to 0 (non-negative).
If the num argument is NULL, the result will also be NULL.

Example

gSQL> SELECT SQRT( 9 ) AS RESULT FROM DUAL;
RESULT
------
     3
1 row selected.

STATEMENT_DATE

Syntax

STATEMENT_DATE()
CURRENT_DATE [()]

Description

It retrieves the current date (of DATE type).

The differences among the functions for obtaining the current date are as follows.

• TRANSACTION_DATE(): All date values within the transaction are the same.
• STATEMENT_DATE(): All date values within an SQL statement are the same.
• CLOCK_DATE(): Whenever the function is called, the current date value is returned.

Example

gSQL> SELECT STATEMENT_DATE() AS result FROM t1;

RESULT    
----------
2013-12-12
2013-12-12
2013-12-12

3 rows selected.

STATEMENT_LOCALTIME

Syntax

STATEMENT_LOCALTIME()
LOCALTIME [()]

Description

It retrieves the current value of the TIME WITHOUT TIME ZONE type based on the session time.

LOCALTIME is an SQL standard function.

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_LOCALTIME(): All time values within the transaction are the same.
• TATEMENT_LOCALTIME(): All time values within an SQL statement are the same.
• CLOCK_LOCALTIME(): Whenever the function is called, the current time value is returned.

Example

All rows have the same time value.

gSQL> SELECT STATEMENT_LOCALTIME() AS result FROM t1;

RESULT         
---------------
16:18:50.775870
16:18:50.775870
16:18:50.775870

3 rows selected.

STATEMENT_LOCALTIMESTAMP

Syntax

STATEMENT_LOCALTIMESTAMP()
LOCALTIMESTAMP [()]

Description

It retrieves the current value of the TIMESTAMP WITHOUT TIME ZONE type based on the session time.

LOCALTIMESTAMP is an SQL standard function.

The differences among the functions for obtaining the current timestamp are as follows.

• TRANSACTION_LOCALTIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• STATEMENT_LOCALTIMESTAMP(): All TIMESTAMP values within an SQL statement are the same.
• CLOCK_LOCALTIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

All rows contain the same value.

gSQL> SELECT STATEMENT_LOCALTIMESTAMP() FROM t1;

STATEMENT_LOCALTIMESTAMP()
--------------------------
2013-12-12 16:23:39.782187
2013-12-12 16:23:39.782187
2013-12-12 16:23:39.782187

3 rows selected.

STATEMENT_TIME

Syntax

STATEMENT_TIME()
CURRENT_TIME [()]

Description

It retrieves the current value of the TIME WITH TIME ZONE type.

CURRENT_TIME is an SQL standard function.

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_TIME(): All time values within the transaction are the same.
• STATEMENT_TIME(): All time values within an SQL statement are the same.
• CLOCK_TIME(): Whenever the function is called, the current time value is returned.

Example

All rows contain the same value.

gSQL> SELECT STATEMENT_TIME() AS result FROM t1;

RESULT                
----------------------
16:28:19.268513 +09:00
16:28:19.268513 +09:00
16:28:19.268513 +09:00

3 rows selected.

STATEMENT_TIMESTAMP

Syntax

STATEMENT_TIMESTAMP()
CURRENT_TIMESTAMP [()]

Description

It retrieves the current value of the TIMESTAMP WITH TIME ZONE type.

CURRENT_TIMESTAMP is an SQL standard function.

The differences among the functions for obtaining the current timestamp are as follows.

• TRANSACTION_TIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• STATEMENT_TIMESTAMP(): All TIMESTAMP values within an SQL statement are the same.
• CLOCK_TIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

All rows contain the same value.

gSQL> SELECT STATEMENT_TIMESTAMP() AS result FROM t1;

RESULT                           
---------------------------------
2013-12-12 16:36:11.032957 +09:00
2013-12-12 16:36:11.032957 +09:00
2013-12-12 16:36:11.032957 +09:00

3 rows selected.

STATEMENT_VIEW_SCN

Syntax

STATEMENT_VIEW_SCN()

Description

It retrieves the VIEW SCN of the current STATEMENT.

Example

gSQL> SELECT STATEMENT_VIEW_SCN() FROM dual;

STATEMENT_VIEW_SCN()
--------------------
17697.658.17880     

1 row selected.

STATEMENT_VIEW_SCN_DCN

Syntax

STATEMENT_VIEW_SCN_DCN()

Description

It retrieves the Domain Change Number (DCN) value of the VIEW SCN for the current STATEMENT.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_DCN() FROM dual;

STATEMENT_VIEW_SCN_DCN()
------------------------
                     658

1 row selected.

STATEMENT_VIEW_SCN_GCN

Syntax

STATEMENT_VIEW_SCN_GCN()

Description

It retrieves the Global Change Number (GCN) value of the VIEW SCN for the current STATEMENT.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_GCN() FROM dual;

STATEMENT_VIEW_SCN_GCN()
------------------------
                   17697

1 row selected.

STATEMENT_VIEW_SCN_LCN

Syntax

STATEMENT_VIEW_SCN_LCN()

Description

It retrieves the Local Change Number (LCN) value of the VIEW SCN for the current STATEMENT.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_LCN() FROM dual;

STATEMENT_VIEW_SCN_LCN()
------------------------
                   17880

1 row selected.

STDDEV

Syntax

STDDEV( [ ALL | DISTINCT ] expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregation function, and it calculates the standard deviation of an expr set.

When ALL is specified, it is performed on all values. When DISTINCT is specified, it is performed on the values with duplicates removed. If neither is specified, it is treated as if ALL were specified.

If FILTER is specified, the aggregation is performed only for the values that satisfy the condition.

If the number of expr sets, excluding NULL values after duplicates are removed using DISTINCT, is one, it returns 0, similar to VARIANCE.

The following table describes the argument and result types.

Argument and result type of STDDEV
expr	Result type
NATIVE_INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER
NATIVE_DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

GOLDILOCKS calculates the standard deviation as follows.
　• If the number of expr sets is 1, it returns 0.
　• If the number of expr sets is greater than 1, it returns the value of STDDEV_SAMP( expr ).

The standard deviation is the positive square root of the variance, and it is obtained by calculating the square root of the variance. In other words, the STDDEV function is equivalent to the square root of the VARIANCE function.

STDDEV( [ ALL ] expr )

= SQRT( VARIANCE( [ ALL ] expr ) )

STDDEV( DISTINCT expr )

= SQRT( VARIANCE( DISTINCT expr ) )

Example

gSQL> SELECT STDDEV(c1) FROM t1;

      STDDEV(C1)
----------------
11.4978258814438

1 row selected.


gSQL> SELECT STDDEV(ALL c1) FROM t1;

  STDDEV(ALL C1)
----------------
11.4978258814438

1 row selected.


gSQL> SELECT STDDEV(DISTINCT c1) FROM t1;

STDDEV(DISTINCT C1)
-------------------
   13.2759180473518

1 row selected.


gSQL> SELECT STDDEV(c1) FILTER( WHERE c1 > 0 ) FROM t1;

      STDDEV(C1)
----------------
11.4978258814438

1 row selected.

STDDEV() OVER

Syntax

STDDEV ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function STDDEV calculates the standard deviation of expr. 
If the number of expr values, excluding NULLs, is one, it returns 0 as the result.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , STDDEV( min_price ) OVER ( ORDER BY min_price ) AS "STDDEV"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE           STDDEV
--------- ----------------
       73                0
      247 123.036579926459
      731 340.953564775811
     null 340.953564775811
     null 340.953564775811

5 rows selected.

STDDEV_POP

Syntax

STDDEV_POP( expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregation function, and it calculates the population standard deviation of an expr set. 
If the number of expr set values, excluding NULLs, is one, it returns 0.

If FILTER is specified, the aggregation is performed only for the values that satisfy the condition.

The following table describes the argument and result types.

Argument and result type of STDDEV_POP
expr	Result type
NATIVE_INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER
NATIVE_DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

The population standard deviation is the positive square root of the population variance, and it is obtained by calculating the square root of the population variance. In other words, the STDDEV_POP function is equivalent to the square root of the VAR_POP function.

STDDEV_POP( expr )

= SQRT( VAR_POP( expr ) )

Example

gSQL> SELECT STDDEV_POP(c1) FROM t1;

 STDDEV_POP(C1)
---------------
10.283968105746

1 row selected.


gSQL> SELECT STDDEV_POP(c1) FILTER( WHERE c1 > 0 ) FROM t1;

 STDDEV_POP(C1)
---------------
10.283968105746

1 row selected.

STDDEV_POP() OVER

Syntax

STDDEV_POP ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function STDDEV_POP calculates the population standard deviation of expr. 
If the number of expr values, excluding NULLs, is one, it returns 0 as the result.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , STDDEV_POP( min_price ) OVER ( ORDER BY min_price ) AS "STDDEV_POP"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE      STDDEV_POP
--------- ---------------
       73               0
      247              87
      731 278.38741989457
     null 278.38741989457
     null 278.38741989457

5 rows selected.

STDDEV_SAMP

Syntax

STDDEV_SAMP( expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregation function, and it calculates the sample standard deviation of an expr set. 
If the number of expr set values, excluding NULLs, is one, it returns NULL.

If FILTER is specified, the aggregation is performed only for the values that satisfy the condition.

The following table describes the argument and result types.

Argument and result type of STDDEV_SAMP
expr	Result type
NATIVE_INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER
NATIVE_DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

The sample standard deviation is the positive square root of the sample variance, and it is obtained by calculating the square root of the sample variance. In other words, the STDDEV_SAMP function is equivalent to the square root of the VAR_SAMP function.

STDDEV_SAMP( expr )

= SQRT( VAR_SAMP( expr ) )

Example

gSQL> SELECT STDDEV_SAMP(c1) FROM t1;

 STDDEV_SAMP(C1)
----------------
11.4978258814438

1 row selected.


gSQL> SELECT STDDEV_SAMP(c1) FILTER( WHERE c1 > 0 ) FROM t1;

 STDDEV_SAMP(C1)
----------------
11.4978258814438

1 row selected.

STDDEV_SAMP() OVER

Syntax

STDDEV_SAMP ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function STDDEV_SAMP calculates the sample standard deviation of expr. 
If the number of expr values, excluding NULLs, is one, it returns NULL as the result.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , STDDEV_SAMP( min_price ) OVER ( ORDER BY min_price ) AS "STDDEV_SAMP"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE      STDDEV_SAMP
--------- ----------------
       73             null
      247 123.036579926459
      731 340.953564775811
     null 340.953564775811
     null 340.953564775811

5 rows selected.

STRING_AGG() OVER

Syntax

STRING_AGG( str [, delimiter] ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function STRING_AGG connects str according to the execution range defined in the OVER clause.

If str is NULL, it will be excluded.

A delimiter is str connection delimiter, and if omitted, the default value is NULL.

str can be either a character string or a binary string. 
If str is a character string, the result type will be varchar. 
If str is a binary string, then the result type will be varbinary.

Example

gSQL> 
SELECT regionkey,
       name,
       STRING_AGG( name ) OVER ( PARTITION BY regionkey
                                 ORDER BY nationkey
                                 ROWS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW ) 
       AS "STRING_AGG( name ) OVER",
       STRING_AGG( name, ', ' ) OVER ( PARTITION BY regionkey
                                       ORDER BY nationkey
                                       ROWS BETWEEN UNBOUNDED PRECEDING
                                                AND CURRENT ROW ) 
       AS "STRING_AGG( name, ', ' ) OVER"  
  FROM nation;

REGIONKEY NAME    STRING_AGG( name ) OVER STRING_AGG( name, ', ' ) OVER
--------- ------- ----------------------- -----------------------------
        1 BRAZIL  BRAZIL                  BRAZIL                       
        1 CANADA  BRAZILCANADA            BRAZIL, CANADA               
        1 PERU    BRAZILCANADAPERU        BRAZIL, CANADA, PERU         
        1 null    BRAZILCANADAPERU        BRAZIL, CANADA, PERU         
        2 null    null                    null                         
        2 INDIA   INDIA                   INDIA                        
        2 null    INDIA                   INDIA                        
        2 null    INDIA                   INDIA                        
        2 JAPAN   INDIAJAPAN              INDIA, JAPAN                 
        2 CHINA   INDIAJAPANCHINA         INDIA, JAPAN, CHINA          
        2 null    INDIAJAPANCHINA         INDIA, JAPAN, CHINA          
        2 VIETNAM INDIAJAPANCHINAVIETNAM  INDIA, JAPAN, CHINA, VIETNAM 
        3 EGYPT   EGYPT                   EGYPT                        
        3 IRAN    EGYPTIRAN               EGYPT, IRAN                  
        3 IRAQ    EGYPTIRANIRAQ           EGYPT, IRAN, IRAQ            

15 rows selected.

SUBSTR

Syntax

SUBSTR( str FROM start_position [ FOR string_length ] )
SUBSTR( str, start_position [ , string_length ] )

Description

It is an alias of SUBSTRING.

Example

Multi-byte character set (e.g. UTF8): 1 byte character

gSQL> SELECT 
      SUBSTR( 'DATABASE MANAGEMENT SYSTEM', 10, 10 ) AS RESULT 
      FROM DUAL;
RESULT    
----------
MANAGEMENT
1 row selected.

Multi-byte character set (e.g. UTF8): 2 bytes or 3 bytes character

gSQL> SELECT SUBSTR( '“αβ≠ΑΒ”', 2, 5 ) AS RESULT FROM DUAL;
RESULT
------
αβ≠ΑΒ 
1 row selected.

SUBSTRB

Syntax

SUBSTRB( str, start_position [ , string_length ] )

Description

It extracts characters within the string_length range, starting from start_position, and returns the result for str.

This function is the same as the SUBSTRING function, except that the start_position and string_length in the SUBSTR function are calculated in byte units.

Example

Multi-byte character set (e.g. UTF8): 1 byte character

gSQL> SELECT 
      SUBSTRB( 'DATABASE MANAGEMENT SYSTEM', 10, 10 ) AS RESULT 
      FROM DUAL;
RESULT    
----------
MANAGEMENT
1 row selected.

Multi-byte character set (e.g. UTF8): 2 bytes or 3 bytes character

gSQL> SELECT SUBSTRB( '“αβ≠ΑΒ”', 4, 11 ) AS RESULT FROM DUAL;
RESULT
------
αβ≠ΑΒ 
1 row selected.

SUBSTRING

Syntax

SUBSTRING( str FROM start_position [ FOR string_length ] )  
SUBSTRING( str, start_position [ , string_length ] )

Description

It extracts characters within the string_length range, starting from start_position, and returns the result for str.

The str argument data type can be a character type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary data type, such as BINARY, BINARY VARYING, BINARY LONG VARYING.

The start_position and string_length arguments can be of a numeric data type.

If any of str, start_position, or string_length is NULL, the result will be NULL. 
Both start_position and string_length start at 1 and are calculated in character units according to the character set (not in byte units).

If start_position is 0, it is set to 1. 
If start_position is a positive number, it searches for the position forwards (to the right) from the beginning of str. 
If start_ position is a negative number, it searches for the position backwards (to the left) from the end of str. 
If string_length is omitted, characters from start_position to the last character of str are returned.

If string_length is 0 or a negative number, the result will be NULL.
If start_position > (str length), the result will be NULL.
If (str length + start_position) < 0, the result will be NULL.

It is an alias of SUBSTR.
For more information, refer to SUBSTRB.

The following table describes the result types.

Result type of SUBSTRING
str type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

Multi-byte character set (e.g. UTF8): 1 byte character

gSQL> SELECT 
      SUBSTRING( 'DATABASE MANAGEMENT SYSTEM' FROM 10 FOR 10 ) AS RESULT 
      FROM DUAL;
RESULT    
----------
MANAGEMENT
1 row selected.

Multi-byte character set (e.g. UTF8): 2 bytes or 3 bytes character

gSQL> SELECT SUBSTRING( '“αβ≠ΑΒ”' FROM 2 FOR 5 ) AS RESULT FROM DUAL;
RESULT
------
αβ≠ΑΒ 
1 row selected.

SUM

Syntax

SUM( [ ALL | DISTINCT ] expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregate function that returns the sum of the expr values.

If ALL is explicitly specified, aggregation is performed on all values.
If DISTINCT is explicitly specified, aggregation is performed on the values excluding duplicates.
If neither ALL nor DISTINCT is specified, the aggregation is performed as if ALL were specified.

If FILTER is specified, the aggregation is performed only on the values that satisfy the condition.

Example

gSQL> SELECT SUM( c1 ) FROM t1;

SUM(C1)
-------
      6

1 row selected.


gSQL> SELECT SUM( c1 ) FILTER( WHERE c1 > 1 ) FROM t1;

SUM(C1)
-------
      5

1 row selected.

SUM() OVER

Syntax

SUM ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function SUM calculates the sum of the expr values.
NULLs are excluded from the calculation.

Example

gSQL> SELECT min_price AS "MIN_PRICE"
             , SUM( min_price ) OVER ( ORDER BY min_price ) AS "SUM"
        FROM product_information
       WHERE supplier_id = 102050;

MIN_PRICE  SUM
--------- ----
       73   73
      247  320
      731 1051
     null 1051
     null 1051

5 rows selected.

SYSDATE

Syntax

SYSDATE

Description

It returns the current DATE value based on the OS time of the database server.

Example

gSQL> SELECT SYSDATE FROM t1;

SYSDATE   
----------
2013-12-12
2013-12-12
2013-12-12

3 rows selected.

SYS_EXTRACT_UTC

Syntax

SYS_EXTRACT_UTC( datetime_with_timezone )

Description

It returns the UTC (Coordinated Universal Time, formerly known as Greenwich Mean Time) value.
If the timezone is not specified, it is calculated based on the session time zone.

The data type of an input argument can be TIME, TIME WITH TIME ZONE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE.
The result type will be either TIME or TIMESTAMP.

Example

gSQL> SELECT
      SYS_EXTRACT_UTC( 
          TO_TIMESTAMP_TZ( '2017-05-25 00:00:00.000000 +09:00',
                           'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ) 
          ) AS RESULT
      FROM DUAL; 
RESULT                    
--------------------------
2017-05-24 15:00:00.000000
1 row selected.

SYSTIME

Syntax

SYSTIME

Description

It returns the current TIME WITH TIME ZONE value based on the OS time of the database server.

Example

gSQL> SELECT SYSTIME FROM t1;

SYSTIME               
----------------------
16:30:46.954941 +09:00
16:30:46.954941 +09:00
16:30:46.954941 +09:00

3 rows selected.

SYSTIMESTAMP

Syntax

SYSTIMESTAMP

Description

It returns the current TIMESTAMP WITH TIME ZONE value based on the OS time of the database server.

Example

gSQL> SELECT SYSTIMESTAMP FROM t1;

SYSTIMESTAMP                     
---------------------------------
2013-12-12 16:37:34.432241 +09:00
2013-12-12 16:37:34.432241 +09:00
2013-12-12 16:37:34.432241 +09:00

3 rows selected.

TABLE_PHYSICAL_STATS

Syntax

TABLE_PHYSICAL_STATS( [schema_name.]table_name [,sampling_ratio_value] )

Description

TABLE_PHYSICAL_STATS is a function that returns fragmentation information for the pages allocated to a table.

The input parameter table_name must be specified as an identifier, and an error is raised if the corresponding object is not a base table.

The input parameter sampling_ratio_value represents the percentage (%) of the total pages owned by the object that will be accessed for analysis. 
By randomly sampling and analyzing only a subset of pages instead of processing all pages, the operation can be performed more quickly and efficiently. 
The Used and Fragmented values in the result represent the sizes analyzed based on the sampled pages, not the total allocated pages. 
If this parameter is omitted, a default value of 100% is applied, and all pages are analyzed.
The valid range of this value is 1 to 100. An error is returned if the value is outside this range.

The result type is VARCHAR and includes the fields Page, Used, and Fragmented.
• Page: The total number of pages allocated to the table.
• Used: The size of the used space, which is the sum of the page header size and the size of the stored data. The unit is bytes.
• Fragmented: The size of the fragmented space, in bytes.

GLOBAL_DUAL can be used to retrieve this information from all nodes in the cluster.

Example

When using DUAL
- Returns the object information of the connected node.
- In a cluster environment, a cluster domain can be specified.

gSQL> SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(T1) FROM DUAL;
CLUSTER_MEMBER_NAME TABLE_PHYSICAL_STATS(T1)                     
------------------- ---------------------------------------------
G1N1                Page: 992, Used: 4935498, Fragmented: 2133312
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(PUBLIC.T1) FROM DUAL;
CLUSTER_MEMBER_NAME TABLE_PHYSICAL_STATS(PUBLIC.T1)              
------------------- ---------------------------------------------
G1N1                Page: 992, Used: 4935498, Fragmented: 2133312
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(PUBLIC.T1, 50) FROM DUAL;
CLUSTER_MEMBER_NAME TABLE_PHYSICAL_STATS(PUBLIC.T1, 50)          
------------------- ---------------------------------------------
G1N1                Page: 992, Used: 2497724, Fragmented: 1079680
1 row selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(T1) FROM DUAL@G1N1
      UNION ALL
      SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(T1) FROM DUAL@G2N1
      UNION ALL
      SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(T1) FROM DUAL@G3N1;
CLUSTER_MEMBER_NAME TABLE_PHYSICAL_STATS(T1)                        
------------------- ------------------------------------------------
G1N1                Page: 992, Used: 4935498, Fragmented: 2133312   
G2N1                Page: 2912, Used: 14806136, Fragmented: 6400000 
G3N1                Page: 5760, Used: 29611846, Fragmented: 12800000
3 rows selected.

When using GLOBAL_DUAL
- Returns the object information from all nodes in the cluster environment.
- A cluster domain can be specified.

gSQL> SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(PUBLIC.T1, 50)
        FROM GLOBAL_DUAL
      ORDER BY CLUSTER_MEMBER_NAME;
CLUSTER_MEMBER_NAME TABLE_PHYSICAL_STATS(PUBLIC.T1, 50)            
------------------- -----------------------------------------------
G1N1                Page: 992, Used: 2591000, Fragmented: 1120000  
G1N2                Page: 992, Used: 2290444, Fragmented: 990080   
G2N1                Page: 2912, Used: 7120068, Fragmented: 3077760 
G2N2                Page: 2912, Used: 7405078, Fragmented: 3200960 
G3N1                Page: 5760, Used: 15095166, Fragmented: 6525120
G3N2                Page: 5760, Used: 14494054, Fragmented: 6265280
6 rows selected.

gSQL> SELECT CLUSTER_MEMBER_NAME, TABLE_PHYSICAL_STATS(PUBLIC.T1, 50)
        FROM GLOBAL_DUAL@G1N1|G2N1|G3N1
      ORDER BY CLUSTER_MEMBER_NAME;
CLUSTER_MEMBER_NAME TABLE_PHYSICAL_STATS(PUBLIC.T1, 50)            
------------------- -----------------------------------------------
G1N1                Page: 992, Used: 2399266, Fragmented: 1037120  
G2N1                Page: 2912, Used: 7353258, Fragmented: 3178560 
G3N1                Page: 5760, Used: 14633968, Fragmented: 6325760
3 rows selected.

TAN

Syntax

TAN( num )

Description

It returns the tangent value of num in radians.
If num is NULL, the result will also be NULL.

Example

gSQL> SELECT TAN( 1 ) AS RESULT FROM DUAL;
         RESULT
---------------
1.5574077246549
1 row selected.

TO_BASE64

Syntax

TO_BASE64( str )

Description

It converts str using base64 encoding and returns the converted value.
The str argument can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, a type convertible to a character type, or a binary type such as BINARY, BINARY VARYING, or BINARY LONG VARYING.
The result type is a character type, such as CHARACTER VARYING or CHARACTER LONG VARYING.

If str is NULL, the result will also be NULL.

Base64 encoding represents 8-bit binary data using 64 characters from the ascii character set.
These 64 characters consist of  A~Z, a~z, 0~9, +, and /.

Each 6 bits are represented by a single character, and every three characters (24 bits) are represented by four characters as a unit.
If the encoded output does not fill 4 characters, the remaining space is filled with '='.
If the encoded string exceeds 76 characters, a newline is added, and it is split into multiple lines.

Use the FROM_BASE64() function to decode a base64-encoded string.
Newlines, carriage returns, tabs, and spaces are ignored during base64 decoding.

For more information, refer to FROM_BASE64.

Example

gSQL> SELECT TO_BASE64( 'abc' ), TO_BASE64( 'abcd' ) FROM DUAL;

TO_BASE64( 'abc' ) TO_BASE64( 'abcd' )
------------------ -------------------
YWJj               YWJjZA==           
1 row selected.

TO_CHAR( datetime )

Syntax

TO_CHAR( datetime [, fmt ] )

Description

It converts the datetime to a string in the specified fmt format and returns the result.

The datetime argument can be of a data type such as DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, or INTERVAL. 
The fmt argument can be a character data type, such as CHARACTER or CHARACTER VARYING.
If any argument is NULL, the result will also be NULL.

If fmt is omitted, the default format is used.
• DATE: Refer to NLS_DATE_FORMAT.
• TIMESTAMP: Refer to NLS_TIMESTAMP_FORMAT.
• TIMESTAMP WITH TIME ZONE: Refer to NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT.
• TIME: Refer to NLS_TIME_FORMAT.
• TIME WITH TIME ZONE: Refer to NLS_TIME_WITH_TIME_ZONE_FORMAT.

If the data type of the datetime argument is INTERVAL, it is converted to a string and returned, regardless of the fmt argument.

For more information about the strings that can be specified in fmt, refer to the Datetime Format String.

The result type is CHARACTER VARYING.

Example

The following is an example of when fmt is omitted, and NLS_DATE_FORMAT = 'YYYY-MM-DD'.

gSQL> SELECT 
      TO_CHAR( TO_DATE( '2012-03-15','YYYY-MM-DD' ) ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2012-03-15
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT 
      TO_CHAR( TO_DATE('2012-03-15','YYYY-MM-DD'), 'DD-MON-YY' ) AS RESULT
      FROM DUAL;
RESULT   
---------
15-MAR-12
1 row selected.

TO_CHAR( number )

Syntax

TO_CHAR( number [, fmt ] )

Description

It converts the number to a string in the specified fmt format and returns the result.

The number argument can be of a numeric data type.
The fmt argument can be of a character data type, such as CHARACTER or CHARACTER VARYING.
If fmt is omitted, all significant digits are converted to a string and returned.
For more information about the strings that can be specified in fmt, refer to the Number Format String.
If any input argument is NULL, the result will also be NULL.

The result type is CHARACTER VARYING.

Example

gSQL> SELECT TO_CHAR( 12500000 ) AS RESULT FROM DUAL;
RESULT  
--------
12500000
1 row selected.

gSQL> SELECT TO_CHAR( 12500000, 'S999,999,999' ) AS RESULT FROM DUAL;
RESULT      
------------
 +12,500,000
1 row selected.

TO_DATE

Syntax

TO_DATE( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the DATE type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
If fmt is omitted, the default format is NLS_DATE_FORMAT, and in this case, str must match the default format string.

For more information about the string that can be specified in fmt, refer to the Datetime Format String.
For more information, refer to NLS_DATE_FORMAT.

If either str or fmt is NULL, the result will also be NULL.

The result type is DATE.

Example

The following is an example of when fmt is omitted, and NLS_DATE_FORMAT = 'YYYY-MM-DD'.

gSQL> SELECT TO_DATE( '2009-07-29' ) AS RESULT FROM DUAL;
RESULT    
----------
2009-07-29
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT TO_DATE( '29-JUL-09', 'DD-MON-YY' ) AS RESULT FROM DUAL;
RESULT    
----------
2009-07-29
1 row selected.

TO_NATIVE_BIGINT

Syntax

TO_NATIVE_BIGINT( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the NATIVE_BIGINT type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either str or fmt is NULL, the result will also be NULL.  
For more information about the strings that can be specified in fmt, refer to the Number Format String.

The result type is NATIVE_BIGINT.

Example

gSQL> SELECT TO_NATIVE_BIGINT( '123.45' ) AS RESULT1,
             TO_NATIVE_BIGINT( '+123.45', 'S999.99' ) AS RESULT2
        FROM DUAL;
RESULT1 RESULT2
------- -------
    123     123
1 row selected.

TO_NATIVE_DOUBLE

Syntax

TO_NATIVE_DOUBLE( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the NATIVE_DOUBLE type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either the str or fmt is NULL, the result will also be NULL.
For more information about the string that can be specified in fmt, refer to the Number Format String.

The result type is NATIVE_DOUBLE.

Example

gSQL> SELECT TO_NATIVE_DOUBLE( '123.45' ) AS RESULT1, 
             TO_NATIVE_DOUBLE( '+123.45', 'S999.99' ) AS RESULT2 
        FROM DUAL;
RESULT1 RESULT2
------- -------
 123.45  123.45
1 row selected.

TO_NATIVE_INTEGER

Syntax

TO_NATIVE_INTEGER( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the NATIVE_INTEGER type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either the str or fmt is NULL, the result will also be NULL.
For more information about the string that can be specified in fmt, refer to the Number Format String.

The result type is NATIVE_INTEGER.

Example

gSQL> SELECT TO_NATIVE_INTEGER( '123.45' ) AS RESULT1,
             TO_NATIVE_INTEGER( '+123.45', 'S999.99' ) AS RESULT2
        FROM DUAL;
RESULT1 RESULT2
------- -------
    123     123
1 row selected.

TO_NATIVE_REAL

Syntax

TO_NATIVE_REAL( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the NATIVE_REAL type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either the str or fmt is NULL, the result will also be NULL.
For more information about the string that can be specified in fmt, refer to the Number Format String.

The result type is NATIVE_REAL.

Example

gSQL> SELECT TO_NATIVE_REAL( '123.45' ) AS RESULT1, 
             TO_NATIVE_REAL( '+123.45', 'S999.99' ) AS RESULT2 
        FROM DUAL;
RESULT1 RESULT2
------- -------
 123.45  123.45

TO_NATIVE_SMALLINT

Syntax

TO_NATIVE_SMALLINT( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the NATIVE_SMALLINT type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either the str or fmt is NULL, the result will also be NULL.
For more information about the string that can be specified in fmt, refer to the Number Format String.

The result type is NATIVE_SMALLINT.

Example

gSQL> SELECT TO_NATIVE_SMALLINT( '123.45' ) AS RESULT1,
             TO_NATIVE_SMALLINT( '+123.45', 'S999.99' ) AS RESULT2
        FROM DUAL;
RESULT1 RESULT2
------- -------
    123     123
1 row selected.

TO_NUMBER

Syntax

TO_NUMBER( str [, fmt] )

Description

It converts the string str in the specified fmt format to the NUMBER type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either the str or fmt is NULL, the result will also be NULL.
For more information about the string that can be specified in fmt, refer to the Number Format String.

The result type is NUMBER.

Example

gSQL> SELECT TO_NUMBER( '123.45' ) AS RESULT1, 
             TO_NUMBER( '+123.45', 'S999.99' ) AS RESULT2 
        FROM DUAL;
RESULT1 RESULT2
------- -------
 123.45  123.45
1 row selected.

TO_TIME

Syntax

TO_TIME( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the TIME type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIME_FORMAT, and in this case, str must match the default format string.

For more information about the string that can be specified in fmt, refer to the Datetime Format String.
For more information, refer to NLS_TIME_FORMAT.

If either str or fmt is NULL, the result will also be NULL.

The result type is TIME.

Example

The following is an example of when fmt is omitted, and NLS_TIME_FORMAT = 'HH24:MI:SS.FF6'.

gSQL> SELECT TO_TIME( '11:22:33.999999' ) AS RESULT FROM DUAL;
RESULT         
---------------
11:22:33.999999
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT 
      TO_TIME( '112233.999999/P.M.', 'HH12MISS.FF6/P.M.' ) AS RESULT 
      FROM DUAL;
RESULT         
---------------
23:22:33.999999
1 row selected.

TO_TIME_TZ

Syntax

TO_TIME_TZ( str [, fmt ] )

Description

It is an alias of TO_TIME_WITH_TIME_ZONE.
For more information, refer to NLS_TIME_WITH_TIME_ZONE_FORMAT.

Example

The following is an example of when fmt is omitted, and NLS_TIME_WITH_TIME_ZONE_FORMAT = 'HH24:MI:SS.FF6 TZH:TZM'.

gSQL> SELECT TO_TIME_TZ( '11:22:33.999999 +09:00' ) AS RESULT FROM DUAL;
RESULT                
----------------------
11:22:33.999999 +09:00
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT TO_TIME_TZ( '11:22:33.999999 +09:00 PM', 
                         'HH12:MI:SS.FF6 TZH:TZM PM' ) AS RESULT 
      FROM DUAL;
RESULT                
----------------------
23:22:33.999999 +09:00
1 row selected.

TO_TIME_WITH_TIME_ZONE

Syntax

TO_TIME_WITH_TIME_ZONE( str [, fmt ] )
TO_TIME_TZ( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the TIME WITH TIME ZONE type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIME_WITH_TIME_ZONE_FORMAT, and in this case, str must match the default format string.

For more information about the string that can be specified in fmt, refer to the Datetime Format String
For more information, refer to NLS_TIME_WITH_TIME_ZONE_FORMAT.

If either str or fmt is NULL, the result will also be NULL.

It is an alias of TO_TIME_TZ.

The result type is TIME WITH TIME ZONE.

Example

The following is an example of when fmt is omitted, and NLS_TIME_WITH_TIME_ZONE_FORMAT = 'HH24:MI:SS.FF6 TZH:TZM'.

gSQL> SELECT 
      TO_TIME_WITH_TIME_ZONE( '11:22:33.999999 +09:00' ) AS RESULT 
      FROM DUAL;
RESULT                
----------------------
11:22:33.999999 +09:00
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT 
      TO_TIME_WITH_TIME_ZONE( '11:22:33.999999 +09:00 PM', 
                              'HH12:MI:SS.FF6 TZH:TZM PM' ) 
      AS RESULT 
      FROM DUAL;
RESULT                
----------------------
23:22:33.999999 +09:00
1 row selected.

TO_TIMESTAMP

Syntax

TO_TIMESTAMP( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the TIMESTAMP type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIMESTAMP_FORMAT, and in this case, str must be the default format string.

For more information about the string that can be specified in fmt, refer to the Datetime Format String.
For more information, refer to NLS_TIMESTAMP_FORMAT.

If either str or fmt is NULL, the result will also be NULL.

The result type is TIMESTAMP.

Example

The following is an example of when fmt is omitted, and NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6'.

gSQL> SELECT 
      TO_TIMESTAMP( '2009-07-29 11:22:33.999999' ) AS RESULT 
      FROM DUAL;
RESULT                    
--------------------------
2009-07-29 11:22:33.999999
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT 
      TO_TIMESTAMP( '090729 112233999999 PM', 'YYMMDD HH12MISSFF6 PM' ) 
      AS RESULT 
      FROM DUAL;

RESULT                    
--------------------------
2009-07-29 23:22:33.999999
1 row selected.

TO_TIMESTAMP_TZ

Syntax

TO_TIMESTAMP_TZ( str [, fmt ] )

Description

It is an alias of TO_TIMESTAMP_WITH_TIME_ZONE.
For more information, refer to NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT.

Example

The following is an example of when fmt is omitted, and NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'.

gSQL> SELECT 
      TO_TIMESTAMP_TZ( '2009-07-29 11:22:33.999999 +09:00' ) AS RESULT 
      FROM DUAL;
RESULT                           
---------------------------------
2009-07-29 11:22:33.999999 +09:00
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT 
      TO_TIMESTAMP_TZ( '29-JUL-09 11:22:33.999999 +09:00',
                       'DD-MON-RR HH12:MI:SS.FF6 TZH:TZM' ) AS RESULT 
      FROM DUAL;
RESULT                           
---------------------------------
2009-07-29 11:22:33.999999 +09:00
1 row selected.

TO_TIMESTAMP_WITH_TIME_ZONE

Syntax

TO_TIMESTAMP_WITH_TIME_ZONE( str [, fmt ] )
TO_TIMESTAMP_TZ( str [, fmt ] )

Description

It converts the string str in the specified fmt format to the TIMESTAMP WITH TIME ZONE type and returns the result.

The str and fmt arguments can be of a character data type, such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT, and in this case, str must match the default format string.

For more information about the string that can be specified in fmt, refer to the  Datetime Format String.
For more information, refer to NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT.

If either str or fmt is NULL, the result will also be NULL.

It is an alias of TO_TIMESTAMP_TZ.

The result type is TIMESTAMP WITH TIME ZONE .

Example

The following is an example of when fmt is omitted, and NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'.

gSQL> SELECT 
      TO_TIMESTAMP_WITH_TIME_ZONE( '2009-07-29 11:22:33.999999 +09:00' ) 
      AS RESULT 
      FROM DUAL;
RESULT                           
---------------------------------
2009-07-29 11:22:33.999999 +09:00
1 row selected.

The following is an example of when fmt is specified.

gSQL> SELECT 
      TO_TIMESTAMP_WITH_TIME_ZONE( '29-JUL-09 11:22:33.999999 +09:00',
                                   'DD-MON-RR HH12:MI:SS.FF6 TZH:TZM' ) 
      AS RESULT 
      FROM DUAL;
RESULT                           
---------------------------------
2009-07-29 11:22:33.999999 +09:00
1 row selected.

TRANSACTION_DATE

Syntax

TRANSACTION_DATE()

Description

It returns the current date (DATE type) value based on the session time.

The differences among the functions for obtaining the current date are as follows.

• TRANSACTION_DATE(): All date values within the transaction are the same.
• STATEMENT_DATE(): All date values within an SQL statement are the same.
• CLOCK_DATE(): Whenever the function is called, the current date value is returned.

Example

All date values are always the same within a single transaction.

gSQL> SELECT TRANSACTION_DATE() FROM dual;

TRANSACTION_DATE()
------------------
2013-12-12        

1 row selected.

gSQL> SELECT TRANSACTION_DATE() FROM dual;

TRANSACTION_DATE()
------------------
2013-12-12        

1 row selected.

gSQL> COMMIT;

Commit complete.

gSQL> SELECT TRANSACTION_DATE() FROM dual;

TRANSACTION_DATE()
------------------
2013-12-13        

1 row selected.

TRANSACTION_LOCALTIME

Syntax

TRANSACTION_LOCALTIME()

Description

It returns the current TIME WITHOUT TIME ZONE value based on the session time.

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_LOCALTIME(): All time values within the transaction are the same. 
• STATEMENT_LOCALTIME(): All time values within an SQL statement are the same.
• CLOCK_LOCALTIME(): Whenever the function is called, the current time value is returned.

Example

All time values are always the same within a single transaction.

gSQL> SELECT TRANSACTION_LOCALTIME() FROM dual;

TRANSACTION_LOCALTIME()
-----------------------
16:43:24.391834        

1 row selected.

gSQL> SELECT TRANSACTION_LOCALTIME() FROM dual;

TRANSACTION_LOCALTIME()
-----------------------
16:43:24.391834        

1 row selected.

gSQL> COMMIT;

Commit complete.

gSQL> SELECT TRANSACTION_LOCALTIME() FROM dual;

TRANSACTION_LOCALTIME()
-----------------------
16:43:32.651833        

1 row selected.

TRANSACTION_LOCALTIMESTAMP

Syntax

TRANSACTION_LOCALTIMESTAMP()

Description

It returns the current TIMESTAMP WITHOUT TIME ZONE value based on the session time.

The differences among the functions for obtaining the current timestamp are as follows.

• TRANSACTION_LOCALTIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• STATEMENT_LOCALTIMESTAMP(): All TIMESTAMP values within an SQL statement are the same.
• CLOCK_LOCALTIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

All timestamp values are always the same within a single transaction.

gSQL> SELECT TRANSACTION_LOCALTIMESTAMP() FROM dual;

TRANSACTION_LOCALTIMESTAMP()
----------------------------
2013-12-12 16:43:32.651833  

1 row selected.

gSQL> SELECT TRANSACTION_LOCALTIMESTAMP() FROM dual;

TRANSACTION_LOCALTIMESTAMP()
----------------------------
2013-12-12 16:43:32.651833  

1 row selected.

gSQL> COMMIT;

Commit complete.

gSQL> SELECT TRANSACTION_LOCALTIMESTAMP() FROM dual;

TRANSACTION_LOCALTIMESTAMP()
----------------------------
2013-12-12 16:46:07.831834  

1 row selected.

TRANSACTION_TIME

Syntax

TRANSACTION_TIME()

Description

It returns the current TIME WITH TIME ZONE value based on the session time.

The differences among the functions for obtaining the current time are as follows.

• TRANSACTION_TIME(): All time values within the transaction are the same.
• STATEMENT_TIME(): All time values within an SQL statement are the same. 
• CLOCK_TIME(): Whenever the function is called, the current time value is returned.

Example

All time values are always the same within a single transaction.

gSQL> SELECT TRANSACTION_TIME() FROM dual;

TRANSACTION_TIME()    
----------------------
16:46:07.831834 +09:00

1 row selected.

gSQL> SELECT TRANSACTION_TIME() FROM dual;

TRANSACTION_TIME()    
----------------------
16:46:07.831834 +09:00

1 row selected.

gSQL> COMMIT;

Commit complete.

gSQL> SELECT TRANSACTION_TIME() FROM dual;

TRANSACTION_TIME()    
----------------------
16:48:00.691827 +09:00

1 row selected.

TRANSACTION_TIMESTAMP

Syntax

TRANSACTION_TIMESTAMP()

Description

It returns the current TIMESTAMP WITH TIME ZONE value based on the session time.

The differences among the functions for obtaining the current timestamp are as follows.

• TRANSACTION_TIMESTAMP(): All TIMESTAMP values within the transaction are the same.
• STATEMENT_TIMESTAMP(): All TIMESTAMP values within an SQL statement are the same. 
• CLOCK_TIMESTAMP(): Whenever the function is called, the current timestamp value is returned.

Example

All timestamp values are always the same within a single transaction.

gSQL> SELECT TRANSACTION_TIMESTAMP() FROM dual;

TRANSACTION_TIMESTAMP()          
---------------------------------
2013-12-12 16:48:00.691827 +09:00

1 row selected.

gSQL> SELECT TRANSACTION_TIMESTAMP() FROM dual;

TRANSACTION_TIMESTAMP()          
---------------------------------
2013-12-12 16:48:00.691827 +09:00

1 row selected.

gSQL> COMMIT;

Commit complete.

gSQL>  SELECT TRANSACTION_TIMESTAMP() FROM dual;

TRANSACTION_TIMESTAMP()          
---------------------------------
2013-12-12 16:49:26.291827 +09:00

1 row selected.

TRANSLATE

Syntax

TRANSLATE( string, from, to )

Description

It replaces characters by substituting each character in the string that matches a character from from with the corresponding character from to at the same position and returns the result.

The arguments string, from, and to can be of a CHARACTER data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.

If either string, from, or to is NULL, the result will also be NULL.

If a character in the string matches a character from from:
- If the length of from is equal to the length of to, the characters in from are replaced with the corresponding characters in to at the same positions.
- If the length of from is greater than the length of to, any characters in from beyond the length of to will be removed from the string.
- If from consists of duplicate characters, the characters in from are replaced with the corresponding characters in to at the same positions as the first occurrence of each duplicated character in from.
If no character in the string matches any character from from, the string will remain unchanged.

The following table describes the result types.

Result type of TRANSLATE
string type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR

Example

When a character in the string matches one from the from set, it is replaced with the corresponding character from the to set at the same position.
- A → Z, C → Y, E → X, G → W

gSQL> SELECT TRANSLATE('ABCDEFG', 'ACEG', 'ZYXW') AS RESULT
FROM DUAL;
RESULT 
-------
ZBYDXFW
1 row selected.

If the length of the from string is longer than that of the to string, the characters in from that are beyond the length of the to string are deleted and replaced.
- A → Z, C → Y, deleting E, deleting G

gSQL> SELECT TRANSLATE('ABCDEFG', 'ACEG', 'ZY') AS RESULT
      FROM DUAL;
RESULT
------
ZBYDF 
1 row selected.

TRIM

Syntax

TRIM([ [ LEADING | TRAILING | BOTH ]  [trim_character] FROM ] trim_source)

Description

It removes the matching characters by comparing the trim_character in trim_source from the LEADING, TRAILING, or BOTH directions until no matching character remains. Then, it returns the result.

The trim_character and trim_source arguments can be of a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING or a binary character data type such as BINARY, BINARY VARYING, or BINARY LONG VARYING.

If either trim_character or trim_source is NULL, the result will also be NULL.

[ LEADING | TRAILING | BOTH ]
- LEADING: Removes trim_character from the beginning of trim_source.
- TRAILING: Removes trim_character from the end of trim_source.
- BOTH: Removes trim_character from both the beginning and the end of trim_source.
trim_character must be a single character.
If trim_character is omitted, a single blank space (' ') is specified by default.
When FROM is specified
- [ LEADING | TRAILING | BOTH ], trim_character or [ LEADING | TRAILING | BOTH ] trim_character must be specified.
  - e.g., TRIM( LEADING FROM ' abc' ) , TRIM( 'x' FROM 'xabc' ) , TRIM( LEADING 'x' FROM 'xabc' )
- If [ LEADING | TRAILING | BOTH ] is omitted, BOTH is specified by default.
When FROM is omitted.
- It is TRIM(trim_source), which behaves the same as TRIM(BOTH ' ' FROM trim_source).

The following table describes the result types.

Result type of TRIM
trim_character, trim_source type	Result type
CHAR or VARCHAR	VARCHAR
LONG VARCHAR	LONG VARCHAR
BINARY or VARBINARY	VARBINARY
LONG VARBINARY	LONG VARBINARY

Example

gSQL> SELECT TRIM( LEADING '_' FROM '___TRIM FUNCTION___' ) AS RESULT 
      FROM DUAL;
RESULT          
----------------
TRIM FUNCTION___
1 row selected.

gSQL> SELECT TRIM( TRAILING '_' FROM '___TRIM FUNCTION___' ) AS RESULT 
      FROM DUAL;
RESULT          
----------------
___TRIM FUNCTION
1 row selected.

gSQL> SELECT TRIM( BOTH '_' FROM '___TRIM FUNCTION___' ) AS RESULT 
      FROM DUAL;
RESULT       
-------------
TRIM FUNCTION
1 row selected.

TRUNC( number )

Syntax

TRUNC( num [ , scale ] )

Description

It truncates the num based on the scale and then returns the result.

The num and scale arguments can be of a numeric type.
If either the num or scale argument is NULL, the result will also be NULL.

If scale is omitted, it defaults to 0 and is executed as TRUNC(num, 0).
If scale is positive, the number is truncated based on the digits to the right of the decimal point.
If scale is negative, the number is truncated based on the digits to the left of the decimal point.

Example

gSQL> SELECT TRUNC( 142.4282, 2 ) AS RESULT FROM DUAL;
RESULT
------
142.42
1 row selected.

gSQL> SELECT TRUNC( 142.4282, -2 ) AS RESULT FROM DUAL;
RESULT
------
   100
1 row selected.

TRUNC( date )

Syntax

TRUNC( date [ , fmt ] )

Description

It returns the truncated value of the date based on the specified fmt unit.

The date argument can be of the DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE data type.
The fmt argument can be a character data type, such as CHARACTER or CHARACTER VARYING.
If either the date or fmt argument is NULL, the result will also be NULL.

The result type is always DATE, regardless of the input date type.

If fmt is omitted, the default is DAY, and the available format strings are described in the following table.

Available format strings for fmt
Format string	Description
CC, SCC	Century
YYYY, YEAR, SYYYY, SYEAR, YYY, YY, Y	Year
IYYY, IYY, IY, I	The year that accommodates the calendar week defined by the ISO 8601 standard.
Q	Quarter
MONTH, MON, MM, RM	Month
WW	The week that starts with January 1st of the year.
IW	The first week of the year is the week that contains the first Thursday, according to the calendar week (1–52 or 1–53 weeks) defined by the ISO 8601 standard.
W	The week that starts with the 1st day of the month.
DDD, DD, J	Day
DAY, DY, D	Day of the week
HH, HH12, HH24	Hour
MI	Minute

Example

gSQL> SELECT 
      TRUNC( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'CC' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2001-01-01
1 row selected.

gSQL> SELECT 
      TRUNC( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'YYYY' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2051-01-01
1 row selected.

gSQL> SELECT 
      TRUNC( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'MONTH' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2051-07-01
1 row selected.

gSQL> SELECT 
      TRUNC( TO_TIMESTAMP( '2001-05-05 11:22:33.999999',         
             'YYYY-MM-DD HH24:MI:SS.FF6' ) ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2001-05-05
1 row selected.

UPPER

Syntax

UPPER( str )

Description

It returns the uppercase version of str.

The str argument can be of a character data type, such as CHARACTER, CHARACTER VARYING, or CHARACTER LONG VARYING.
If str is NULL, the result will also be NULL.

The return type is the same as the type of the str argument.

Example

gSQL> SELECT UPPER( 'spring' ) AS RESULT FROM DUAL;
RESULT
------
SPRING
1 row selected.

UNHEX

Syntax

UNHEX( str )

Description

The str argument is a hexadecimal character, which is represented as individual bytes and returned as a binary string.

The input argument can be of a character type, such as CHARACTER VARYING or CHARACTER LONG VARYING. The result type can be of a binary character type, such as BINARY VARYING or BINARY LONG VARYING.

If str is NULL, the result will also be NULL.
If str contains a character that is not within the hexadecimal range, an error will be returned.

For more information, refer to HEX.

Example

gSQL> SELECT UNHEX( HEX( 'abc' ) ) FROM DUAL;
UNHEX( HEX( 'abc' ) )
---------------------
616263               
1 row selected.

UNHEX_TO_CHARSTR

Syntax

UNHEX_TO_CHARSTR( str )

Description

The str argument is a hexadecimal character, which is represented as individual bytes and returned as  a character string.

The input argument can be of a character type, such as CHARACTER VARYING or CHARACTER LONG VARYING. The result type can be of a character type, such as CHARACTER VARYING or CHARACTER LONG VARYING.

If str is NULL, the result will also be NULL.
If str contains a character that is not within the hexadecimal range, an error will be returned.

For more information, refer to HEX, UNHEX.

Example

gSQL> SELECT UNHEX_TO_CHARSTR( '616263' ) FROM DUAL;
UNHEX_TO_CHARSTR( '616263' )
----------------------------
abc                         
1 row selected.

gSQL> SELECT UNHEX_TO_CHARSTR( HEX( 'abc' ) ) FROM DUAL;
UNHEX_TO_CHARSTR( HEX( 'abc' ) )
--------------------------------
abc                             
1 row selected.

USER_ID

Syntax

USER_ID ()

Description

It retrieves the current user's number ID.

In a cluster system, the value may vary depending on the connected server.

It is recommended to use the CURRENT_USER function to obtain the current username.

Example

% gsql test test

gSQL> SELECT USER_ID() FROM dual;

USER_ID()
---------
        6

1 row selected.

UUID

Syntax

UUID()

Description

It generates a Universally Unique Identifier (UUID) and returns it. 
The return type is VARBINARY, consisting of 16 bytes internally.

Example

gSQL> SELECT HEX( UUID() ) FROM DUAL;
HEX( UUID() )                   
--------------------------------
E6F0A5C2387511E8B95259E479C2FD50
1 row selected.

VAR_POP

Syntax

VAR_POP( expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregation function that calculates the population variance of the expr set. 
If the number of expr sets, excluding NULL, is one, it returns 0.

If a FILTER is specified, the aggregation is performed only on the values that satisfy the condition.

The following table describes the argument and result types.

Argument and result type of VAR_POP
expr	Result type
NATIVE_INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER
NATIVE_DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

The population variance is the variance of the entire population, and variance is the average of the squared deviations. In other words, it is calculated by subtracting the population average (the average of the entire group) from each data point, squaring the results, summing them up, and dividing by the number of data points in the population.

This is used to determine how much each observation deviates from the average.

For more information, refer to STDDEV_POP.

Example

gSQL> SELECT VAR_POP(c1) FROM t1;

VAR_POP(C1)
-----------
     105.76

1 row selected.


gSQL> SELECT VAR_POP(c1) FILTER( WHERE c1 > 0 ) FROM t1;

VAR_POP(C1)
-----------
     105.76

1 row selected.

VAR_POP() OVER

Syntax

VAR_POP ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function VAR_POP calculates the population variance of expr. 
If the number of expr values, excluding NULL, is one, it returns 0 as the result.

Example

gSQL> SELECT product_id, min_price
             , VAR_POP( min_price ) OVER ( ORDER BY product_id ) AS "VAR_POP"
        FROM product_information
       WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE          VAR_POP
---------- --------- ----------------
      1769      null             null
      1770        73                0
      2378       247             7569
      2382       731 77499.5555555556
      3355      null 77499.5555555556

5 rows selected.

VAR_SAMP

Syntax

VAR_SAMP( expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregation function that calculates the sample variance of the expr set. 
If the number of expr sets, excluding NULL, is one, it returns NULL.

If a FILTER is specified, the aggregation is performed only on the values that satisfy the condition.

The following table describes the argument and result types.

Argument and result type of VAR_SAMP
expr	Result type
NATIVE_INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER
NATIVE_DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

Unlike population variance, which deals with the entire population, sample variance deals with the average and deviations of the extracted sample. In other words, it is calculated by subtracting the sample average from each data point, squaring the results, summing them up, and then dividing by the number of data points in the sample minus 1.

This is used to estimate the variance of the population.

For more information, refer to STDDEV_SAMP.

Example

gSQL> SELECT VAR_SAMP(c1) FROM t1;

VAR_SAMP(C1)
------------
       132.2

1 row selected.


gSQL> SELECT VAR_SAMP(c1) FILTER( WHERE c1 > 0 ) FROM t1;

VAR_SAMP(C1)
------------
       132.2

1 row selected.

VAR_SAMP() OVER

Syntax

VAR_SAMP ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function VAR_SAMP calculates the sample variance of expr. 
If the number of expr values, excluding NULL, is one, it returns NULL as the result.

Example

gSQL> SELECT product_id, min_price
             , VAR_SAMP( min_price ) OVER ( ORDER BY product_id ) AS "VAR_SAMP"
        FROM product_information
       WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE         VAR_SAMP
---------- --------- ----------------
      1769      null             null
      1770        73             null
      2378       247            15138
      2382       731 116249.333333333
      3355      null 116249.333333333

5 rows selected.

VARIANCE

Syntax

VARIANCE( [ ALL | DISTINCT ] expr ) [ FILTER ( [ WHERE ] condition ) ]

Description

It is an aggregation function that calculates the variance of the expr set.

If ALL is specified, the function is applied to all values. If DISTINCT is specified, the function is applied to values with duplicates removed. If neither is specified, the function behaves as if ALL were specified.

If, after removing duplicates using DISTINCT, the number of expr sets (excluding NULL) is one, the function returns 0.

If a FILTER is specified, the aggregation is performed only on the values that satisfy the condition.

The following table describes the argument and result types.

Argument and result type of VARIANCE
expr	Result type
NATIVE_INTEGER family NATIVE_SMALLINT NATIVE_INTEGER NATIVE_BIGINT	NATIVE_DOUBLE
NUMBER	NUMBER
NATIVE_DOUBLE family NATIVE_REAL NATIVE_DOUBLE	NATIVE_DOUBLE

GOLDILOCKS calculates the variance as follows:

• If the number of expr sets is 1, it returns 0.

• If the number of expr sets is greater than 1, It returns the value of VAR_SAMP ( expr ).

Example

gSQL> SELECT VARIANCE(c1) FROM t1;

VARIANCE(C1)
------------
       132.2

1 row selected.


gSQL> SELECT VARIANCE(ALL c1) FROM t1;

VARIANCE(ALL C1)
----------------
           132.2

1 row selected.


gSQL> SELECT VARIANCE(DISTINCT c1) FROM t1;

VARIANCE(DISTINCT C1)
---------------------
               176.25

1 row selected.


gSQL> SELECT VARIANCE(c1) FILTER( WHERE c1 > 0 ) FROM t1;

VARIANCE(C1)
------------
       132.2

1 row selected.

VARIANCE() OVER

Syntax

VARIANCE ( expr ) OVER < window name or specification >

For more information about < window name or specification >, refer to the window clause.

Description

The window function VARIANCE calculates the variance of expr. 
If the number of expr values, excluding NULL, is one, it returns 0 as the result.

Example

gSQL> SELECT product_id, min_price
             , VARIANCE( min_price ) OVER ( ORDER BY product_id ) AS "VARIANCE"
        FROM product_information
       WHERE supplier_id = 102050;

PRODUCT_ID MIN_PRICE         VARIANCE
---------- --------- ----------------
      1769      null             null
      1770        73                0
      2378       247            15138
      2382       731 116249.333333333
      3355      null 116249.333333333

5 rows selected.

VERSION

Syntax

VERSION()

Description

It retrieves the product's version string.

Example

gSQL> SELECT VERSION() FROM dual;

VERSION()                            
-------------------------------------
Release Name.X.X.X revision(XXXXX)

1 row selected.

WIDTH_BUCKET

Syntax

WIDTH_BUCKET( num, min, max, cnt )

Description

It creates sections with the same width as cnt within the specified min and max range, and returns the position of the section that contains num.

The data types of the num, min, max, and cnt arguments can all be numeric data types.

min, max define the range for the sections. If the min value is equal to the max value, an error is returned.
cnt represents the number of sections. The cnt must be a positive number. If cnt is 0 or a negative number, an error will be returned. 
The position of the sections is numbered starting from 1.

If any of num, min, max, or cnt is NULL, the result will also be NULL.

Example

gSQL> SELECT WIDTH_BUCKET( 5, 1, 20, 5 ) AS RESULT FROM DUAL;
RESULT
------
     2
1 row selected.