* (MULTIPLICATION)

Syntax

expr1 * expr2

Description

It returns the multiplication result of expr1 and expr2.

The multiplication types and result types are as follows.
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 the interval type.)
INTERVAL DAY TO SECOND	Numeric type	INTERVAL DAY TO SECOND (The result type is the interval type.)
Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

INTERVAL type details which is included in INTERVAL type written in the following table
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 addition result of expr1 and expr2.

The addition types and result types are as follows.
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

The + sign is displayed in expr.

Example

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

- (NEGATIVE)

Syntax

- expr

Description

The - sign is displayed in 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 subtraction result of expr1 and expr2.

The subtraction types and result types are as follows.
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 division result of expr1 and expr2.

The division types and result types are as follows.
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 interval type.)
INTERVAL DAY TO SECOND	Numeric type	INTERVAL DAY TO SECOND (The result type is 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 the string concatenating str1 and str2.

If either str1 or str2 is NULL, the string except NULL is returned. If both of str1 and str2 are NULL, NULL is returned.

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

It is an alias of CONCAT, 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 types which can be converted to number.
If num is NULL, then it 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 should be in the range of -1 to 1. 
If num is NULL, then it returns NULL.

It returns the radians value in the range of 0 and 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, then returns the result.

If any of the input argument value is NULL, the result is also NULL.
The first argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and the second argument data type can be INTERVAL or numeric.

The result type is as 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, then returns the result.

expr1 data type can be TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE TYPE, and expr2 data type can be INTERVAL DAY TO SECOND TYPE.

If expr1 or expr2 is NULL, the result is NULL.

The result type is as 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 adds as many month as the number to the date, then returns the result.
After ADD_MONTHS operation, if the date is bigger than the last day of the month, it is adjusted to the last day of the month.

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

The result type is always DATE regardless of the input argument 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.

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 can be a type which can be converted to a character type, and the return type is NUMBER.
If char is NULL, then it 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 should be in the range of -1 to 1.
If num is NULL, then it returns NULL.

It returns the radians value in the range of -pi/2 and 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.

The num value range is not limited. It returns the radians value in the range of -pi/2 and pi/2.
If num is NULL, then 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.

The num1 argument value range is not limited. It returns the radians value in the range of -pi and pi.
Either num1 or num2 is NULL, then it returns NULL.

Example

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

AVG

Syntax

AVG( [ ALL | DISTINCT ] num )

Description

It is an aggregate function, and it obtains average value of exprs.

If ALL is explicitly specified, aggregation is executed for all values.
If DISTINCT is explicitly specified, aggregation is executed for the values which exclude duplicate values.
If ALL or DISTINCT is not explicitly specified, it is processed in the same way as when ALL is specified.

Example

gSQL> SELECT AVG(c1) FROM t1;

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

1 row selected.

AVG() OVER

Syntax

AVG ( expr ) OVER < window name or specification >

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

Description

Window function AVG calculates the average value of expr.
NULL is 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 AND operation result for the bits of num1 and num2.

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT or a data type which can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT type, the decimal point is truncated.
If any of the input argument value is NULL, the result is also 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 NOT operation result for the num bit.

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

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

Example

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

BITOR

Syntax

BITOR( num1, num2 )

Description

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

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT types or a data type which can be converted to NATIVE_BIGINT type.
When converting to NATIVE_BIGINT type, the decimal point is truncated.
If any of the input argument value is NULL, the result is NULL.

The result type is NATIVE_BIGINT type.

Example

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

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

BITXOR

Syntax

BITXOR( num1, num2 )

Description

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

The input argument data type can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT or a data type which can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT type, the decimal point is truncated.
If any of the input argument value is NULL, the result is 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 str.
If str is NULL, then 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 condition in the described order.
If the comparison result is FALSE, it continues evaluating until TRUE comes up.
If the comparison result is TRUE, it returns the corresponding result, and does not evaluate any more.
If all the comparison results are FALSE, it returns the default value. If the default is omitted, it returns NULL.

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

CASE2 can be expressed by 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 is also 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 which is equal to or bigger than num.
If num is NULL, then 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 character for str according to the character set.

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

If the data type of str is CHARACTER, the 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 a character in the database character set code corresponding to num.

num is a numeric type.
If num is NULL, then 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, the current date (DATE type) value is obtained.

The differences among the functions to obtain the current date are as follows.

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

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, the current time value without TIME ZONE (TIME WITHOUT TIME ZONE type) is obtained.

The differences among the functions to obtain the current time are as follows.

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

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, the current TIMESTAMP value without TIME ZONE (TIMESTAMP WITHOUT TIME ZONE type) is obtained.

The differences among the functions to obtain the current TIMESTAMP are as follows.

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

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, the current time value with TIME ZONE (TIME WITH TIME ZONE type) is obtained.

The differences among the functions to obtain the current time are as follows.

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

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 CLOCK_TIMESTAMP() function is called, the current TIMESTAMP value with TIME ZONE (TIMESTAMP WITH TIME ZONE type) is obtained.

The differences among the functions to obtain the current TIMESTAMP are as follows.

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

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.
In the expr list, there should be two or more expr.

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

COALESCE can be expressed by 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 CONCAT function and 2 ~ 254 number of CONCATs can be set.
For more information, refer to || (CONCATENATE), 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 CONCATENATE  function and 2 ~ 254 number of CONCATENATEs can be set.
For more information, refer to CONCAT, || (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 window clause.

Description

Window function CORR calculates the coefficient of correlation for the pair of exprs.

If expr1 or expr2 is NULL, then it is excluded from the calculation.
If the number of rows for the pair of exprs is one or less, then it returns NULL as a result.

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 is also 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 is also NULL.

Example

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

COUNT

Syntax

COUNT( [ ALL | DISTINCT ] expr )

Description

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

If ALL is explicitly specified, aggregation is executed for all values.
If DISTINCT is explicitly specified, aggregation is executed for the values which exclude duplicate values.
If ALL or DISTINCT is not explicitly specified, it is processed in the same way as when ALL is specified.

Example

gSQL> SELECT COUNT(c1) FROM t1;

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

1 row selected.

COUNT() OVER

Syntax

COUNT ( expr ) OVER < window name or specification >

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

Description

Window function COUNT counts the number of rows. 
NULL is 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(*)

Description

It is an aggregate function, and the number of rows is obtained.
It has nothing to do with whether it is NULL or not because an expression is not explicitly specified.

Example

gSQL> SELECT COUNT(*) FROM t1;

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

1 row selected.

COUNT(*) OVER

Syntax

COUNT(*) OVER < window name or specification >

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

Description

Window function COUNT(*) counts the number of rows.
It does not separately specify an expression, so it is irrelevant 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 window clause.

Description

Window function COVAR_POP calculates the population covariance for the pair of exprs.

If expr1 or expr2 is NULL, then it is excluded from the calculation. 
If the number of rows for the pair of exprs is one or less, then it returns 0 as a result.

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 window clause.

Description

Window function COVAR_SAMP calculates the sample covariance for the pair of exprs.

If expr1 or expr2 is NULL, then it is excluded from the calculation. 
If the number of rows for the pair of exprs is one or less, then it returns NULL as a result.

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 window clause.

Description

Window function CUME_DIST calculates the cumulative distribution according to the relative position of the current row's value.

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

window frame is not available.

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 (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 to obtain the current date are as follows.

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

Example

gSQL> SELECT CURRENT_DATE FROM t1;

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

3 rows selected.

CURRENT_SCHEMA

Syntax

CURRENT_SCHEMA [()]

Description

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 to obtain the current time are as follows.

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

Example

All rows have 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

It obtains the TIMESTAMP WITH TIME ZONE type value based on the session time.

CURRENT_TIMESTAMP is an SQL standard function.

The differences among the functions to obtain the current TIMESTAMP are as follows.

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

Example

All rows have 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.

The 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 as same as the first logon user, but it can be changed using the SET SESSION AUTHORIZATION statement.
Current user: It is generally as 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, 
        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 should be set with 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 number to the specified datepart of date, and returns the result.

If the number is decimal point, it is not rounded off.
The date data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE.
If number or date is NULL, the result is also NULL.

The result type which is as same as the input date argument type is returned.

Available string format in 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, then returns the result to the specified datepart.

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

The result type is NUMBER.

Available string format in 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 as same as the result of the EXTRACT function. It searches for the specified field from the input datetime type, and returns it.

The field argument should be text literal, and YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE can be specified to text literal.
The datetime argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, INTERVAL.

If field is not in the range of datetime, an error is returned. 
For DATE type, field should be YEAR, MONTH, DAY, otherwise an error is returned.
If datatime is NULL, then it returns NULL.

The return type is 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 described order in DECODE statement using equal operation.
If the comparison result is FALSE, it continues evaluating until TRUE comes up.
If the comparison result is TRUE, it returns the corresponding result, and does not evaluate any more.

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

Comparing expr and comparison_expr
All expr, comparison_expr1, ..., comparison_exprN are converted to the data type of comparison_expr1 (the first comparison_expr), then they are compared.
If comparison_expr1 (the first comparison_expr) is a character type and a numeric type then it becomes the type including the range of types described in each expr, comparison_expr1, ..., comparison_exprN.
If all types described in expr, comparison_expr1, ..., comparison_exprN are CHAR, then VARCHAR type comparison is performed.

Result type
The result type becomes the data type of result1 (the first result).
If the data type of result1 (the first result) is a character type and a numeric type then it becomes the type including the range of types described in result1, ..., resultN each.
If result1 (the first result) is CHAR or NULL, then the result type is VARCHAR.

DECODE can be expressed by 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 converts a degree radians to a value in degrees, and returns the converted value.
If radians is NULL, then it returns 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 window clause.

Description

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, even when rows with the same value appear, the ranking is not skipped.

window frame is not available.

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 given type, and returns the result in VARBINARY type.

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

• Input the BINARY, VARBINARY type data in VARBINARY type.
• Input LONG VARBINARY type data in LONG VARBINARY type.
• Input LONG VARCHAR type data in LONG VARCHAR type.
• Input all other type of data after implicitly converting it to VARCHAR type.

DIGEST function supports the following hash types.

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

Use HEX function to view the result in hexadecimal character because the result is returned in VARBINARY type. In this case, the length becomes double of 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 internal representation information of expr.
Internal representation information is displayed as the data type, byte length and data information.

expr can be any data types.
If expr is NULL, then 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 squared value of e (base of natural logarithm)'s num.
If num is NULL, then 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 from an input datetime type, and returns it.

The datetime argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, INTERVAL.

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

Result of EXTRACT is as 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 multiplies the successive natural numbers from 1 to num in order, and returns the result.
If num is NULL, then 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 window clause.

Description

Window function FIRST sorts the sort specification list used in order by within KEEP clause, then returns the aggregation function value of rows whose DENSE_RANK is 1.

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

order by is not available within the window clause.
window frame is not available.

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 window clause.

Description

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 except for NULL.
If it is not specified, the default value is RESPECT NULLS.

order by is not available in window clause.
window frame is not available.

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 biggest integer which is equal to or smaller than num.
If num is NULL, then 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

The converted character by base 64 encoding is input to FROM_BASE64, then the decoded binary string is returned.

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

If str is NULL, then the result value is also NULL.
If str includes characters which are not in the range of base64 character, then it returns an error.
A newline, carriage return, tab, and space of str is ignored when 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

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

The timestamp argument should be TIMESTAMP type or the type convertible to TIMESTAMP type. 
If the timestamp argument is NULL, then the result value is also NULL.

The timezone argument should be CHARACTER type such as CHARACTER and CHARACTER VARYING, and the format is 'TZH:TZM'. 
If the timezone argument is NULL, then the result is also 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 received expr argument.

If any expr argument is NULL, the result value is NULL.

The result type becomes the data type of expr1  (the first expr). 
If the data type of expr1 (the first expr) is a character type and a numeric type then it becomes the type including the range of expr1, ..., exprN each.
If all of expr1, ..., exprN is described in CHAR type, then all exprs are compared in VARCHAR type and the result type is VARCHAR.

Example

gSQL> SELECT GREATEST( 100, 0, 200, 150, 1 ) AS RESULT FROM DUAL;
RESULT
------
   200
1 row 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 a str argument in hexadecimal character.
A str argument data type can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, a type which can be converted to a character type, or a binary character type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
The result type is a character type such as CHARACTER VARYING or CHARACTER LONG VARYING.

If str is NULL, then the result value is also NULL.

If an argument of HEX function is a numeric type, then it returns an error. 
To convert a decimal number to a hexadecimal number, use TO_CHAR() function by using  '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.

INITCAP

Syntax

INITCAP( str )

Description

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

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

Each word in string is classified by white space or characters which are not alphanumeric.
If str is NULL, the result is also NULL.

The return type is as same as str argument 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 occurrence^th substr starting from str's position, and returns its location.

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

The position argument and the occurrence argument can be numeric data type.

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

The position means the first position to search substr in str, it should not be zero, but an integer value.
  • If the position is positive: It compares forwards
     (toward the right) from the beginning of str until it finds the position of substr.
  •  If the position is negative: It compares backwards  
     (toward the left) from the end of str it finds the position of substr.
  •  If the position is 0: The result is 0.

The occurrence means the number of repeating the subtr in the str, and it should be a positive integer.

If any of the input argument is NULL, the result is also 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 specifies the data type of the function's result.
If not specified, the default is VARCHAR(4000).

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 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_ARRAYAGG is an aggregation function that concatenates value expressions and returns a single JSON array string row.

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 specifies the data type of the function's result. 
If not specified, the default is VARCHAR(4000).

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.

JSON_ARRAYAGG() OVER

Syntax

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

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 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 specifies the data type of the function's result. 
If not specified, the default is VARCHAR(4000).

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 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 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 output clause is an option that specifies the data type of the function's result. 
If not specified, the default is VARCHAR(4000).

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 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 output clause>, refer to the JSON Output Clause section.

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 output clause is an option that specifies the data type of the function's result. 
If not specified, the default is VARCHAR(4000).

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.

JSON_OBJECTAGG() OVER

Syntax

JSON_OBJECTAGG( <JSON name and value>
                [ <JSON constructor null clause> ]
                [ <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 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 output clause is an option that specifies the data type of the function's result. 
If not specified, the default is VARCHAR(4000).

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 window clause.

Description

Window function LAG returns the row value ahead from the current row as far as offset.
If offset is out of window range, then it returns the default value.

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

RESPECT NULLS returns the row value ahead as far as offset including NULL.
IGNORE NULLS returns the row value ahead as far as offset except for NULL.
If it is not specified, the default value is RESPECT NULLS.

window frame is not available.

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 specifying 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 specifying offset and default.

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 window clause.

Description

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

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

order by is not available within the window clause.
window frame is not available.

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 which is included in date.

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

If date is NULL, then 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 recent value automatically created for an identity column in the current session, and the result type is NATIVE_BIGINT.

If there is not an automatically created value, then it returns null.

This function is similar to @@IDENTITY of MS-SQL and LAST_INSERT_ID() of MySQL. Be cautious when using it because the last altered table determines the value when performing DML for multiple tables as follows.

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 an identity column value created when performing the INSERT, use INSERT INTO name RETURNING .. INTO statement as follows.

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 using 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 not an identity value created in the current session.

gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

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

1 row selected.

Identity value (1) is automatically created.

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.

Identity value (2) is automatically created as a 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 create 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 window clause.

Description

Window function LAST_VALUE returns the last value of expr.

RESPECT NULLS returns the last value of rows including NULL. 
IGNORE NULLS returns the last value of row except for NULL. 
If it is not specified, the default value is RESPECT NULLS.

order by is not available in window clause.
window frame is not available.

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 in 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 window clause.

Description

Window function LEAD returns the row value behind from the current row as far as offset. 
If offset is out of window range, then it returns the default value.

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

RESPECT NULLS returns the row value behind as far as offset including NULL.
IGNORE NULLS returns the row value behind as far as offset except for NULL.
If it is not specified, the default value is RESPECT NULLS.

window frame is not available.

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 specifying 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 specifying offset and default.

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 received expr arguments.

If any of expr is NULL, the result is NULL.

The result type is determined according to the data type of expr1 (the first expr). 
If the data type of expr1 (the first expr) is a character type and a numeric type then it becomes the type including the range of expr1, ..., exprN each.
If all of expr1, ..., exprN is described in CHAR type, then all exprs are compared in VARCHAR type and the result type is 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 window clause.

Description

Window function LISTAGG connects str in its order sorted within each group.

Only PARTITION BY clause is available in OVER() clause of LISTAGG.
It divides the query result sets into groups by using OVER() clause.

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

It connects str in the order of the record sorted within each group.
If str is NULL, then it is excluded.

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

str can be a character string or a binary string. 
If str is a character string, then the result type is varchar. 
If str is a binary string, then 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 should be a value which is bigger than 0.
If num is NULL, then it returns NULL.

Example

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

LNNVL

Syntax

LNNVL( expr )

Description

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

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.

LOCALTIME

Syntax

LOCALTIME [()]
STATEMENT_LOCALTIME()

Description

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

LOCALTIME is an SQL standard function.

The differences among the functions to obtain the current time are as follows.

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

Example

All 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 TIMESTAMP WITHOUT TIME ZONE type value based on the session time is obtained.

LOCALTIMESTAMP is an SQL standard function.

The differences among the functions to obtain the current TIMESTAMP are as follows.

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

Example

All rows have 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.

LOCAL_GROUP_ID

Syntax

LOCAL_GROUP_ID()

Description

It returns a cluster group ID for a server which processes a query from a user.

It is a valid information in a cluster system.

Example

All rows have 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 a cluster group name for a server which processes a query from a user.

It is a valid information in a cluster system.

Example

All rows have 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 a cluster member ID for a server which processes a query from a user.

It is a valid information in a cluster system.

Example

All rows have 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 a cluster member name for a server which processes a query from a user.

It is a valid information in a cluster system.

Example

All rows have the same value.

gSQL> SELECT LOCAL_MEMBER_NAME() FROM DUAL;

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

1 row selected.

LOG

Syntax

LOG( num2 )
LOG( num1, num2 )

Description

It returns the logarithm of num2 in the num1 base.
If num1 is omitted, it returns the logarithm value whose base is 10.

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

If num1 or num2 is NULL, then 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 logged-in user.

The 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 as same as the first logon user, but it can be changed using the SET SESSION AUTHORIZATION statement.
Current user: It is generally as 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 test test

gSQL> SELECT LOGON_USER() AS result FROM DUAL;

RESULT
------
TEST  

1 row selected.

LOWER

Syntax

LOWER( str )

Description

It returns lowercases of str.

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

The return type is the same datatype as 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 adds character string fill to the left side of str until the string length becomes length, then returns the result.

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

The length argument is numeric type.

length means the number of characters, and its maximum range is the maximum precision of the result type. 
If fill is omitted, a white space is added.
If str is longer than the length, it cuts the str as long as the length, then returns it.
If any of str, length, fill is NULL, the result is also NULL. 
If length is 0 or a negative number, the result is 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 matching characters by comparing from the left side of trim_character in trim_source until the matching character does not exist. Then it returns the result.

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

If any of trim_character, trim_source is NULL, the result is NULL.
If trim_character is omitted, a single blank space (' ') is specified 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 )

Description

It is an aggregate function and the maximum value among rows' exprs is obtained.

If ALL is explicitly specified, aggregation is executed for all values.
If DISTINCT is explicitly specified, aggregation is executed for the values which exclude duplicate values.
If ALL or DISTINCT is not explicitly specified, it is processed in the same way as when ALL is specified.

MAX function returns the same result without being affected by the ALL and DISTINCT.

Example

gSQL> SELECT MAX(c1) FROM t1;

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

1 row selected.

MAX() OVER

Syntax

MAX ( expr ) OVER < window name or specification >

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

Description

Window function MAX obtains the maximum value of exprs.
NULL is 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 window clause.

Description

Window function MEDIAN returns the median value of row.
NULL is excluded from the calculation.

order by is not available within the window clause.
window frame is not available.

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 )

Description

It is an aggregate function and the minimum value among rows' exprs is obtained.

If ALL is explicitly specified, aggregation is executed for all values.
If DISTINCT is explicitly specified, aggregation is executed for the values which exclude duplicate values.
If ALL or DISTINCT is not explicitly specified, it is processed in the same way as when ALL is specified.

MIN function returns the same result without being affected by the ALL and DISTINCT.

Example

gSQL> SELECT MIN(c1) FROM t1;

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

1 row selected.

MIN() OVER

Syntax

MIN ( expr ) OVER < window name or specification >

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

Description

Window function MIN obtains the minimum value of exprs. 
NULL is 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.

The num1 argument and num2 argument can be a numeric data type.
If num2 is 0, an error is returned.
If the num1 argument or num2 argument is NULL, then NULL is returned.

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 of which days between date2 and date1 are divided by 31.

If date1 or date2 is NULL, then the result is also NULL.
The date1 argument and date2 argument can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE type.

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 both in date1 and date2, then it returns the integer result regardless of the agreement of timestamp section (if it exists).

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 obtains a date of the day (day of week) which comes first after the given date (an argument).

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

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

The return type is always DATE regardless of the input type of the date.
The hour, minute and second of the result value returns the same hour, minute and second 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 obtains the next value of 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 window clause.

Description

Window function NTH_VALUE returns the expr value of the n-th row.
If the number of window's rows is less than n, then it returns NULL.

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

FROM FIRST points to the n-th row from the first row.
FROM LAST points to the n-th row from the last row.
If it is 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 except for NULL. 
If it is 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 of 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 of 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 window clause.

Description

Window function NTILE returns the bucket number corresponding to each row.

The bucket number is a consecutive integer starting from 1, and the number of buckets are as same as the number of expr.
If the number of buckets are bigger than that of rows, a bucket is given to each row and other buckets remains empty.

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

window frame is not available.

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 of when the number of buckets (the number of expr) is bigger 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 which is the first argument.

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

NULLIF can be expressed by 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 interval_indicator unit to interval day to second type, then returns it.

The argument number is a numeric type.

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

If any argument is NULL, then NULL is returned as a result.

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

Example

It converts 1 Day to 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 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 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 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 interval_indicator unit to interval year to month type, then returns it.

The argument number is a numeric type.

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

If any argument is NULL, then NULL is returned as a result.

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

Example

It converts 1 Year to 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 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, then it returns expr1. If expr1 is NULL, it returns expr2.

The result type is determined according to the data type of expr1.
If NULL is described in expr1, then the result type is determined according to the data type of expr2. 
If the data type of expr1 is a character type and a numeric type then it becomes the type including the range of expr1 and expr2 each.
If the data type of both expr1 and expr2 is CHAR type, then the result type is 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, then it returns expr2. If expr1 is NULL, it returns expr3.

The result type is determined according to the data type of expr2. 
If NULL is described in expr2, then the result type is determined according to the data type of expr3.
If the data type of expr2 is a character type and a numeric type then it becomes the type including the range of expr2 and expr3 each.
If the data type of both expr2 and expr3 is CHAR type, then the result type is 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 str.

The str argument data type can be 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, the white spaces are included in the calculation.
If str is NULL, the result is also 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_lenght with str2.

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

The start_position argument and string_length argument can be numeric data type.

OVERLAY function has the following result.
- 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 window clause.

Description

Window function PERCENT_RANK calculates the ranking ratio of each row for the entire number of rows.

The result of PERCENT_RANK function is the number between 0 and 1, and the rows with the same value have the same ratio value.

window frame is not available.

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 window clause.

Description

Window function PERCENTILE_CONT is an inverse distribution function assuming a consecutive distribution model.

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

expr should be the percentile score between 0 and 1.

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

It sorts the records within the group with WITHIN GROUP ( ORDER BY <sort specification> ). 
It can specify only one <sort specification> in ORDER BY.

NULL is excluded from the sorted values.

The following is a 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

MEDIAN window function is a specific case among PERCENTILE_CONT window functions, and its default value is percentile score 0.5.

For more information, refer to the followings.

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 window clause.

Description

Window function PERCENTILE_DISC is an inverse distribution function assuming a discrete distribution model.

It calculates the percentile score for not null values sorted within the group. 
It determines the smallest value among the values same or over the arguments percentile score of calculated percentile scores.
It returns the determined percentile score.

expr should be the percentile score between 0 and 1.

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

It sorts the records within the group with WITHIN GROUP ( ORDER BY <sort specification> ). 
It can specify only one <sort specification> in ORDER BY.

It calculates CUME_DIST for the sort expression value with the sorted record.
When calculating CUME_DIST, NULL is excluded.

It determines the smallest value among CUME_DIST which is same or over the percentile score specified as expr.
It returns the determined CUME_DIST value.

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

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

PHYSICAL_LENGTH returns the number of internal expression information bytes in expr.

The expr argument can be any data type.

If an input argument is NULL, then the result is 0.

Example

When an input argument is NULL

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

The following example shows the number of NUMBER type bytes of 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 "π" 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 str1 within str2, then returns its location.

The data type of str1 argument and str2 argument can be 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 str1 can not be found within str2, the return value is 0.
If str1 is found within str2, the position of str1 is returned, and the return value starts from 1.
The returned position value is calculated in character unit (not in byte unit).
If 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 squares num1 to num2, and returns the result.

The num1 argument and num2 argument can be a numeric data type.

If num1 is a negative number, num2 should be an integer.
If num1 or num2 is NULL, the result is also 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 degrees.

The degrees argument can be a numeric data type.
If the degrees argument is NULL, then NULL is returned.

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 in the range above min and below max.

The min argument and max argument can be a numeric data type.
If either min argument or the max argument is NULL, then NULL is returned.

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 window clause.

Description

Window function RANK calculates the ranking.

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

window frame is not available.

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 window clause.

Description

Window function RATIO_TO_REPORT calculates the ratio of each row value for the total sum of expr values.
If the row value is NULL, then it returns NULL as a result.

order by is not available in window clause.
window frame is not available.

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.

REGR_AVGX() OVER

Syntax

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

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

Description

Window function REGR_AVGX is a linear regression function.
It calculates the average value of the independent variable expr2 of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is the dependent variable ( y ) and expr2 is the independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

AVG( expr2 )

The returned type is a numeric type.

The returned value is the result from 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 window clause.

Description

Window function REGR_AVGY is a linear regression function. 
It calculates the average value of the dependent variable expr1 of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

AVG( expr1 )

The returned type is a numeric type.

The returned value is the result from 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 window clause.

Description

Window function REGR_COUNT is a linear regression function. 
It calculates the least-squares-fit linear equation of (X, Y) set.

It returns the number of ( X, Y ) pairs, the execution targets of the linear equation and both of which are not NULL.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The returned type is a numeric type.

It returns the number of pairs both of whose 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 window clause.

Description

Window function REGR_INTERCEPT is a linear regression function. 
It calculates the y intercept of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

AVG( expr1 ) - REGR_SLOPE( expr1, expr2 ) * AVG( expr2 )

The returned type is a numeric type.

The returned value is the result from 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 window clause.

Description

Window function REGR_R2  is a linear regression function. 
It calculates the coefficient of determination (R-squared or fitness) of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a 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 from 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 window clause.

Description

Window function REGR_SLOPE is a linear regression function. 
It calculates the slope of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

COVAR_POP(expr1, expr2) / VAR_POP(expr2)

The returned type is a numeric type.

The returned value is the result from 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 window clause.

Description

Window function REGR_SXX is a linear regression function. 
It is an auxiliary function calculating the diagnostic statistics of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

REGR_COUNT( expr1, expr2 ) * VAR_POP( expr2 )

The returned type is a numeric type.

The returned value is the result from 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 window clause.

Description

Window function REGR_SXY is a linear regression function. 
It is an auxiliary function calculating the diagnostic statistics of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

REGR_COUNT( expr1, expr2 ) * COVAR_POP( expr1, expr2 )

The returned type is a numeric type.

The returned value is the result from 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 window clause.

Description

Window function REGR_SYY is a linear regression function. 
It is an auxiliary function calculating the diagnostic statistics of (X, Y) set's least-squares-fit linear equation.

The arguments of expr1 and expr2 are numeral types.

expr1 is an dependent variable ( y ) and expr2 is a independent variable ( x ).

If expr1 is NULL or expr2 is NULL, then it is excluded from the target.

The following is a calculation formula.

REGR_COUNT( expr1, expr2 ) * VAR_POP( expr1 )

The returned type is a numeric type.

The returned value is the result from 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 in 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, BINARY LONG VARYING.

The num argument can be a numeric data type.

If either str or num is NULL, the result is also NULL.
If num is 0 or a negative number, the result is also 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 from strings in str string with to strings, and returns the result.

The str argument, the from argument, and the to argument can be character data types such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If str is NULL, the result is also NULL.
If from is NULL, the str is returned without replacement.
If to value is omitted or NULL, the str value of which from is 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 characters of str in reverse order.

The str argument can be types that are convertible to a character string type or a binary string type.
A character string type is performed in a character unit, and a binary string type can be performed in a byte unit.

If str is NULL, then it returns NULL.

The following table describes the arguments 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 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 is executed as if it is ROUND(num, 0).
If scale is a positive number, it is rounded off based on the number of right digit of the decimal point. If scale is a negative number, it is rounded off based on the number of left digit of the decimal point.

If either num argument or the scale argument is NULL, then NULL is returned.

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 date argument can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE. 
The fmt argument can be a character type such as CHARACTER, CHARACTER VARYING. 
If either date argument or the fmt argument is NULL, then NULL is returned.

The result type is always DATE regardless of the date argument 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 is represented in four digit year by rounding off from 51 year. (e.g. XX01)
YYYY, YEAR, SYYYY, SYEAR, YYY, YY, Y	It is rounded off from July 1st.
IYYY, IYY, IY, I	It is the year embracing the calendar week defined by ISO 8601 standards, and it is rounded off from July 1st.
Q	It is rounded off from the 16th day in the second month of the quarter.
MONTH, MON, MM, RM	It is rounded off from the 16th day.
WW	A week starts from January 1st of the year, and it is rounded off on wednesday 12 p.m of WEEK.
IW	It is the calendar week defined by ISO 8601 standards (1 ~ 52 weeks or 1 ~ 53 weeks), and it is 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 WEEK.
DDD, DD, J	It is rounded off at 12 p.m.
DAY, DY, D	It is rounded off on wednesday 12 p.m of 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 window clause.

Description

Window function ROW_NUMBER assigns the unique number to each row.
The unique number is a consecutive integer starting from 1.

window frame is not available.

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 a 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 a 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 returns the member ID.

It is a 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 returns 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 returns 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 returns 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 returns the shard ID.

It is a 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 returns 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 a number starting from 1 to rows which satisfy the WHERE condition.

It allows using ROWNUM in WHERE clause for the compatibility with Oracle.

However, to restrict the number of the query results, it is recommended to use offset limit clause (the SQL standard) as follows.

(Non standard) Describing the number of the results by using ROWNUM

gSQL> SELECT * FROM t1 WHERE ROWNUM <= 3;

C1
--
A 
B 
C 

3 rows selected.

(SQL standard) Describing the number of the results by using FETCH statement

gSQL> SELECT * FROM t1 FETCH 3;

C1
--
A 
B 
C 

3 rows selected.

To restrict the range of the query results, it is recommended to use OFFSET, FETCH statement as follows.

(Non standard) Describing the range of the number of the results by using ROWNUM

gSQL> SELECT c1 
        FROM ( SELECT ROWNUM rn, c1 
                 FROM t1 )
        WHERE rn BETWEEN 2 AND 3;

C1
--
B 
C 

2 rows selected.

(SQL standard) Describing the range of the number of the results by using ROWNUM OFFSET, FETCH statement

gSQL> SELECT c1 FROM t1 OFFSET 1 FETCH 2;

C1
--
B 
C 

2 rows selected.

It is not recommended to use ROWNUM in WHERE clause for any other uses than the restriction of the number of the results.

The results for the same query may be different according to the execution method as follows when using the ambiguous condition (WHERE c1 < ROWNUM + 3).

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 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 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 fill string to the right side of str until the string's length becomes length, and it 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 means the number of characters, and its maximum range is the maximum PRECISION of the result type. 
If fill is omitted, a white space is added.
If str is longer than length, it cuts the str as long as the length, then returns it.
If any of str, length, fill is NULL, the result is also NULL. 
If length is 0 or a negative number, the result is 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 the matching characters by comparing from the right side of trim_character in trim_source until the matching character does not exist. Then it returns the result.

The data type of trim_character and trim_source arguments 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.

If any of trim_character, trim_source is NULL, the result is 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 obtains the current session ID.

Example

gSQL> SELECT SESSION_ID() FROM dual;

SESSION_ID()
------------
           4

1 row selected.

SESSION_SERIAL

Syntax

SESSION_SERIAL()

Description

It obtains the serial number of 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 user.

The 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 as same as the first logon user, but it can be changed using the SET SESSION AUTHORIZATION statement.
Current user: It is generally as 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 '[+|-]TZH:TZM' format character.
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, then it returns the modified 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 which stores shard_key_value when the shard strategy is defined in the table_name.

The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.

The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.

The result type is NATIVE_BIGINT.

It is a 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 which stores shard_key_value when the shard strategy is defined in the table_name.

The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.

The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.

The result type is VARCHAR.

It is a 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 for the shard which stores shard_key_value when the shard strategy is defined in the table_name.

The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.

The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.

The result type is NATIVE_BIGINT.

It is a 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 for the shard which stores shard_key_value when the shard strategy is defined in the table_name.

The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.

The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.

The result type is VARCHAR.

It is a 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 moves num to the left as many as cnt bits, and returns the movement values.

The data type of input num argument and cnt argument can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or the type which can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT type,  the decimal point is truncated.

cnt is masked with 6 bit, and it is processed to a value in the range within 6 bit.

If either num or cnt is NULL, then NULL is returned.

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 moves num to the right as many as cnt bits, and returns the movement values.

The data type of input num argument and cnt argument can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or the type which can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT type, the decimal point is truncated.

cnt is masked with 6 bit, and it is processed to a value in the range within 6 bit.

If either num or cnt is NULL, then NULL is returned.

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 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, then NULL is returned.

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, then NULL is returned.

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 character string of the field by specifying a character as delimiter within a string.

The data type of string argument and delimiter argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

The field argument can be a numeric data type.

If any of string, delimiter, field is NULL, the result is also NULL.
The value of field should be a numeric value above 1, and if it is 0 or a negative number, an error is 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 it should not be a negative number, but above 0.
If the num argument is NULL, then NULL is returned.

Example

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

STATEMENT_DATE

Syntax

STATEMENT_DATE()
CURRENT_DATE [()]

Description

The current date(DATE type) value is obtained.

The differences among the functions to obtain the current date are as follows.

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

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

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

LOCALTIME is an SQL standard function.

The differences among the functions to obtain the current time are as follows.

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

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

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

LOCALTIMESTAMP is an SQL standard function.

The differences among the functions to obtain the current timestamp are as follows.

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

Example

All rows have 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

The current TIME WITH TIME ZONE type value is obtained.

CURRENT_TIME is an SQL standard function.

The differences among the functions to obtain the current time are as follows.

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

Example

All rows have the same time 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

The current TIMESTAMP WITH TIME ZONE type value is obtained.

CURRENT_TIMESTAMP is an SQL standard function.

The differences among the functions to obtain the current timestamp are as follows.

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

Example

All rows have 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 obtains 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 obtains the Domain Change Number (DCN) value of the current STATEMENT's VIEW SCN.

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 obtains the Global Change Number (GCN) value of the current STATEMENT's VIEW SCN.

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 obtains the Local Change Number (LCN) value of the current STATEMENT's VIEW SCN.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_LCN() FROM dual;

STATEMENT_VIEW_SCN_LCN()
------------------------
                   17880

1 row selected.

STDDEV

Syntax

STDDEV( [ ALL | DISTINCT ] expr )

Description

It is an aggregation function, and it obtains the standard deviation of an expr set.

If ALL is specified, this function is performed for all values. If DISTINCT is specified, this function is performed for the values of which the duplicates were deleted from. If it is not specified, it is processed as if ALL is apecified.

If the number of expr sets except for NULL after deleting the duplicates by using DISTINCT is one, then it returns 0 like as VARIANCE.

The following table describes the arguments 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 gets the standard deviation as follows.
　• If the number of expr sets is 1, then it returns 0.
　• If the number of expr sets is bigger than 1, it returns the value of STDDEV_SAMP( expr ).

The standard deviation is a positive square root of a variance, and it is obtained calculating the square root of the variance. In other words, the STDDEV function is as same as the square root of 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.

STDDEV() OVER

Syntax

STDDEV ( expr ) OVER < window name or specification >

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

Description

Window function STDDEV calculates the standard deviation of expr. 
If the number of expr except for NULL is one, then it returns 0 as a 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 )

Description

It is an aggregation function, and it obtains the population standard deviation of an expr set. 
If the number of expr sets except for NULL is one, then it returns 0.

The following table describes the arguments 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 a positive square root of a population variance, and it is obtained by calculating the square root of the population variance. In other words, the STDDEV_POP function is as same as the square root of 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.

STDDEV_POP() OVER

Syntax

STDDEV_POP ( expr ) OVER < window name or specification >

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

Description

Window function STDDEV_POP calculates the population standard deviation of expr. 
If the number of expr except for NULL is one, then it returns 0 as a 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 )

Description

It is an aggregation function, and it obtains the sample standard deviation of an expr set. If the number of expr sets except for NULL is one, then it returns NULL.

The following table describes the arguments 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 a positive square root of a sample variance, and it is obtained by calculating the square root of the sample variance. In other words, the STDDEV_SAMP function is as same as the square root of 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.

STDDEV_SAMP() OVER

Syntax

STDDEV_SAMP ( expr ) OVER < window name or specification >

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

Description

Window function STDDEV_SAMP calculates the sample standard deviation of expr. 
If the number of expr except for NULL is one, then it returns NULL as a 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 window clause.

Description

Window function STRING_AGG connects str according to the function's execution range defined in OVER clause.

If str is NULL, then it is excluded.

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

str can be a character string or a binary string. 
If str is a character string, then the result type is varchar. 
If str is a binary string, then the result type is 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 which are within string_length range from start_position, and returns the result for str.

This function is as same as SUBSTRING function, except that start_position and string_length of 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 which are within string_length range from start_position, and returns the result for str.

The str argument data type can be a character data 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 argument and string_length argument can be a numeric data type.

If any of str, start_position, string_length is NULL, the result is NULL. 
The start_position and string_length start from 1, and they are calculated in character unit according to character set (not in byte unit).

If start_position is 0, the start_position is assigned to 1. 
If start_position is a positive number, it searches for the position forwards (towards right) from the beginning of str. 
If start_ position is a negative number, it searches for the position backwards (towards left) from the end of str. 
If string_length is omitted, characters from the start_position to the last character of str, are returned.

If string_length is 0 or a negative number, the result is NULL.
If start_position > (str length), the result is NULL.
If (str length + start_position) < 0, the result is 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 )

