* (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.

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.

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.

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.

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.

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.

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

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

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

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(*)

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

NULLIF

Syntax

NULLIF( expr1, expr2 )

Description

If expr1 is equal to expr2, it returns NULL. If it is 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.

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.

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.

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.

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.

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.

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.

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.

ROWID_GRID_BLOCK_ID

Syntax

ROWID_GRID_BLOCK_ID( rowid )

Description

It returns the GRID block ID.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 ).

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_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.

Example

gSQL> SELECT STDDEV_POP(c1) FROM t1;

 STDDEV_POP(C1)
---------------
10.283968105746

1 row 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.

Example

gSQL> SELECT STDDEV_SAMP(c1) FROM t1;

 STDDEV_SAMP(C1)
----------------
11.4978258814438

1 row 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.

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.

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.

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.

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.

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.

When returning it as a character string, it applies the currently applicable character set and returns the result value because the str argument is a hexadecimal character of an unknown data.
If it is not included in the currently applicable character set, 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.

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.

For more information, refer to STDDEV_POP.

Example

gSQL> SELECT VAR_POP(c1) FROM t1;

VAR_POP(C1)
-----------
     105.76

1 row 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.

For more information, refer to STDDEV_SAMP.

Example

gSQL> SELECT VAR_SAMP(c1) FROM t1;

VAR_SAMP(C1)
------------
       132.2

1 row 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.

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.

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.