Description

It is an aggregate function and the sum of expr value is obtained.

If ALL is explicitly specified, aggregation is executed for all values.
If DISTINCT is explicitly specified, aggregation is executed for the values which exclude duplicate values.
If ALL or DISTINCT is not explicitly specified, it is processed in the same way as when ALL is specified.

Example

gSQL> SELECT SUM(c1) FROM t1;

SUM(C1)
-------
      6

1 row selected.

SUM() OVER

Syntax

SUM ( expr ) OVER < window name or specification >

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

Description

Window function SUM calculates the sum of expr value.
NULL is 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 obtains the current DATE type 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 Greenwich Mean Time) value.
If the timezone is not specified, it is calculated as session time zone.

The data type of an input argument can be time, time with time zone, timestamp, timestamp with time zone.
The result type is time or timestamp type.

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 obtains the current TIME WITH TIME ZONE type 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 obtains the current TIMESTAMP WITH TIME ZONE type 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.

TAN

Syntax

TAN( num )

Description

It returns the tangent value of num in radians unit.
If num is NULL, then NULL is returned.

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 by using base64 encoding, and returns the converted character.
str argument can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, a type which can be converted to a character type, or a binary character type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
The result type is a character such as CHARACTER VARYING or CHARACTER LONG VARYING.

If str is NULL, the result value is also NULL.

Base64 encoding represents 8 bit binary data in 64 characters consisting of ascii areas.
64 characters consist of  A~Z, a~z, 0~9, +, /.

6 bit is represented as a character, and three characters (24 bits) are represented with 4 characters as a unit.
If the encoded characters can not fill 4 characters, then others are filled with '='.
If encoded characters are over 76, then a newline is added and they are divided into multiple lines.

Use FROM_BASE64() function to decode the base64 encoded character.
The newline, carriage return, tab, space are ignored when decoding base64.

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 datetime to a string in the specified fmt format, and returns the result.

The datetime argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, INTERVAL. 
The fmt argument data type can be a character data type such as CHARACTER, CHARACTER VARYING.

If any argument is NULL, then NULL is returned.

If fmt is omitted, it follows the default format.
• 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 then returned regardless of fmt.

For more information about the string which can be specified in fmt, refer to 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 a numeric data type.
The fmt argument data type can be a character data type such as CHARACTER, CHARACTER VARYING.
If fmt is omitted, all significant digits are converted to the string and returned.
For more information about the string which can be specified in fmt, refer to Number Format String.
If any input argument is NULL, then NULL is returned.

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 str string in the specified fmt format to DATE type, and returns the result.

The str argument and fmt argument data type can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.
If fmt is omitted, the default format is NLS_DATE_FORMAT, and in this case str should be the default format string.

For more information about the string which can be specified in fmt, refer to Datetime Format String.
For more information, refer to NLS_DATE_FORMAT.

If either str or fmt is NULL, then NULL is returned.

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 str string in the specified fmt format to NATIVE_BIGINT type, and returns the result.

The str argument and fmt argument data type can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of str, fmt is NULL,  the result is also NULL.  
For more information about the string which can be specified in fmt, refer to 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 str string in the specified fmt format to NATIVE_DOUBLE type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of str, fmt is NULL, the result is also NULL.
For more information about the string which can be specified in fmt, refer to 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 str string in the specified fmt format to NATIVE_INTEGER type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of str, fmt is NULL, the result is also NULL.
For more information about the string which can be specified in fmt, refer to 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 str string in the specified fmt format to NATIVE_REAL type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of str, fmt is NULL, the result is also NULL.
For more information about the string which can be specified in fmt, refer to 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 str string in the specified fmt format to NATIVE_SMALLINT type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of str, fmt is NULL, the result is also NULL.
For more information about the string which can be specified in fmt, refer to 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 str string in the specified fmt format to NUMBER type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of str, fmt is NULL, the result is also NULL.
For more information about the string which can be specified in fmt, refer to 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 str string in the specified fmt format to TIME type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIME_FORMAT, and in this case str should be the default format string.

For more information about the string which can be specified in fmt, refer to Datetime Format String.
For more information, refer to NLS_TIME_FORMAT.

If either str or fmt is NULL, then NULL is returned.

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 str string in the specified fmt format to TIME WITH TIME ZONE type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIME_WITH_TIME_ZONE_FORMAT, and in this case str should be the default format string.

For more information about the string which can be specified in fmt, refer to Datetime Format String
For more information, refer to NLS_TIME_WITH_TIME_ZONE_FORMAT.

If either str or fmt is NULL, then NULL is returned.

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 str string in the specified fmt format to TIMESTAMP type, and returns the result.

The data type of str argument and fmt argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING. 
If fmt is omitted, the default format is NLS_TIMESTAMP_FORMAT, and in this case str should be the default format string.

For more information about the string which can be specified in fmt, refer to Datetime Format String.
For more information, refer to NLS_TIMESTAMP_FORMAT.

If either str or fmt is NULL, then NULL is returned.

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 str string in the specified fmt format to TIMESTAMP WITH TIME ZONE type, and returns the result.

The data type of str argument and fmt argument can be 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 should be the default format string.

For more information about the string which can be specified in fmt, refer to  Datetime Format String.
For more information, refer to NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT.

If either str or fmt is NULL, then NULL is returned.

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 obtains the current date (DATE type) value based on the session time.

The differences among the functions to obtain the current date are as follows.

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

Example

All date values are always 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 obtains the current TIME WITHOUT TIME ZONE type value based on the session time.

The differences among the functions to obtain the current time are as follows.

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

Example

All time values are always 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 obtains the current TIMESTAMP WITHOUT TIME ZONE type value based on the session time.

The differences among the functions to obtain the current timestamp are as follows.

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

Example

All timestamp values are always 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 obtains the current TIME WITH TIME ZONE type value based on the session time.

The differences among the functions to obtain the current time are as follows.

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

Example

All time values are always 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 obtains the current TIMESTAMP WITH TIME ZONE type value based on the session time.

The differences among the functions to obtain the current timestamp are as follows.

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

Example

All timestamp values are always 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. It replaces characters of string which corresponds to the the character of from with the character of to at the same position as the character of from.

The data type of the string argument, the from argument, and the to argument can be a data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.

If any of string, from, to is NULL, the result is also NULL.

When the character of string which are as same as those of from exists,
- If the length of from is as same as that of to, then it is replaced with the character of to at the same position as the character of from.
- If the length of from is longer than that of to, then characters of from at the position after that of to length are deleted from the string.
- If the character of from is duplicate, then it is replaced with the character of to at the same position as the first duplicate character of from.
When the character of string which is as same as those of from does not exist, then the string is not replaced.

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 the character of string which are as same as those of from exists, then it is replaced with the character of to at the same position as the character of from.
- 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 from string is longer than that of to string, then characters of from at the position after that of to string length are deleted from the string, 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 trim_character in trim_source from the LEADING, TRAILING, BOTH direction until the matching character does not exist. Then it returns the result.

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

If any of trim_character, trim_source is NULL, the result is NULL.

[ LEADING | TRAILING | BOTH ]
- LEADING: Removes trim_character from the beginning of trim_source.
- TRAILING: Removes trim_character from the back of trim_source.
- BOTH: Removes trim_character from the both direction (the beginning, the back) of trim_source.
trim_character should be a single character.
If trim_character is omitted, single blank space (' ') is specified by default.
When FROM is specified
- [ LEADING | TRAILING | BOTH ], trim_character or [ LEADING | TRAILING | BOTH ] trim_character should 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 ), it is executed in the same way 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 scale, then returns the result.

The num argument and scale argument can be a numeric type.
If either the num argument or the scale argument is NULL, then NULL is returned.

If scale is omitted, the scale becomes 0, and it is executed as same as TRUNC( num, 0 ).
If scale is a positive number, it is truncated based on the number of right digit of the decimal point.
If scale is a negative number, it is truncated off based on the number of left digit 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 truncates the date in a specified fmt unit, and returns the result.

The date argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE.
The fmt argument data type can be a character data type such as CHARACTER, CHARACTER VARYING.
If either date argument or fmt argument is NULL, then NULL is returned.

The result type is always DATE regardless of the input date type.

If fmt is omitted, the default is DAY, and the available format string is described in the following table.

Available format string in fmt
Format string	Description
CC, SCC	Century
YYYY, YEAR, SYYYY, SYEAR, YYY, YY, Y	Year
IYYY, IYY, IY, I	The year embracing the calendar week defined by ISO 8601 standards
Q	Quarter
MONTH, MON, MM, RM	Month
WW	The week whose first week starts from January 1st of the year
IW	The week containing the first thursday of the year designated as the calendar week by ISO 8601 standards ( 1 ~ 52 weeks or 1 ~ 53 weeks) becomes the first week.
W	The week whose first week starts from the first 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 characters of str.

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

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

Example

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

UNHEX

Syntax

UNHEX( str )

Description

str argument is a hexadecimal character and this function represents it as each byte and returns it as a binary string.

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

If str is NULL, then the result value is also NULL.
If str includes a character which does not belong to the hexadecimal range, then it returns an error.

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

str argument is a hexadecimal character and this function represents it as each byte and returns it as a character string.

The input argument can be a character type such as CHARACTER VARYING, CHARACTER LONG VARYING. The result type is a character type such as CHARACTER VARYING, CHARACTER LONG VARYING.

If str is NULL, then the result value is also NULL.
If str includes a character which does not belong to the hexadecimal range, then it returns an error.

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 obtains the current user's number ID.

In cluster system, the value may vary depending on the connected server.

It is recommended to use CURRENT_USER function obtaining the current username.

Example

% gsql test test

gSQL> SELECT USER_ID() FROM dual;

USER_ID()
---------
        6

1 row selected.

UUID

Syntax

UUID()

Description

It creates the universal unique identifier, then returns it. 
The return type is VARBINARY type, and it internally consists of 16 bytes.

Example

gSQL> SELECT HEX( UUID() ) FROM DUAL;
HEX( UUID() )                   
--------------------------------
E6F0A5C2387511E8B95259E479C2FD50
1 row selected.

VAR_POP

Syntax

VAR_POP( expr )

Description

It is an aggregation function, and it obtains the population variance of an expr set. If the number of expr sets except for NULL is one, then it returns 0.

The following table describes the arguments 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 a variance of the population (entire) group, and it is the average of the square value of deviation. In other words, it is calculated by extracting the population average (the entire average) from each value of the data, and squaring each value, then adding them together and dividing them by the number of datas in the population group.

This value is used to figure out how far each value is from the average value.

For more information, refer to STDDEV_POP.

Example

gSQL> SELECT VAR_POP(c1) 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 window clause.

Description

Window function VAR_POP calculates the population variance of expr. 
If the number of expr except for NULL is one, then it returns 0 as a 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 )

Description

It is an aggregation function, and it obtains the sample variance of an expr set. If the number of expr sets except for NULL is one, then it returns NULL.

The following table describes the arguments 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 the population variance dealing with the population (entire) group, the sample variance deals with the average and deviation of extracted samples. In other words, it is calculated by extracting the sample average from each value of the data, and squaring each value, then adding them together and dividing them by the number of datas in the population group minus 1.

This value is used to figure out the variance of the population group.

For more information, refer to STDDEV_SAMP.

Example

gSQL> SELECT VAR_SAMP(c1) 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 window clause.

Description

Window function VAR_SAMP calculates the sample variance of expr. 
If the number of expr except for NULL is one, then it returns NULL as a 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 )

Description

It is an aggregation function, and it obtains the variance of an expr set.

If ALL is specified, this function is performed for all values. If DISTINCT is specified, this function is performed for the values of which the duplicates were deleted from. If it is not specified, it is processed as if ALL is apecified.

If the number of expr sets except for NULL after deleting the duplicates by using DISTINCT is one, then it returns 0.

The following table describes the arguments 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 gets the variance as follows.

• If the number of expr sets is 1, then it returns 0.

• If the number of expr sets is bigger than 1, it returns the value of STDDEV_SAMP (expr).

For more information, refer to STDDEV.

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.

VARIANCE() OVER

Syntax

VARIANCE ( expr ) OVER < window name or specification >

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

Description

Window function VARIANCE calculates the variance of expr. 
If the number of expr except for NULL is one, then it returns 0 as a 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 obtains 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 a section of the same width as cnt within a range between specified min and max, and it returns the section location in which the num is located.

The data type of num argument, min argument, max argument and cnt argument can be a numeric data type.

min, max means the range for the section. If the min value is equal to the max value, an error is returned.
cnt means the number of sections. The cnt value should be a positive number. If the cnt value is 0 or a negative number, an error is returned. 
The section's location is numbered from one.

If any of num, min, max, cnt is NULL, the result is also NULL.

Example

gSQL> SELECT WIDTH_BUCKET( 5, 1, 20, 5 ) AS RESULT FROM DUAL;
RESULT
------
     2
1 row selected.