SQL Elements

Syntax Elements

Identifiers

Identifier is divided into an ordinary identifier and a delimited identifier.
An ordinary identifier consists of letters or a combination of letters and numbers, and it is used by internally substituting all characters to uppercase letters. Therefore, an ordinary identifier is not case-sensitive.

The following is an example of an ordinary identifier.

GOLDILOCKS
GoldiLocks

A delimited identifier consists of letters or a combination of letters and numbers enclosed in double quotes ("). All the characters are used as internally described. Therefore, a delimited identifier is case-sensitive.

The following is an example of a delimited identifier.

"GOLDILOCKS"
"GoldiLocks"

Literals

Literals mean representation of non-null value.

Text Literals

Text literals mean representation of strings and binary strings.
Use single quote (') at the beginning and end of a string to write string of text literals.
Not only the double quotes (") string but also all strings except for the single quote (') string can be written within a single quote ('). 
Use a single quote twice without white spaces in between to write a single quote (') in a string. 
A maximum of 4000 characters can be written in a string.

The followings are examples of text literals for a string.

'GOLDILOCKS'
'Sunje''s DBMS'
A binary string of text literals is a string of hexadecimal numbers which starts with x'(X') and ends with '. Only the characters corresponding to 0 ~ 9, A (a) ~ F (f) can be written in each position of a hexadecimal string. The length of a hexadecimal string should always be an even number because its two digits mean one byte. A maximum of 4,000 characters can be written in a binary string.

The followings are examples of text literals for a binary string.

x'001f'
X'FF0A'
x'aF37BBc013'

Numeric Literals

Numeric literals mean literals of numeric type, and integers or the number with decimal point can be written. The syntax for numeric literals is as follows.

[ + | - ] <digits> [ . <digits> ] [ E | e [ + | - ] <digits> ] [ f | F | d | D ]

The followings are examples of numeric literals.

20
+123.45
0.03
+1.23E-02
-1.5

10f
+123.45F
1.2E-3F
-22d
123.45D
-1.23E+05D

Datetime Literals

Datetime literals are representation of date/time type. 
Datetime value is specified using string literal, or by converting character or numeric value to datetime value using TO_*function (TO_DATE, etc).

Datetime data types are DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE.

Date Literals

Date literals are written in a form of DATE'string literal' or TO_DATE(string_literal [, format]).

For more information, refer to TO_DATE, Datetime Format String, NLS_DATE_FORMAT.

DATE'2002-07-15'
TO_DATE( '2002-07-15' )
TO_DATE( '15-JUL-02', 'DD-MON-RR' )
TO_DATE( '2002-07-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
TO_DATE( '2002-07-15 13:25:30', 'YYYY-MM-DD HH24:MI:SS' )
gSQL> SELECT TO_DATE( '2000-07', 'YYYY-MM' ) FROM DUAL;
TO_DATE( '2000-07', 'YYYY-MM' )
-------------------------------
2000-07-01
gSQL> SELECT 
      TO_CHAR( DATE'2002-07-15', 'YYYY-MM-DD HH24:MI:SS' ) AS RESULT
      FROM DUAL;
RESULT             
-------------------
2002-07-15 00:00:00
gSQL> SELECT 
      TO_CHAR( SYSDATE, 
               'YYYY-MM-DD HH24:MI:SS' ) AS RESULT_SYSDATE,
      TO_CHAR( TRUNC( SYSDATE ),
               'YYYY-MM-DD HH24:MI:SS' ) AS RESULT_TRUNC_SYSDATE 
      FROM DUAL;
RESULT_SYSDATE      RESULT_TRUNC_SYSDATE
  ------------------- --------------------
  2014-08-19 10:06:49 2014-08-19 00:00:00
gSQL> SELECT 
      TO_DATE( '2002-08-12' ) = 
      TRUNC( TO_DATE( '2002-08-12 23:59:59', 'YYYY-MM-DD HH24:MI:SS' ) )
      AS RESULT FROM DUAL;
RESULT
------
TRUE

Time Literals

Time literals are written in a form of TIME'string literal' or TO_TIME(string_literal [, format]).
The time type includes hour, minute, second (fractional seconds).
Fractional seconds can be specified to maximum six digits numbers format.

For more information, refer to TO_TIME, Datetime Format String, NLS_TIME_FORMAT.

TIME'15:30:59.999999'
TO_TIME( '15:30:59.999999' )
TO_TIME( '09.45.03.546873 AM', 'HH12.MI.SS.FF6 AM' )
TO_TIME( '09:45:03', 'HH12:MI:SS' )

Time with Time Zone Literals

Time with time zone literals is written in a form of TIME'string literal', TIME WITH TIME ZONE'string literal',  TO_TIME_WITH_TIME_ZONE(string_literal [, format] ), or TO_TIME_TZ(string_literal [, format] ).
Time with time zone type includes hour, minute, second (fractional seconds), and time zone offset (time zone hour, time zone minute).
Fractional seconds can be specified to maximum six digits numbers format.

For more information, refer to TO_TIME_WITH_TIME_ZONE, Datetime Format String, NLS_TIME_WITH_TIME_ZONE_FORMAT.

TIME'15:30:59.999999 +09:00'

TIME WITH TIME ZONE'15:30:59.999999 +09:00'
TO_TIME_WITH_TIME_ZONE( '15:30:59.999999 +09:00' )
TO_TIME_TZ( '15:30:59.999999 +09:00' )
TO_TIME_WITH_TIME_ZONE( '09.45.03.546873 +09:00 AM', 
                        'HH12.MI.SS.FF6 TZH:TZM AM' )

Timestamp Literals

Timestamp literals are written in a form of TIMESTAMP'string literal' or TO_TIMESTAMP(string_literal [, format] ).
Timestamp type includes year, month, day, hour, minute, second (fractional seconds).
Fractional seconds can be specified to maximum six digits numbers format.

For more information, refer to TO_TIMESTAMP, Datetime Format String, NLS_TIMESTAMP_FORMAT.

TIMESTAMP'2002-07-15 15:39:59.999999'
TO_TIMESTAMP( '2002-07-15 15:39:59.999999' )
TO_TIMESTAMP( '15-JUL-02 11.06.30.123456 AM', 
              'DD-MON-RR HH12.MI.SS.FF6 AM' )

Timestamp with Time Zone Literals

Timestamp with time zone literals is written in a form of TIMESTAMP'string literal',  TIMESTAMP WITH TIME ZONE'string literal', TO_TIMESTAMP_WITH_TIME_ZONE(string_literal [, formt] ), or TO_TIMESTAMP_TZ(string_literal [, format]).
Timestamp with time zone type includes year, month, day, hour, minute, second (fractional seconds), time zone offset (time zone hour, time zone minute).
Fractional seconds can be specified to maximum six digits numbers format.

For more information, refer to TO_TIMESTAMP_WITH_TIME_ZONE, Datetime Format String , NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT.

TIMESTAMP'2002-07-15 15:39:59.999999 +09:00'
TIMESTAMP WITH TIME ZONE'2002-07-15 15:39:59.999999 +09:00'
TO_TIMESTAMP_WITH_TIME_ZONE( '2002-07-15 15:39:59.999999 +09:00' )
TO_TIMESTAMP_TZ( '2002-07-15 15:39:59.999999 +09:00' )
TO_TIMESTAMP_WITH_TIME_ZONE( '15-JUL-02 11.06.30.123456 +09:00 AM',
                             'DD-MON-RR HH12.MI.SS.FF6 TZH:TZM AM' )
TO_TIMESTAMP_TZ( '15-JUL-02 11.06.30.123456 +09:00 AM',
                 'DD-MON-RR HH12.MI.SS.FF6 TZH:TZM AM' )

Interval Literals

The interval literals specify the time interval.
Intervals are classified and expressed as follows.

The followings are the list of interval types.

Leading precision
 • It is the digit number of the field, it can be specified from 2 to 6. If it is not specified, the default 
   value is set to 2.
 • If the leading field value exceeds the leading precision, then an error is returned.
Fractional seconds precision
• It is the digit number of fractional seconds, and it can be specified from 0 to 6. If it is not specified,
 the default value is set to 6.
• If the fractional second field value exceeds the fractional seconds precision, then it is rounded off.

For more information, refer to INTERVAL, Precisions and value range of the second or later field in INTERVAL * TO * .

Examples of Using Interval Literals.

The followings are examples of using interval literals.

Interval YEAR

The followings are examples of using interval YEAR literals.

Interval YEAR literals.

Example

Description

Display string

INTERVAL'1'YEAR

INTERVAL'01-00'YEAR

1 year

+01-00

INTERVAL'100'YEAR

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100'YEAR(3)

100 year

+100-00

INTERVAL'+999999'YEAR(6)

999999 year

+999999-00

INTERVAL'-999999'YEAR(6)

-(999999 year)

-999999-00

Interval MONTH

The followings are examples of using interval MONTH literals.

Example

Description

Display string

INTERVAL'1'MONTH

INTERVAL'00-01'MONTH

1 month

+00-01

INTERVAL'100'MONTH

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100'MONTH(3)

8 year 4 month

+008-04

INTERVAL'+999999'MONTH(6)

83333 year 3 month

+083333-03

INTERVAL'-999999'MONTH(6)

-(83333 year 3 month)

-083333-03

Interval YEAR TO MONTH

The followings are examples of using interval YEAR TO MONTH literals.

Example

Description

Display string

INTERVAL'1-06'YEAR TO MONTH

1 year 6 month

+01-06

INTERVAL'1-12'YEAR TO MONTH

The month value exceeded 11, so it returns the error.

-

INTERVAL'100-11'YEAR TO MONTH

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100-11'YEAR(3) TO MONTH

100 year 11 month

+100-11

INTERVAL'+999999-11'YEAR(6) TO MONTH

999999 year 11 month

+999999-11

INTERVAL'-999999-11'YEAR(6) TO MONTH

-(999999 year 11 month)

-999999-11

Interval DAY

The followings are examples of using interval DAY literals.

Example

Description

Display string

INTERVAL'1'DAY

INTERVAL'01 00:00:00'DAY

1 day

+01 00:00:00

INTERVAL'100'DAY

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100'DAY(3)

100 day

+100 00:00:00

INTERVAL'+999999'DAY(6)

999999 day

+999999 00:00:00

INTERVAL'-999999'DAY(6)

-(999999 day)

-999999 00:00:00

Interval HOUR

The followings are examples of using interval HOUR literals.

Example

Description

Display string

INTERVAL'1'HOUR

INTERVAL'00 01:00:00'HOUR

1 hour

+00 01:00:00

INTERVAL'1000'HOUR(3)

It exceeds the leading precision 3, so it returns the error

-

INTERVAL'1000'HOUR(4)

41 day 16 hour

+0041 16:00:00

INTERVAL'+999999'HOUR(6)

41666 day 15 hour

+041666 15:00:00

INTERVAL'-999999'HOUR(6)

-(41666 day 15 hour)

-041666 15:00:00

Interval MINUTE

The following are examples of using interval MINUTE literals.

Example

Description

Display string

INTERVAL'1'MINUTE

INTERVAL'00 00:01:00'MINUTE

1 minute

+00 00:01:00

INTERVAL'12345'MINUTE(4)

It exceeds the leading precision 4, so it returns the error

-

INTERVAL'12345'MINUTE(5)

8 day 13 hour 45 minute

+00008 13:45:00

INTERVAL'+999999'MINUTE(6)

694 day 10 hour 39 minute

+000694 10:39:00

INTERVAL'-999999'MINUTE(6)

-(694 day 10 hour 39 minute)

-000694 10:39:00

Interval SECOND

The followings are examples of using interval SECOND literals.

Example

Description

Display string

INTERVAL'1'SECOND

INTERVAL'00 00:00:01.000000'SECOND

1 second

+00 00:00:01.000000

INTERVAL'100'SECOND

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'99.9999999'SECOND

INTERVAL'99.9999999'SECOND(2,6)

The fractional seconds are rounded off to become 100 second, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL'99.9999999'SECOND(3)

1 minute 40 second

+000 00:01:40.000000

INTERVAL'29.506167'SECOND(2, 2)

29.51 second

+00 00:00:29.51

INTERVAL'999999.999999'SECOND(6,6)

11day 13 hour 46 minute 39.999999 second

+000011 13:46:39.999999

INTERVAL'-999999.999999'SECOND(6,6)

-(11day 13 hour 46 minute 39.999999 second)

-000011 13:46:39.999999

Interval DAY TO HOUR

The followings are examples of using interval DAY TO HOUR literals.

Example

Description

Display string

INTERVAL'1 23'DAY TO HOUR

INTERVAL'01 23:00:00'DAY TO HOUR

1 day 23 hour

+01 23:00:00

INTERVAL'1 24'DAY TO HOUR

The hour value exceeds 23 (invalid), so it returns the error.

-

INTERVAL'100 23'DAY TO HOUR

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100 23'DAY(3) TO HOUR

100 day 23 hour

+100 23:00:00

INTERVAL'+999999 23'DAY(6) TO HOUR

999999 day 23 hour

+999999 23:00:00

INTERVAL'-999999 23'DAY(6) TO HOUR

-(999999 day 23 hour)

-999999 23:00:00

INTERVAL'-999999 +23'DAY(6) TO HOUR

Invalid sign error

-

Interval DAY TO MINUTE

The followings are examples of using interval DAY TO MINUTE literals.

Example

Description

Display string

INTERVAL'1 23:59'DAY TO MINUTE

INTERVAL'01 23:59:00'DAY TO MINUTE

1 day 23 hour 59 second

+01 23:59:00

INTERVAL'1 24:59'DAY TO MINUTE

The hour value exceeds 23 (invalid), so it returns the error.

-

INTERVAL'1 23:60'DAY TO MINUTE

The minute value exceeds 59 (invalid), so it returns the error.

-

INTERVAL'100 23:59'DAY TO MINUTE

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100 23:59'DAY(3) TO MINUTE

100 day 23 hour 59 minute

+100 23:59:00

INTERVAL'+999999 23:59'DAY(6) TO MINUTE

999999 day 23 hour 59 minute

+999999 23:59:00

INTERVAL'-999999 23:59'DAY(6) TO MINUTE

-(999999 day 23 hour 59 minute)

-999999 23:59:00

Interval DAY TO SECOND

The followings are examples of using interval DAY TO SECOND literals.

Example

Description

display string

INTERVAL '1 23:59:59.999999'DAY TO SECOND

1 day 23 hour 59 minute 59.999999 second

+01 23:59:59.999999

INTERVAL '1 24:59:59.999999'DAY TO SECOND

The hour value exceeds 23, so it returns the error.

-

INTERVAL '1 23:60:59.999999'DAY TO SECOND

The minute value exceeds 59, so it returns the error.

-

INTERVAL '1 23:59:60.999999'DAY TO SECOND

The second value exceeds 60, so it returns the error.

-

INTERVAL '99 23:59:59.9999999'DAY TO SECOND

The fractional seconds are rounded off to become 100 day, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL '99 23:59:59.9999999'DAY(3) TO SECOND

100 day

+100 00:00:00.000000

INTERVAL '1 11:22:33.567890'DAY(2) TO SECOND(2)

1 day 11 hour 22 minute 33.57 second

+01 11:22:33.57

INTERVAL '+999999 23:59:59.999999'DAY(6) TO SECOND(6)

999999 day 23 hour 59 minute 59.999999 hour

+999999 23:59:59.999999

INTERVAL '-999999 23:59:59.999999'DAY(6) TO SECOND(6)

-(999999 day 23 hour 59 minute 59.999999 hour)

-999999 23:59:59.999999

Interval HOUR TO MINUTE

The followings are examples of using interval HOUR TO MINUTE literals.

Example

Description

Display string

INTERVAL'23:59'HOUR TO MINUTE

INTERVAL'00 23:59:00'HOUR TO MINUTE

23 hour 59 minute

+00 23:59:00

INTERVAL'23:60'HOUR TO MINUTE

The minute value exceeds 59, so it returns the error.

-

INTERVAL'100:59'HOUR TO MINUTE

It exceeds the leading precision 2, so it returns the error.

-

INTERVAL'100:59'HOUR(3) TO MINUTE

4 day 4 hour 59 minute

+004 04:59:00

INTERVAL'+999999:59'HOUR(6) TO MINUTE

41666 day 15 hour 59 minute

+041666 15:59:00

INTERVAL'-999999:59'HOUR(6) TO MINUTE

-(41666 day 15 hour 59 minute)

-041666 15:59:00

Interval HOUR TO SECOND

The followings are examples of using interval HOUR TO SECOND literals.

Example

Description

Display string

INTERVAL '23:59:59.999999'HOUR TO SECOND

INTERVAL '00 23:59:59.999999'HOUR TO SECOND

23 hour 59 minute 59.999999 second

+00 23:59:59.999999

INTERVAL '23:60:59.999999'HOUR TO SECOND

The minute value exceeds 59, so it returns the error.

-

INTERVAL '23:59:60.999999'HOUR TO SECOND

The second value exceeds 59, so it returns the error.

-

INTERVAL '99:59:59.9999999'HOUR TO SECOND

The fractional seconds are rounded off to become 100 hour, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL '99:59:59.9999999'HOUR(3) TO SECOND

4 day 4 hour

+004 04:00:00.000000

INTERVAL '11:22:29.569'HOUR(3) TO SECOND(1)

11 hour 22 minute 29.6 second

+000 11:22:29.6

INTERVAL '+999999:59:59.999999'HOUR(6) TO SECOND(6)

41666 day 15 hour 59 minute 59.999999 second

+041666 15:59:59.999999

INTERVAL '-999999:59:59.999999'HOUR(6) TO SECOND(6)

-(41666 day 15 hour 59 minute 59.999999 second)

-041666 15:59:59.999999

Interval MINUTE TO SECOND

The followings are examples of using interval MINUTE TO SECOND literals.

Example

Description

Display string

INTERVAL '15:23.123456'MINUTE TO SECOND

INTERVAL '00 00:15:23.123456'MINUTE TO SECOND

15 minute 23.123456 second

+00 00:15:23.123456

INTERVAL '15:60.123456'MINUTE TO SECOND

The second value exceeds 59, so it returns the error.

-

INTERVAL '99:59.999999'MINUTE TO SECOND(2)

The fractional seconds are rounded off to become 100 minute, then it exceeds the leading precision 2, so it returns the error.

-

INTERVAL '99:59.999999'MINUTE(3) TO SECOND(2)

1 hour 40 minute

+000 01:40:00.00

INTERVAL '+999999:59.999999'MINUTE(6) TO SECOND(6)

694 day 10 hour 39 minute 59.999999 second

+000694 10:39:59.999999

INTERVAL '-999999:59.999999'MINUTE(6) TO SECOND(6)

-(694 day 10 hour 39 minute 59.999999 second)

-000694 10:39:59.999999

Null Value

Null value is an unknown value or an undefined value. NULL value can be a value of any data type. The unknown value of the boolean type is represented as null value.
Null value is defined as a keyword and it is not case-sensitive.

The following is an example of null value representation.

NULL
Null

Comments

Single Line Comment

A single line comment is a comment which starts with -- or //. The single line comment processes a comment from the behind of the comment's symbol to the end of the line.

The following is an example of using a single line comment.

gSQL> SELECT I1, -- I2, I3,
2 I4, I5
3 FROM T1;

I1        I4        I5       
--------- --------- ---------
column i1 column i4 column i5

1 row selected.


gSQL> SELECT I1, // I2, I3,
2 I4, I5
3 FROM T1;

I1        I4        I5       
--------- --------- ---------
column i1 column i4 column i5

1 row selected.

Multiple Line Comment

A multiple line comment is a comment which starts with /* and ends with */. Multiple line comments specify a comment from /* to */, and it can use multiple lines to represent comments.

The following is an example of using multiple line comment.

gSQL> SELECT I1, I2, I3, I4, I5
2  /* Output 
3   all columns of TABLE T1 */

4 FROM T1;

I1        I2        I3        I4        I5       
--------- --------- --------- --------- ---------
column i1 column i2 column i3 column i4 column i5

1 row selected.

Hint Comment

A hint comment is a comment which starts with /*+ and ends with */. Hint comment is similar to multiple line comment, but the difference is that the hint comment has + at the beginning. 
Do not use a space between * and +. If  so,  it will be treated as multiple line comment.
Unlike other comments, a hint comment is specified to be used only at the location which is right after the SELECT keyword. The processing method which a user specified to GOLDILOCKS optimizer is described in the hint comment. For more information, refer to hint clause.

The following is an example of using hint comment.
gSQL> SELECT /*+ FULL(T1) */ * FROM T1;

I1        I2        I3        I4        I5       
--------- --------- --------- --------- ---------
column i1 column i2 column i3 column i4 column i5

1 row selected.

SQL Reserved Words and Keywords

SQL Reserved Words

GOLDILOCKS supports reserved words which are specified as SQL reserved words. The SQL reserved words can not be used without quotation marks other than specified location. However, it is not recommended to use SQL reserved words with quotation marks.

The followings are SQL reserved words of GOLDILOCKS. * marked SQL reserved words are supported by the SQL standard. 
For more information about the list, refer to V$RESERVED_WORDS.
ABSOLUTE
ACCESS
ALL *
ALLOCATE *
ALTER *
AND *
ANY *
ARE *
AS *
ASYMMETRIC *
AT *
AUTHORIZATION *
BEGIN *
BETWEEN *
BOTH *
BY *
CALL *
CASE *
CHECK *
CLOSE *
COLUMN *
COMMENT
COMMIT *
CONNECT *
CONSTRAINT *
CREATE *
CROSS *
CURRENT *
CURRENT_CATALOG *
CURRENT_DATE *
CURRENT_DEFAULT_TRANSFORM_GROUP *
CURRENT_PATH *
CURRENT_ROLE *
CURRENT_ROW *
CURRENT_SCHEMA *
CURRENT_TIME *
CURRENT_TIMESTAMP *
CURRENT_TRANSFORM_GROUP_FOR_TYPE *
CURRENT_USER *
DATABASE
DEALLOCATE *
DECLARE *
DEFAULT *
DELETE *
DEREF *
DESCRIBE *
DETERMINISTIC *
DISCONNECT *
DISTINCT *
DROP *
ELSE *
END *
END_EXEC *
ESCAPE *
EXCEPT *
EXEC *
EXECUTE *
EXISTS *
FALSE *
FETCH *
FILTER *
FIRST
FOR *
FOREIGN *
FREE *
FROM *
FULL *
FUNCTION *
GET *
GLOBAL *
GRANT *
GROUP *
HAVING *
HOLD *
IDENTIFIED
IF
IMMEDIATE
IN *
INDICATOR *
INNER *
INOUT *
INSERT *
INTERSECT *
INTO *
IS *
JOIN *
LAST
LEADING *
LEFT *
LIKE *
LIMIT
LOCAL *
LOCALTIME *
LOCALTIMESTAMP *
MATCH *
MEMBER *
MERGE *
MINUS
NATURAL *
NEW *
NEXT
NOT *
NULL *
OF *
OFFSET *
OLD *
ON *
OPEN *
OR *
ORDER *
OUT *
PREPARE *
PRIMARY *
PRIOR
PROCEDURE *
PROFILE
REF *
REFERENCES *
RELATIVE
RELEASE *
RENAME
RETURN *
RETURNING
RETURNS *
REVOKE *
RIGHT *
ROLLBACK *
ROW *
ROWID
ROWS *
ROW_NUMBER *
SAVEPOINT *
SELECT *
SESSION_USER *
SET *
SOME *
SQL *
SQLEXCEPTION *
SQLSTATE *
SQLWARNING *
START *
SYMMETRIC *
SYNONYM
SYSDATE
SYSTEM *
SYSTEM_USER *
SYSTIME
SYSTIMESTAMP
TABLE *
THEN *
TO *
TRAILING *
TRIGGER *
TRUE *
TRUNCATE *
UNION *
UNIQUE *
UNKNOWN *
UPDATE *
UPPER *
USER *
USING *
VALUES *
VIEW
WHEN *
WHENEVER *
WHERE *
WINDOW *
WITH *
WITHOUT *

SQL Keywords

GOLDILOCKS SQL keywords are not reserved words. However, they are keywords which are internally used by GOLDILOCKS. Therefore, it is not recommended to use GOLDILOCKS SQL keywords because it can decrease the readability of the results.

GOLDILOCKS SQL keywords list can be viewed through V$KEYWORDS.

Compatibility for Syntax Elements

The SQL standard compatibility for syntax element is as follows.

SQL standard compatibility for syntax element

Feature ID

Description

Availability

E021-03

Character literals

O

E131

Null value support (nulls in lieu of values)

O

E161

SQL comments using leading double minus

O

F051-01

DATE data type (including support of DATE literal)

O

F051-02

TIME data type (including support of TIME literal) with fractional seconds precision of at least 0

O

F051-03

TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6

O

F271

Compound character literals

X

F383

Set column not null clause

O

F391

Long identifiers

X

F392

Unicode escapes in identifiers

X

F393

Unicode escapes in literals

X

T023

Compound binary literals

X

T024

Spaces in binary literals

X

T101

Enhanced nullability determination

X

T351

Bracketed comments

X

T591

UNIQUE constraints of possibly null columns

O

X041

Basic table mapping: null absent

X

X042

Basic table mapping: null as nil

X

X051

Advanced table mapping: null absent

X

X052

Advanced table mapping: null as nil

X

X170

XML null handling options

X

X400

Name and identifier mapping

X

Data Type

Numeric Type

Numeric data types are classified according to the storage method and the fractional part representation.

Decimal Numeric Type

This type's precision and scale are based on decimal number. The precision indicates accuracy of the valid digits, and the scale indicates the range of fraction.

Decimal Fixed Point Number Type

The decimal fixed point number type is defined in SQL.

Decimal fixed point number type

Type

Decimal precision

Decimal scale

Refer to

NUMBER( p )

p

0

NUMBER

NUMBER( p, s )

p

s

NUMBER

NUMERIC( p )

p

0

NUMERIC

NUMERIC( p, s )

p

s

NUMERIC

DECIMAL( p )

p

0

NUMERIC type alias

DECIMAL( p, s )

p

s

NUMERIC type alias

DEC( p )

p

0

NUMERIC type alias

DEC( p, s )

p

s

NUMERIC type alias

SMALLINT

5

0

NUMERIC type alias

INTEGER

10

0

NUMERIC type alias

BIGINT

19

0

NUMERIC type alias

INT2

5

0

NUMERIC type alias

INT4

10

0

NUMERIC type alias

INT8

19

0

NUMERIC type alias

Decimal Floating Point Number Type

The decimal floating point number type is defined in SQL.

Decimal floating point number type

Type

Decimal precision

Decimal scale

Refer to

NUMBER

38

N/A

NUMBER

FLOAT( p )

ceil( log10 2p )

N/A

FLOAT

REAL

ceil( log10 224 ) = 8

N/A

FLOAT type alias

DOUBLE

ceil( log10 253 ) = 16

N/A

FLOAT type alias

FLOAT4

ceil( log10 224 ) = 8

N/A

FLOAT type alias

FLOAT8

ceil( log10 253 ) = 16

N/A

FLOAT type alias

Binary Number Type

This type's precision and scale are based on binary number. The precision indicates accuracy of the valid digits, and the scale indicates the range of fraction.

Binary Fixed Point Number Type

The binary fixed point number type refers to the signed integer data type of C language.
1 bit is used to represent the sign bit, and other bits are used to represent the precision, but not any bit is used to represent the scale.
Binary fixed point number type

Type

Binary precision

Binary scale

Refer to

NATIVE_SMALLINT

15

0

NATIVE_SMALLINT

NATIVE_INTEGER

31

0

NATIVE_INTEGER

NATIVE_BIGINT

63

0

NATIVE_BIGINT

Binary Floating Point Number Type

The binary floating point type refers to the float and double data type in C language.
1 bit is used to represent the sign bit, and other bits are used to represent the precision and scale.
Binary floating point number type

Type

Binary precision

Binary scale

Refer to

NATIVE_REAL

23

8

NATIVE_REAL

NATIVE_DOUBLE

52

11

NATIVE_DOUBLE

The precision and scale of binary floating point type is subject to change depending on the influence of the compiler and OS.

CHARACTER STRING Type

CHARACTER STRING data types are classified according to whether it is a variable length string and the maximum length of string.

BINARY STRING Type

BINARY STRING data types are classified according to whether it is a variable length binary string and the maximum length of binary string.

Date/ Time Type

Date/ time data type specifies the year, month, day, hour, minute, second, time zone offset in accordance with their representation method. 
Date/ time data type has DATE, TIME, TIMESTAMP types.

INTERVAL Type

INTERVAL data type specifies the time interval. 
It specifies the time interval of the year, month, day, hour, minute, second in accordance with their representation method.
INTERVAL data types are classified to the YEAR TO MONTH family type and the DAY TO SECOND family type, according to the range of value representation.

BOOLEAN Type

The BOOLEAN data type stores values of TRUE, FALSE, UNKNOWN. UNKNOWN value is represented as a null value. All expressions used as conditions return the BOOLEAN value and the column or the value defined as a BOOLEAN data type can be used as a condition.

For more information, refer to BOOLEAN.

ROWID Type

All records stored in the database have unique location information. The record identifier (ROWID) is used to distinguish each record.
ROWID data type is used to store and manage the record identifier (ROWID).
Record identifier (ROWID) is obtained by the query using the ROWID pseudo column.
For more information, refer to ROWID.

Type Comparison

Comparing two types is executed on the basis of one representative type. If the comparison target type is different from the representative type, then the comparison can go through a type conversion.
The representative types for type comparison defines the representative type for comparing two types.

The following table describes target type conversion for comparison per each representative type.

The followings are abbreviations which are used for the type comparison.

In the type comparison table, the built-in data types are represented by an abbreviated word enclosed in double quotes ("").

The representative types for type comparison

Data

type

C

H

A

R

V

A

R

C

H

A

R

L

O

N

G


V

A

R

C

H

A

R

B

I

N

A

R

Y

V

A

R

B

I

N

A

R

Y

L

O

N

G


V

A

R

B

I

N

A

R

Y

N

A

T

I

V

E


S

M

A

L

L

I

N

T

N

A

T

I

V

E


I

N

T

E

G

E

R

N

A

T

I

V

E


B

I

G

I

N

T

N

A

T

I

V

E


R

E

A

L

N

A

T

I

V

E


D

O

U

B

L

E

N

U

M

B

E

R

N

U

M

E

R

I

C

F

L

O

A

T

D

A

T

E

T

I

M

E

T

I

M

E




T

Z

T

I

M

E

S

T

A

M

P

T

I

M

E

S

T

A

T

M

P



T

Z

I

N

T

E

R

V

A

L


Y

M

I

N

T

E

R

V

A

L


D

S

B

O

O

L

E

A

N

R

O

W

I

D

CHAR

VC

VC

LC

NU

NU

NU

NU

ND

NU

NU

NU

DA

TI

TZ

TS

SZ

YM

DS

BO

RI

VARCHAR

VC

VC

LC

NU

NU

NU

NU

ND

NU

NU

NU

DA

TI

TZ

TS

SZ

YM

DS

BO

RI

LONG VARCHAR

LC

LC

LC

NU

NU

NU

NU

ND

NU

NU

NU

DA

TI

TZ

TS

SZ

YM

DS

BO

RI

BINARY

VB

VB

LB

VARBINARY

VB

VB

LB

LONG VARBINARY

LB

LB

LB

NATIVE_SMALLINT

NU

NU

NU

NB

NB

NB

ND

ND

NU

NU

NU

YM

DS

NATIVE_INTEGER

NU

NU

NU

NB

NB

NB

ND

ND

NU

NU

NU

YM

DS

NATIVE_BIGINT

NU

NU

NU

NB

NB

NB

ND

ND

NU

NU

NU

YM

DS

NATIVE_REAL

NU

NU

NU

ND

ND

ND

ND

ND

NU

NU

NU

NATIVE_DOUBLE

ND

ND

ND

ND

ND

ND

ND

ND

ND

ND

ND

NUMBER

NU

NU

NU

NU

NU

NU

NU

ND

NU

NU

NU

YM

DS

NUMERIC

NU

NU

NU

NU

NU

NU

NU

ND

NU

NU

NU

YM

DS

FLOAT

NU

NU

NU

NU

NU

NU

NU

ND

NU

NU

NU

YM

DS

DATE

DA

DA

DA

DA

TS

SZ

TIME

TI

TI

TI

TI

TZ

TIME_TZ

TZ

TZ

TZ

TZ

TZ

TIMESTAMP

TS

TS

TS

TS

TS

SZ

TIMESTAMP_TZ

SZ

SZ

SZ

SZ

SZ

SZ

INTERVAL_YM

YM

YM

YM

YM

YM

YM

YM

YM

YM

YM

INTERVAL_DS

DS

DS

DS

DS

DS

DS

DS

DS

DS

DS

BOOLEAN

BO

BO

BO

BO

ROWID

RI

RI

RI

RI

Type conversion for the VC comparison

Source type

Converted type

CHAR

CHAR (no conversion)

VARCHAR

VARCHAR (no conversion)

Type conversion for the LC comparison

Source type

Converted type

CHAR

CHAR (no conversion)

VARCHAR

VARCHAR (no conversion)

LONG VARCHAR

LONG VARCHAR (no conversion)

Type conversion for the VB comparison

Source type

Converted type

BINARY

BINARY (no conversion)

VARBINARY

VARBINARY (no conversion)

Type conversion for the LB comparison

Source type

Converted type

BINARY

BINARY (no conversion)

VARBINARY

VARBINARY (no conversion)

LONG VARBINARY

LONG VARBINARY (no conversion)

Type conversion for the NB comparison

Source type

Converted type

CHAR

NATIVE_BIGINT

VARCHAR

NATIVE_BIGINT

LONG VARCHAR

NATIVE_BIGINT

NATIVE_SMALLINT

NATIVE_SMALLINT (no conversion)

NATIVE_INTEGER

NATIVE_INTEGER (no conversion)

NATIVE_BIGINT

NATIVE_BIGINT (no conversion)

Type conversion for the ND comparison

Source type

Converted type

CHAR

NATIVE_DOUBLE

VARCHAR

NATIVE_DOUBLE

LONG VARCHAR

NATIVE_DOUBLE

NATIVE_SMALLINT

NATIVE_SMALLINT (no conversion)

NATIVE_INTEGER

NATIVE_INTEGER (no conversion)

NATIVE_BIGINT

NATIVE_BIGINT (no conversion)

NATIVE_REAL

NATIVE_REAL (no conversion)

NATIVE_DOUBLE

NATIVE_DOUBLE (no conversion)

NUMBER

NUMBER (no conversion)

NUMERIC

NUMERIC (no conversion)

FLOAT

FLOAT (no conversion)

Type conversion for the NU comparison

Source type

Converted type

CHAR

NUMBER

VARCHAR

NUMBER

LONG VARCHAR

NUMBER

NATIVE_SMALLINT

NATIVE_SMALLINT (no conversion)

NATIVE_INTEGER

NATIVE_INTEGER (no conversion)

NATIVE_BIGINT

NATIVE_BIGINT (no conversion)

NATIVE_REAL

NATIVE_REAL (no conversion)

NATIVE_DOUBLE

NATIVE_DOUBLE (no conversion)

NUMBER

NUMBER (no conversion)

NUMERIC

NUMERIC (no conversion)

FLOAT

FLOAT (no conversion)

Type conversion for the DA comparison

Source type

Converted type

CHAR

DATE

VARCHAR

DATE

LONG VARCHAR

DATE

DATE

DATE (no conversion)

Type conversion for the TI comparison

Source type

Converted type

CHAR

TIME

VARCHAR

TIME

LONG VARCHAR

TIME

TIME

TIME (no conversion)

Type conversion for the TZ comparison

Source type

Converted type

CHAR

TIME_TZ

VARCHAR

TIME_TZ

LONG VARCHAR

TIME_TZ

TIME

TIME_TZ

TIME_TZ

TIME_TZ (no conversion)

Type conversion for the TS comparison

Source type

Converted type

CHAR

TIMESTAMP

VARCHAR

TIMESTAMP

LONG VARCHAR

TIMESTAMP

DATE

DATE (no conversion)

TIMESTAMP

TIMESTAMP (no conversion)

Type conversion for the SZ comparison

Source type

Converted type

CHAR

TIMESTAMP_TZ

VARCHAR

TIMESTAMP_TZ

LONG VARCHAR

TIMESTAMP_TZ

DATE

TIMESTAMP_TZ

TIMESTAMP

TIMESTAMP_TZ

TIMESTAMP_TZ

TIMESTAMP_TZ (no conversion)

Type conversion for the YM comparison

Source type

Converted type

CHAR

INTERVAL_YM

VARCHAR

INTERVAL_YM

LONG VARCHAR

INTERVAL_YM

NATIVE_SMALLINT

INTERVAL_YM

NATIVE_INTEGER

INTERVAL_YM

NATIVE_BIGINT

INTERVAL_YM

NUMBER

INTERVAL_YM

NUMERIC

INTERVAL_YM

FLOAT

INTERVAL_YM

INTERVAL_YM

INTERVAL_YM (no conversion)

Type conversion for the DS comparison

Source type

Converted type

CHAR

INTERVAL_DS

VARCHAR

INTERVAL_DS

LONG VARCHAR

INTERVAL_DS

NATIVE_SMALLINT

INTERVAL_DS

NATIVE_INTEGER

INTERVAL_DS

NATIVE_BIGINT

INTERVAL_DS

NUMBER

INTERVAL_DS

NUMERIC

INTERVAL_DS

FLOAT

INTERVAL_DS

INTERVAL_DS

INTERVAL_DS (no conversion)

Type conversion for the BO comparison

Source type

Converted type

CHAR

BOOLEAN

VARCHAR

BOOLEAN

LONG VARCHAR

BOOLEAN

BOOLEAN

BOOLEAN (no conversion)

Type conversion for the RI comparison

Source type

Converted type

CHAR

ROWID

VARCHAR

ROWID

LONG VARCHAR

ROWID

ROWID

ROWID (no conversion)

Type Conversion

Type conversions are classified into implicit type conversion and explicit type conversion.

The availability of type conversion describes the availability of data type conversion from a type to another type.

In the type conversion table, the built-in data types are represented by an abbreviated string enclosed in double quotes ("").

The availability of type conversion

Data

type

C

H

A

R

V

A

R

C

H

A

R

L

O

N

G


V

A

R

C

H

A

R

B

I

N

A

R

Y

V

A

R

B

I

N

A

R

Y

L

O

N

G


V

A

R

B

I

N

A

R

Y

N

A

T

I

V

E


S

M

A

L

L

I

N

T

N

A

T

I

V

E


I

N

T

E

G

E

R

N

A

T

I

V

E


B

I

G

I

N

T

N

A

T

I

V

E


R

E

A

L

N

A

T

I

V

E


D

O

U

B

L

E

N

U

M

B

E

R

N

U

M

E

R

I

C

F

L

O

A

T

D

A

T

E

T

I

M

E

T

I

M

E




T

Z

T

I

M

E

S

T

A

M

P

T

I

M

E

S

T

A

T

M

P



T

Z

I

N

T

E

R

V

A

L


Y

M

I

N

T

E

R

V

A

L


D

S

B

O

O

L

E

A

N

R

O

W

I

D

CHAR

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

VARCHAR

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

LONG VARCHAR

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

O

BINARY

O

O

O

VARBINARY

O

O

O

LONG VARBINARY

O

O

O

NATIVE_SMALLINT

O

O

O

O

O

O

O

O

O

O

O

O

O

NATIVE_INTEGER

O

O

O

O

O

O

O

O

O

O

O

O

O

NATIVE_BIGINT

O

O

O

O

O

O

O

O

O

O

O

O

O

NATIVE_REAL

O

O

O

O

O

O

O

O

O

O

O

NATIVE_DOUBLE

O

O

O

O

O

O

O

O

O

O

O

NUMBER

O

O

O

O

O

O

O

O

O

O

O

O

O

NUMERIC

O

O

O

O

O

O

O

O

O

O

O

O

O

FLOAT

O

O

O

O

O

O

O

O

O

O

O

O

O

DATE

O

O

O

O

O

O

TIME

O

O

O

O

O

TIME_TZ

O

O

O

O

O

TIMESTAMP

O

O

O

O

O

O

O

TIMESTAMP_TZ

O

O

O

O

O

O

O

O

INTERVAL_YM

O

O

O

O

O

O

O

O

O

O

INTERVAL_DS

O

O

O

O

O

O

O

O

O

O

BOOLEAN

O

O

O

O

ROWID

O

O

O

O

Type Combination

When Type Combination Is Required

A CASE operator and a set operator have many expressions as a result of operation.
Each expression can have different types each other as follows. In this case, the result type should be determined.
SELECT CASE expr WHEN expr THEN char(3)
                 WHEN expr THEN char(5)
                 ELSE char(1)
       END   
  FROM t1;
SELECT float_column
  FROM t1
UNION ALL
SELECT number_precision_column
  FROM t2
UNION ALL
SELECT native_integer_column
  FROM t3;

A rule is applied to determine the result type according to the type combination. The following is an example of applying the rule.

Result Type Combination Rule

Each expression's data type should be the same family type which is available to combine.

The result types determined by result type combination rule are described in the following table.

The following abbreviations are used to describe the result type combination rule.

In result type combination table, the built-in data types are represented by an abbreviated word enclosed in double quotes ("").

The result type determined by result type combination rule

Data

type

C

H

A

R

V

A

R

C

H

A

R

L

O

N

G


V

A

R

C

H

A

R

B

I

N

A

R

Y

V

A

R

B

I

N

A

R

Y

L

O

N

G


V

A

R

B

I

N

A

R

Y

N

A

T

I

V

E


S

M

A

L

L

I

N

T

N

A

T

I

V

E


I

N

T

E

G

E

R

N

A

T

I

V

E


B

I

G

I

N

T

N

A

T

I

V

E


R

E

A

L

N

A

T

I

V

E


D

O

U

B

L

E

N

U

M

B

E

R

N

U

M

E

R

I

C

F

L

O

A

T

D

A

T

E

T

I

M

E

T

I

M

E




T

Z

T

I

M

E

S

T

A

M

P

T

I

M

E

S

T

A

T

M

P



T

Z

I

N

T

E

R

V

A

L


Y

M

I

N

T

E

R

V

A

L


D

S

B

O

O

L

E

A

N

R

O

W

I

D

CHAR

VC

VC

VARCHAR

VC

VC

LONG VARCHAR

LC

BINARY

VB

VB

VARBINARY

VB

VB

LONG VARBINARY

LB

NATIVE_SMALLINT

NS

NI

NB

ND

ND

NU

NU

NU

NATIVE_INTEGER

NI

NI

NB

ND

ND

NU

NU

NU

NATIVE_BIGINT

NB

NB

NB

ND

ND

NU

NU

NU

NATIVE_REAL

ND

ND

ND

NR

ND

ND

ND

ND

NATIVE_DOUBLE

ND

ND

ND

ND

ND

ND

ND

ND

NUMBER

NU

NU

NU

ND

ND

NU

NU

NU

NUMERIC

NU

NU

NU

ND

ND

NU

NU

NU

FLOAT

NU

NU

NU

ND

ND

NU

NU

FL

DATE

DA

TS

SZ

TIME

TI

TZ

TIME_TZ

TZ

TZ

TIMESTAMP

TS

TS

SZ

TIMESTAMP_TZ

SZ

SZ

SZ

INTERVAL_YM

YM

INTERVAL_DS

DS

BOOLEAN

BO

ROWID

RI

Compatibility for Data Type

The SQL standard compatibility for data type is as follows.

SQL standard compatibility for data type

Feature ID

Description

Availability

B033

Untyped SQL-invoked function arguments

X

E011-01

INTEGER and SMALLINT data types

O

E011-02

REAL, DOUBLE PRECISION, and FLOAT data types

O

E011-03

DECIMAL and NUMERIC data types

X

E011-04

Arithmetic operators

O

E011-05

Numeric comparison

O

E011-06

Implicit casting among the numeric data types

O

E021-01

CHARACTER data type

O

E021-02

CHARACTER VARYING data type

O

E021-03

Character literals

O

E021-04

CHARACTER_LENGTH function

O

E021-05

OCTET_LENGTH function

O

E021-06

SUBSTRING function

O

E021-07

Character concatenation

O

E021-08

UPPER and LOWER functions

O

E021-09

TRIM function

O

E021-10

Implicit casting among the fixed-length and variable-length character string types

O

E021-11

POSITION function

O

E021-12

Character comparison

O

E071-05

Columns combined via table operators need not have exactly the same data type

O

F051-01

DATE data type (including support of DATE literal)

O

F051-02

TIME data type (including support of TIME literal) with fractional seconds precision of at least 0

O

F051-03

TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6

O

F051-04

Comparison predicate on DATE, TIME, and TIMESTAMP data types

X

F051-05

Explicit CAST between datetime types and character string types

O

F054

TIMESTAMP in DATE type precedence list

X

F382

Alter column data type

O

F611

Indicator data types

X

F741

Referential MATCH types

X

J521

JDBC data types

X

J622

external Java types

X

S011-01

USER_DEFINED_TYPES view

X

S023

Basic structured types

X

S024

Enhanced structured types

X

S025

Final structured types

X

S026

Self-referencing structured types

X

S041

Basic reference types

X

S043

Enhanced reference types

X

S051

Create table of type

X

S071

SQL paths in function and type name resolution

X

S091-01

Arrays of built-in data types

X

S091-02

Arrays of distinct types

X

S092

Arrays of user-defined types

X

S094

Arrays of reference types

X

S161

Subtype treatment

X

S162

Subtype treatment for references

X

S201-02

Array as result type of functions

X

S231

Structured type locators

X

S261

Specific type method

X

S272

Multisets of user-defined types

X

S274

Multisets of reference types

X

S281

Nested collection types

X

S401

Distinct types based on array types

X

S402

Distinct types based on distinct types

X

T021

BINARY and VARBINARY data types

O

T022

Advanced support for BINARY and VARBINARY data types

O

T031

BOOLEAN data type

O

T041

Basic LOB data type support

X

T042

Extended LOB data type support

X

T051

Row types

X

T071

BIGINT data type

O

T201

Comparable data types for referential constraints

X

T322

Declared data type attributes

X

X010

XML type

X

X011

Arrays of XML type

X

X012

XMultisets of XML type

X

X013

Distinct types of XML type

X

X014

Attributes of XML type

X

X015

Fields of XML type

X

X181

XML(DOCUMENT(UNTYPED)) type

X

X182

XML(DOCUMENT(ANY)) type

X

X190

XML(SEQUENCE) type

X

X191

XML(DOCUMENT(XMLSCHEMA)) type

X

X192

XML(CONTENT(XMLSCHEMA)) type

X

X231

XML(CONTENT(UNTYPED)) type

X

X232

XML(CONTENT(ANY)) type

X

X251

Persistent XML values of XML(DOCUMENT(UNTYPED)) type

X

X252

Persistent XML values of XML(DOCUMENT(ANY)) type

X

X253

Persistent XML values of XML(CONTENT(UNTYPED)) type

X

X254

Persistent XML values of XML(CONTENT(ANY)) type

X

X255

Persistent XML values of XML(SEQUENCE) type

X

X256

Persistent XML values of XML(DOCUMENT(XMLSCHEMA)) type

X

X257

Persistent XML values of XML(CONTENT(XMLSCHEMA)) type

X

X260

XML type: ELEMENT clause

X

X261

XML type: NAMESPACE without ELEMENT clause

X

X263

XML type: NO NAMESPACE with ELEMENT clause

X

X264

XML type: schema location

X

X410

Alter column data type: XML type

X

Format String

Format string defines the format which is used when a numeric type or date/time type is converted to a character string or when a character string is converted to a numeric types or date/time type.
Format strings are classified according to the type.
• Numeric data type: Refer to Number Format String.
• Date/time type: Refer to Datetime Format String.

Number Format String

Number format string defines the format which is used when a numeric type is converted to a character string type, or when a character string type is converted to a numeric type.
Number format string is used as an argument of the functions such as TO_CHAR( number ), TO_NUMBER, TO_NATIVE_REAL, TO_NATIVE_DOUBLE.
Number format string can specify multiple format elements according to the desired format.
All number format elements are rounded off to fit the format.
If the number of digits before the decimal point of the value to be converted is bigger than the number of digits specified in the format string, then they are replaced with '#' character.
If the format element representing the sign of MI, S, PR is not specified, a negative number returns - sign and a positive number returns a white space to the front of the number.
Number format elements

Format

element

Example

Description

, (comma)

9,999

It returns a comma to the specified position.

Multiple commas can be specified.

Format string can not begin with a comma, and it can not come after the decimal point (.).

. (period)

99.99

It returns a decimal point(.) to the specified position.

The decimal point in the format string can be specified only once.

$

$9999

It returns the $ sign to the front of the number.

0

0999

9990

It returns zero(0) to the front of or to the end of the number.

If the number of digits of the value to be converted is smaller than the number of digits to the zero position of the format string, then the gap is filled with zero(0)s and is returned.

9

9999

It returns a white space and numbers according to the sign and the number of specified 9.

If the number of digits of the value to be converted is smaller than the number of the specified 9, then the gap is filled with white spaces and is returned.

For a positive number, a white space is returned to the front of the number. For a negative number, '-' symbol is returned to the front of the number.

If the value before the format string's decimal point is 0, then 0 is returned as a white space.

e.g. TO_CHAR( 0.123, '9.999' ) → .123

e.g. TO_CHAR( 0, '9' ) → 0

B

B9999

If the value is zero, it returns a white space.

EEEE

9.9EEEE

It returns in exponential notation.

It can be at the end of format string or it can be in front of S, MI, PR.

It can not be specified together with a comma (,).

MI

9999MI

For a positive number, a white space is returned to the end of the number. For a negative number, '-' symbol is returned to the end of the number.

It can be specified only at the end of format string and it can not be specified together with S, PR.

PR

9999PR

For a positive number, white spaces are returned to the beginning and end of the number.

For a negative number, it returns the number into the inside of angle brackets. <number>

It can be specified only at the end of format string, and it can not be specified together with S, MI.

RN

rn

RN

rn

Roman numerals are converted to uppercase and returned. (RN)

Roman numerals are converted to lowercase and returned. (rn)

Only the numbers between 1 ~ 3999 are returned.

It can be specified together only with FM format element, but it can not be specified with any other format elements.

It can not be used in TO_NUMBER function.

S

S9999

9999S

For a positive number, '+' symbol is returned to the front of the number. For a negative number, '-' symbol is returned to the front of the number.(S9999

For a positive number, '+' symbol is returned to the end of the number. For a negative number, '-' symbol is returned to the end of the number.(9999S)

It can be specified only at the beginning of format string or at the end of format string.

It can not be specified together with MI, PR.

V

999V99

10n(n: the digit number of 9 after V format element) multiplied by the value is returned.

It can not specified together with the decimal point (.).

It can not be used in TO_NUMBER function.

X

XXXX

xxxx

It returns the white space and hexadecimal number according to the digit number of the specified X.

It converts an integer value to the hexadecimal number, and returns it. (A non-integer value is rounded off to make it to an integer value)

XXX returns hexadecimal uppercase letters and xxxx returns hexadecimal lowercase letters.

If the number of the converted hexadecimal digit is smaller than the number of the specified X, then the gap is filled with white spaces and is returned.

Only 0 and positive integers are processed, and negative numbers are replaced with '#'.

It can be specified together only with format element 0 and FM, but it can not be specified with any other format elements.

FM

FM

It removes the front and end white spaces, and returns left aligned effect.

It removes the front and end white spaces of the number.

It removes zero(0)s under the decimal point which are added by 9 format element.

Followings are examples of using number format string.

TO_CHAR( 12345, '99,999' )           : ' 12,345'
TO_CHAR( 123456789, '999,999,999' )  : ' 123,456,789'
TO_CHAR( 12.345, '99.999' )          : ' 12.345'
TO_CHAR( 1234.56, '$9,999.99' )      : ' $1,234.56'
TO_CHAR( 123, '099999' )             : ' 000123'
TO_CHAR( 0.2, '0.9' )                : ' 0.2'
TO_CHAR( 123.45, '999999.99' )       : '    123.45'
TO_CHAR( -123.45, '999999.99' )      : '   -123.45'
TO_CHAR( 123.45, 'FM999999.99' )     : '123.45'
TO_CHAR( -123.45, 'FM999999.99' )    : '-123.45'
TO_CHAR( 12345.67, '999.99' )        : '#######'
TO_CHAR( 123.100567, '999.999' )     : ' 123.101'
TO_CHAR( 0.2, '90.99' )              : '  0.20'
TO_CHAR( 0.2, '99.99' )              : '   .20'
TO_CHAR( 0, '90.99' )                : '  0.00'
TO_CHAR( 0, 'B90.99' )               : '      '
TO_CHAR( 123.45, '9.9EEEE' )         : '  1.2E+02'
TO_CHAR( 123.45, '999.99MI' )        : '123.45 '
TO_CHAR( -123.45, '999.99MI' )       : '123.45-'
TO_CHAR( 123.45, '999.99PR' )        : ' 123.45 '
TO_CHAR( -123.45, '999.99PR' )       : '<123.45>'
TO_CHAR( 123, 'RN' )                 : '         CXXIII'
TO_CHAR( 123, 'rn' )                 : '         cxxiii'
TO_CHAR( 123, 'FMRN' )               : 'CXXIII'
TO_CHAR( 4000, 'RN' )                : '###############'
TO_CHAR( 123.45, 'S999.99' )         : '+123.45'
TO_CHAR( -123.45, 'S999.99' )        : '-123.45'
TO_CHAR( 123.45, '999.99S' )         : '123.45+'
TO_CHAR( -123.45, '999.99S' )        : '123.45-'
TO_CHAR( 123.45, '999V999' )         : ' 123450'
TO_CHAR( 123, 'XX' )                 : ' 7B'
TO_CHAR( 123, 'xx' )                 : ' 7b'
TO_CHAR( 45678, 'XXXXXXX' )          : '    B26E'
TO_CHAR( 45678, 'FMXXXXXXX' )        : 'B26E'
TO_CHAR( 123.45, '99,999.999999' )   : '    123.450000'
TO_CHAR( 123.45, 'FM99,999.999999' ) : '123.45'

Datetime Format String

Datetime format string defines the format which is used when a date/time type is converted to a character string type, or when a character string type is converted to a date/time type.
Datetime format string is used as an argument of the functions such as TO_CHAR( datetime ), TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_WITH_TIME_ZONE, TO_TIME, TO_TIME_WITH_TIME_ZONE.
For datetime format string, if the format string is not specified, then the default value is used. The default value of each type is specified in the session property (NLS _ * _ FORMAT).
NLS * _FORMAT values can be changed by using ALTER SESSION SET property_name.
In datetime format string, multiple format elements can be specified upon the desired representation.
Datetime format elements

Format

element

Whether to use TO_*

datetime

Description

-

/

,

.

;

:

"text"

Special characters

Y

It returns the format element character to the specified location.

AD

A.D.

Y

AD with or without periods.

AM

A.M.

Y

AM with or without periods.

BC

B.C.

Y

BC with or without periods.

CC

N

Century

If the last two digits of the four digits year is 01~ 99, the value which is added by one to the first two digits is returned. (e.g. If the year is 2005, 21 is returned.)

If the last two digits of the four digits year is 00, the first two digits value is returned. (e.g. If the year is 2000, 20 is returned.)

D

Y

It returns the sequence of the day in a week. (1 ~ 7)

Sunday is 1, saturday is 7, and so on.

DAY

Day

day

Y

It returns the day of the week. (e.g. SUNDAY)

  • DAY: The day which is all in uppercase is returned.

  • Day: The day whose first character is uppercase and others are lowercase is returned.

  • day: The day which is all in lowercase is returned.

DD

Y

It returns the sequence of the day in a month. (1 ~ 31)

DDD

Y

It returns the sequence of the day in a year. (1 ~ 366)

DY

Dy

dy

Y

It returns the abbreviated word for the day of the week. (e.g. SUN)

  • DY: The day which is all in uppercase is returned.

  • Dy: The day whose first character is uppercase and others are lowercase is returned.

  • dy: The day which is all in lowercase is returned.

FF[1..6]

Y

It returns fractional seconds as many as the number of the specified digits (1-6) after FF.

If the number is not specified, the default value is 6. (FF is equal to FF6.)

If the number of fractional seconds digit is bigger than the number specified after FF, then it is rounded down.

If the number of fractional seconds digit is smaller than the number specified after FF, then zero(0) is added according to the specified number.

It can not be used in DATE type.

HH

HH12

Y

The hour (1 ~ 12)

HH24

Y

The hour (0 ~ 23)

IW

N

The week containing the first thursday of the year designated as the calendar week by ISO 8601 standards (1 ~ 52 weeks or 1 ~ 53 weeks) becomes the first week.

  • The calendar week starts from monday.

  • The first calendar week includes January 4th.

  • The first calendar week may includes December 29th, 30th, and 31st.

  • The last calendar week may include January 1st, 2nd, and 3rd.

IYYY

N

The 4 digits year embracing the calendar week defined by ISO 8601 standards.

IYY

IY

I

N

The 3 digits year embracing the calendar week defined by ISO 8601 standards.

The 2 digits year embracing the calendar week defined by ISO 8601 standards.

The single digit year embracing the calendar week defined by ISO 8601 standards.

J

Y

Julian day: The number of days since BC 4714-11-24

MI

Y

Minute (0 ~ 59)

MM

Y

Month (01 ~ 12), January(01)~December(12)

MON

Mon

mon

Y

The abbreviated word for the month. (e.g. JAN)

  • MON: The month which is all in uppercase is returned.

  • Mon: The month whose first character is uppercase and others are lowercase is returned.

  • mon: The month which is all in lowercase is returned.

MONTH

Month

month

Y

The month name (e.g. JANUARY)

  • MONTH: All uppercase month name is returned.

  • Month: The month name that only the first letter is uppercase and others are lowercase is returned.

  • month: The month of which is all in lowercase is returned.

PM

P.M.

Y

PM with or without periods.

Q

N

The quarter of the year (1 ~ 4)

January to March is 1 and October to December is 4.

RM

Rm

rm

Y

It returns the roman numeral month. (e.g. I)

  • RM: The month which is all in uppercase is returned.

  • Rm: The month whose first character is uppercase and others are lowercase is returned.

  • rm: The month which is all in lowercase is returned.

RR

Y

Adjusted two digit year

The two digit year represented by RR can be converted to four digit year as follows.

  • When the two digit year represented by RR is 00~49:

    • If the last two digits of the current year is 00~50,

      • the four digit year is represented using the first two digits of the current year and the two digits which is represented by RR.

    • If the last two digits of the current year is 51~99,

      • the four digit year is represented using "the first two digits of the current year+1" and the two digits which is represented by RR.

  • When the two digit year represented by RR is 50~99:

    • If the last two digits of the current year is 00~50,

      • the four digit year is represented using "the first two digit of the current year - 1" and the two digits which is represented by RR.

    • If the last two digit of the current year is 51~99,

      • the four digit year is expressed using the first two digit of the current year and the two digits which is represented by RR.

RRRR

Y

Adjusted four digit year

Two digit or four digit can be input.

Two digit input is processed in the same way as RR.

SS

Y

Second (0 ~ 59)

SSSSS

Y

Seconds since last midnight (0 ~ 86399)

TZH

Y

Time Zone Hour

It can not be used in DATE, TIMESTAMP, TIME types. It is available in TIMESTAMP WITH TIME ZONE, TIME WITH TIME ZONE types.

TZM

Y

Time Zone Minute

It can not be used in DATE, TIMESTAMP, TIME types. It can be used only in TIMESTAMP WITH TIME ZONE, TIME WITH TIME ZONE types.

WW

N

The sequence of the week in a year. (1~ 53)

The first week 1 starts on the first day of the year and continues to the seventh day of the year.

W

N

The sequence of the week in a month. (1 ~ 5)

The first week 1 starts on the first day of the month and ends on the seventh day.

Y,YYY

Y

It returns the year with comma in the Y,YYY form.

YYYY

SYYYY

Y

Four digit year.

If it is BC, SYYYY returns '-' signal.

YYY

YY

Y

Y

  • YYY: The last three digit year of the current year

  • YY: The last two digit year of the current year

  • Y: The last one digit year of the current year

The followings are examples of using datetime format string.

* - / , . ; : "text" Special character 
  • TO_CHAR( TO_DATE( '2012-07-15 03:30:30', 'YYYY-MM-DD HH12:MI:SS' ),
             'YYYY/MM/DD HH12:MI:SS' )
    ==> '2012/07/15 03:30:30'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 
             'YYYY"year" DDD"th day"' )
    ==> '2012year 197th day'
* AD
  • TO_CHAR( TO_DATE( '2012-07-15 AD', 'YYYY-MM-DD AD' ), 'YYYY AD' )
    ==> '2012 AD'
  • TO_CHAR( TO_DATE( '0001-01-01 BC', 'YYYY-MM-DD AD'), 'YYYY AD' )
    ==> '0001 BC'

* BC 
  • TO_CHAR( TO_DATE( '0001-01-01 BC', 'YYYY-MM-DD BC'), 'YYYY BC' )
    ==> '0001 BC'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'YYYY BC' )
    ==> '2012 AD'
* AM
  • TO_CHAR( TO_DATE( '2012-07-15 03:30:30 AM',
                      'YYYY-MM-DD HH12:MI:SS AM' ),
             'HH12:MI:SS AM' )
    ==> '03:30:30 AM'
  • TO_CHAR( TO_DATE( '2012-07-15 21:30:30', 'YYYY-MM-DD HH24:MI:SS' ),
             'HH12:MI:SS AM' )
    ==> '09:30:30 PM'

* PM 
  • TO_CHAR( TO_DATE( '2012-07-15 03:30:30', 
                      'YYYY-MM-DD HH24:MI:SS' ), 
             'HH12:MI:SS PM' )
    ==> '03:30:30 AM'
  • TO_CHAR( TO_DATE( '2012-07-15 09:30:30 PM', 
                      'YYYY-MM-DD HH12:MI:SS PM' ), 
             'HH12:MI:SS PM' )
    ==> '09:30:30 PM'
* CC
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'CC' )
    ==> '21'
* D
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'D' )
    ==> '1'

* DD
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DD' )
    ==>  '15'

* DDD
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DDD' )
    ==> '197'
* DAY
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DAY' )
    ==> 'SUNDAY   '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Day' )
    ==> 'Sunday   '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'day' )
    ==> 'sunday   '

* DY
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'DY' )
    ==> 'SUN'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Dy' )
    ==> 'Sun'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'dy' )
    ==> 'sun'
* FF[1 ... 6]
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'FF' )
    ==> '123456'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'FF5' )
    ==> '12345'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.9',
                           'YYYY-MM-DD HH24:MI.SS.FF1' ) , 
             'FF6' )
    ==> '900000'
* HH HH12 HH24
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 03:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'HH12' )
    ==> '03'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'HH12' )
    ==> '11'
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'HH24' )
    ==> '23'
* IW
  • TO_CHAR( DATE'2016-01-01', 'IW' )
    ==> 53
  • TO_CHAR( DATE'2014-12-30', 'IW' )
    ==> 01
* IYYY
  • TO_CHAR( DATE'2016-01-01', 'IYYY' )
    ==> 2015
  • TO_CHAR( DATE'2014-12-30', 'IYYY' )
    ==> 2015

* IYY
  • TO_CHAR( DATE'2016-01-01', 'IYY' )
    ==> 015

* IY
  • TO_CHAR( DATE'2016-01-01', 'IY' )
    ==> 15

* I
  • TO_CHAR( DATE'2016-01-01', 'I' )
    ==> 5
* J
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'J' )
    ==> '2456124'
  • TO_CHAR( TO_DATE( '2456124', 'J' ), 'YYYY-MM-DD' )
    ==> '2012-07-15'
* MI
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
             'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'MI' ) 
    ==> '30'
* MM
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'MM' )
    ==> '07'
* MON
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'MON' )
    ==> 'JUL'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Mon' )
    ==> 'Jul'
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'mon' )
    ==> 'jul'

* MONTH
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'MONTH' )
    ==> 'JULY     '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Month' )
    ==> 'July     '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'month' )
    ==> 'july     '
* Q
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Q' )
    ==> '3'
* RM
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'RM' )
    ==> 'VII ' 
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'Rm' )
    ==> 'Vii '
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'rm' )
    ==> 'vii '
* RR, RRRR ( The current year is 2014. )
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '2049'
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '2049'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '1950'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '1950'
  • TO_CHAR( TO_DATE( '50-07-15', 'YY-MM-DD' ), 'RRRR' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '49-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '2049'
  • TO_CHAR( TO_DATE( '50-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '1950'

* RR, RRRR ( The current year is 2051. )
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '2149'
  • TO_CHAR( TO_DATE( '49-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '2149'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'RRRR' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '50-07-15', 'RR-MM-DD' ), 'YYYY' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '50-07-15', 'YY-MM-DD' ), 'RRRR' )
    ==> '2050'
  • TO_CHAR( TO_DATE( '49-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '2149'
  • TO_CHAR( TO_DATE( '50-07-15', 'RRRR-MM-DD' ), 'YYYY' )
    ==> '2050'
* SS
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456',
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'SS' )
    ==> '45' 

* SSSSS
  • TO_CHAR( TO_TIMESTAMP( '2012-07-15 23:30:45.123456',
                           'YYYY-MM-DD HH24:MI:SS.FF6' ), 
             'SSSSS' )
    ==> '84645'
* TZH 
  • TO_CHAR( TO_TIMESTAMP_TZ( '2012-07-15 23:30:45.123456 +09:00',
                              'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ),
             'TZH' )
    ==> '+09'

* TZM
  • TO_CHAR( TO_TIMESTAMP_TZ( '2012-07-15 23:30:45.123456 +09:00',
                              'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ),
             'TZM' )
    ==> '00'

  • TO_CHAR( TO_TIMESTAMP_TZ( '2012-07-15 23:30:45.123456 +09:00',
                              'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM' ),
             'TZH:TZM' )
    ==> '+09:00'
* WW
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'WW' )
    ==> '29'

* W
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'W' )
    ==> '3'
* Y,YYY
  • TO_CHAR( TO_DATE( '2,012-07-15', 'Y,YYY-MM-DD' ), 'Y,YYY' )
    ==> '2,012'

* YYYY
  • TO_CHAR( TO_DATE( '2012-07-15', 'YYYY-MM-DD' ), 'YYYY' )
    ==> '2012'

* SYYYY
  • TO_CHAR( TO_DATE( '-0001-01-01', 'SYYYY-MM-DD' ), 'SYYYY' )
    ==> '-0001' 

* YYY
  • TO_CHAR( TO_DATE( '012-07-15', 'YYY-MM-DD' ), 'YYY' )
    ==> '012'
  • TO_CHAR( TO_DATE( '012-07-15', 'YYY-MM-DD' ), 'YYYY' )
    ==> '2012' ( (Current year / 1000) is 2 )

* YY
  • TO_CHAR( TO_DATE( '12-07-15', 'YY-MM-DD' ), 'YY' )
    ==> '12'
  • TO_CHAR( TO_DATE( '12-07-15', 'YY-MM-DD' ), 'YYYY' )
    ==> '2012' ( (Current year / 100) is 20 ) 
    ==> '2112' ( (Current year / 100) is 21 ) 

* Y
  • TO_CHAR( TO_DATE( '2-07-15', 'Y-MM-DD' ), 'Y' )
    ==> '2'
  • TO_CHAR( TO_DATE( '12-07-15', 'YY-MM-DD' ), 'YYYY' )
    ==> '2012' ( (Current year / 10 years) is 201 )
    ==> '2052' ( (Current year / 10 years) is 205 )

Expressions

Expression is a combination of value, operator and function for getting data values.
The following is the position of the SQL commands in which expression can be used.
• Target clause in SELECT
• GROUP BY clause in SELECT
• ORDER BY clause in SELECT
• WHERE clause and HAVING clause in SELECT
• INSERT VALUES clause
• UPDATE SET clause
• RETURN clause in INSERT, DELETE, UPDATE
Expression types are various as follows.
• Simple expression
• Compound expresssion
• Boolean value expression
• Case expression
• Datetime expression
• Scalar subquery expression
• Sequence manipulation expression
Simple expressions are column, pseudo columns, literals, and null value.
Compound expressions are combination of multiple expressions.
For more information, refer to the followings.
•  Null Value
•  Literals
•  Pseudo Columns
•  Operators
•  Functions

Boolean Value Expression

Syntax

<boolean value expression> ::=
        <boolean term>
      | <boolean value expression> OR <boolean term>

<boolean term> ::=
        <boolean factor>
      | <boolean term> AND <boolean factor>

<boolean factor> ::=
        [ NOT ] <boolean test>

<boolean test> ::=
        <boolean primary> [ IS [ NOT ] <truth value> ]

<truth value> ::=
        TRUE
      | FALSE
      | UNKNOWN

<boolean primary> ::=
        <column>
        <condition>
      | <boolean predicand>

<boolean predicand> ::=
        <parenthesized boolean value expression>
      | <nonparenthesized value expression primary>

<parenthesized boolean value expression> ::=
        <left paren> <boolean value expression> <right paren>

Description

<boolean value expression> describes a boolean value. <boolean primary> with boolean value are <column>, <condition>, and <boolean predicand>. <column> should be declared as BOOLEAN type, and it is allowed to return a boolean value using CAST.
<boolean value expression> can use logical operators such as AND, OR, NOT, and the dedicated operators of boolean value such as IS, IS NOT are also supported.
IS operator and IS NOT operator which are described in <boolean test> determine whether the boolean value described in <boolean primary> matches with one of the <truth value> (TRUE, FALSE, UNKNOWN).
For more information, refer to Conditions.

Example

gSQL> SELECT * FROM T1 WHERE CAST('TRUE' AS BOOLEAN);

I1   
-----
TRUE 
FALSE
null 

3 rows selected.

gSQL> SELECT * FROM T1 WHERE I1;

I1  
----
TRUE

1 row selected.

gSQL> SELECT * FROM T1 WHERE I1 IS TRUE;

I1  
----
TRUE

1 row selected.

gSQL> SELECT * FROM T1 WHERE I1 IS NOT FALSE;

I1  
----
TRUE
null

2 rows selected.

gSQL> SELECT * FROM T1 WHERE I1 IS UNKNOWN;

I1  
----
null

1 row selected.

CASE Expression

Syntax

<case expression> ::=
        <simple case>
      | <searched case>

<simple case> ::=
        CASE expr WHEN comparison_expr THEN result 
                 [ WHEN comparison_expr THEN result ... ] 
                 [ ELSE result ]
        END

<searched case> ::=
        CASE WHEN condition THEN result
             [ WHEN condition THEN result ... ] 
             [ ELSE result ]
        END

Description

WHEN ... THEN clause is evaluated in the order of which is described in the CASE statement.
If a comparison result is FALSE, the subsequent WHEN ... THEN clauses are evaluated until TRUE comes up.
If a comparison result is TRUE, the result is returned, and the evaluation is not executed any more.
• Simple case
  The comparison_expr of CASE expr and WHEN ... THEN clause is evaluated as the equal operation.  
  (expr = comparison_expr).
• Searched case
  The condition of WHEN ... THEN clause is evaluated.
If all evaluation results of the WHEN clause are FALSE, then result of ELSE clause is returned.
If ELSE clause is omitted, NULL is returned as a result.

If there are multiple types of THEN or ELSE clause results, the result type is determined by Result Type Combination Rule.

For more information, refer to the followings.
• COALESCE
• NULLIF

Example

gSQL> SELECT I1,
             CASE I1 WHEN 1 THEN 'ONE'
                     WHEN 2 THEN 'TWO'
                     ELSE 'NUMBER'
             END AS CASE_RESULT1,
             CASE I1 WHEN 1 THEN 'ONE'
                     WHEN 2 THEN 'TWO'
             END AS CASE_RESULT2
        FROM T1;
I1 CASE_RESULT1 CASE_RESULT2
-- ------------ -----------
 1 ONE          ONE        
 2 TWO          TWO        
 3 NUMBER       null       
3 rows selected.
gSQL> SELECT I1,
             CASE WHEN I1 = 1 THEN 'ONE'
                  WHEN I1 = 2 THEN 'TWO'
                  ELSE 'NUMBER'    
             END AS CASE_RESULT1,
             CASE WHEN I1 = 1 THEN 'ONE'
                  WHEN I1 = 2 THEN 'TWO'
             END AS CASE_RESULT2 
        FROM T1;
I1 CASE_RESULT1 CASE_RESULT2
-- ------------ ------------
 1 ONE          ONE         
 2 TWO          TWO         
 3 NUMBER       null        
3 rows selected.

CAST Specification

Syntax

CAST( expression AS data_type )

Description

CAST converts the expression data type to the data type of the specified data_type.

Example

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

Scalar Subquery Expression

Scalar subquery expression is a subquery which returns a single row with one column as a result. The scalar subquery expression result is the values described in select list of the subquery.
If the subquery does not return any row, then the result value is NULL, and if it returns two or more rows, then an error occurs.
Scalar subquery expression can be described on most position which describes expression. The subquery should be enclosed in parentheses. Even when scalar subquery expression is used as a function argument and the scalar subquery expression is enclosed in parentheses, other parentheses for the subquery is required regardless of the function parentheses. Otherwise, an error occurs
The following is an example of using scalar subquery expression.
gSQL> select * from dual where dummy = (select * from dual);

DUMMY
-----
X    

1 row selected.

gSQL> select sum(select 1 from dual) from dual;

ERR-42000(40000): syntax error 
select sum(select 1 from dual) from dual
...........^    ^
Error at line 1

gSQL> select sum((select 1 from dual)) from dual;

SUM((SELECT 1 FROM DUAL))
-------------------------
                        1

1 row selected.

Compatibility

The SQL standard compatibility for expression is as follows.

SQL standard compatibility for expression

Feature ID

Description

Availability

E121-03

Value expressions in ORDER BY clause

O

F051-05

Basic date and time Explicit CAST between datetime types and character string types

O

F201

CAST function

O

F261-01

Simple CASE

O

F261-02

Searched CASE

O

F261-03

NULLIF

O

F261-04

COALESCE

O

F263

Comma-separated predicates in simple CASE expression

X

F301

CORRESPONDING in query expressions

X

F385

Drop column generation expression clause

X

F561

Full value expressions

X

F846

Octet support in regular expression operators

X

F847

Nonconstant regular expressions

X

F850

Top-level <order by clause> in <query expression>

O

F855

Nested <order by clause> in <query expression>

O

F856

Nested <fetch first clause> in <query expression>

O

F857

Top-level <fetch first clause> in <query expression>

O

F861

Top-level <result offset clause> in <query expression>

O

F863

Nested <result offset clause> in <query expression>

O

S091-03

Arrays expressions

X

S111

ONLY in query expressions

X

T121

WITH (excluding RECURSIVE) in query expression

X

T581

Regular expression substring function

X

Pseudo Columns

Pseudo column is not only similar to function, but also it is similar to table column because it can return different value in row unit every time the pseudo column is executed.
Supported pseudo column

Name

Description

Remarks

CURRVAL

It is a pseudo column which is related to a sequence.

CURRVAL

NEXTVAL

It is a pseudo column which is related to a sequence.

NEXTVAL

ROWNUM

It is the row number which satisfies the condition.

ROWNUM

ROWID

It returns the record identifier in database.

ROWID Pseudo Column

CLUSTER_GROUP_ID

It returns the group identifier in database.

CLUSTER_GROUP_ID Pseudo Column

CLUSTER_MEMBER_ID

It returns the member identifier in which the record is stored.

CLUSTER_MEMBER_ID Pseudo Column

CLUSTER_GROUP_NAME

It returns the group name in which the record is stored.

CLUSTER_GROUP_NAME Pseudo Column

CLUSTER_MEMBER_NAME

It returns the member name in which the record is stored.

CLUSTER_MEMBER_NAME Pseudo Column

ROWID Pseudo Column

ROWID pseudo column is a record identifier, and it returns the identification information of each database record.

ROWID has the following information to identify the location within the database depending on the system.

Standalone system
• OBJECT_ID
• TABLESPACE_ID
• PAGE_ID
• OFFSET in PAGE
Cluster system
• GRID_BLOCK_SEQUENCE
• GRID_BLOCK_ID
• MEMBER_ID
• SHARD_ID
The information stored inside in base 64 encoding is converted into the value such as A-Z, a-z, 0-9, +, / then output when querying ROWID.
Each information to identify the address within database stored in ROWID can be obtained using the ROWID-related functions.
The address of the deleted record can be newly reassigned to the record to be inserted.
ROWID pseudo column can be used only in SELECT operation, but it can not be used in INSERT, UPDATE, DELETE operations.
For more information, refer to ROWID, ROWID-related Functions.
The following is an example of querying ROWID pseudo column.
gSQL> SELECT ROWID FROM T1;
                  ROWID
-----------------------
AAAAAAAAFpEAACAAAEAkAAA
AAAAAAAAFpEAACAAAEAkAAB
AAAAAAAAFpEAACAAAEAkAAC
AAAAAAAAFpEAACAAAEAkAAD
AAAAAAAAFpEAACAAAEAkAAE
5 rows selected.

CLUSTER_GROUP_ID Pseudo Column

CLUSTER_GROUP_ID pseudo column returns the group identifier of a server in which the record is stored.

CLUSTER_GROUP_ID pseudo column can perform the SELECT, but it can not perform the INSERT, UPDATE, or DELETE.

This information in valid in the cluster system.

The following is an example of retrieving CLUSTER_GROUP_ID pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_GROUP_ID FROM T1;
C1 T1.CLUSTER_GROUP_ID
-- -------------------
A                    1
B                    2
C                    3

3 rows selected.

CLUSTER_MEMBER_ID Pseudo Column

CLUSTER_MEMBER_ID pseudo column returns the member identifier of a server in which the record is stored.

CLUSTER_MEMBER_ID pseudo column can perform the SELECT, but it can not perform the INSERT, UPDATE, or DELETE.

This information in valid in the cluster system.

The following is an example of retrieving CLUSTER_MEMBER_ID pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_MEMBER_ID FROM T1;
C1 T1.CLUSTER_MEMBER_ID
-- --------------------
A                     1
B                     3
C                     5

3 rows selected.

CLUSTER_GROUP_NAME Pseudo Column

CLUSTER_GROUP_NAME pseudo column returns the group name of a server in which the record is stored.

CLUSTER_GROUP_NAME pseudo column can perform the SELECT, but it can not perform the INSERT, UPDATE, or DELETE.

This information in valid in the cluster system.

The following is an example of retrieving CLUSTER_GROUP_NAME pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_GROUP_NAME FROM T1;
C1 T1.CLUSTER_GROUP_NAME
-- ---------------------
A  G1                   
B  G2                   
C  G3                   

3 rows selected.

CLUSTER_MEMBER_NAME Pseudo Column

CLUSTER_MEMBER_NAME pseudo column returns the member name of a server in which the record is stored.

CLUSTER_MEMBER_NAME pseudo column can perform the SELECT, but it can not perform the INSERT, UPDATE, or DELETE.

This information in valid in the cluster system.

The following is an example of retrieving CLUSTER_MEMBER_NAME pseudo column.

gSQL> SELECT T1.C1, T1.CLUSTER_MEMBER_NAME FROM T1;
C1 T1.CLUSTER_MEMBER_NAME
-- ----------------------
A  G1N1                  
B  G2N1                  
C  G3N1                  

3 rows selected.

Compatibility

The SQL standard compatibility for pseudo column is as follows.
SQL standard compatibility for pseudo column

Feature ID

Description

Availability

T176

Sequence generator support

O

T177

Sequence generator support: simple restart option

O

Operators

An operator is represented by one or more specific symbols or keywords, and it performs an operation using one or more arguments.
The operator types are various as follows.
• Arithmetic operator
• Concatenation operator
• Set operator

Arithmetic Operator

Syntax

<arithmetic operator> ::=
        <value term>
      | <expression> + <value term>
      | <expression> - <value term>

<value term> ::=
        <value factor>
      | <value term> * <value factor>
      | <value term> / <value factor>

<value factor> ::=
        <expression>
      | + <expression>
      | - <expression>

Description

An arithmetic operator performs an arithmetic operation of the numeric types, date/time types or interval types.

The arithmetic operator precedence is as follows.

  1. + (POSITIVE), - (NEGATIVE)

  2. * (MULTIPLICATION), / (DIVISION)

  3. + (ADDITION), - (SUBTRACTION)

Concatenation Operator

Syntax

<concatenation operator> ::=
        <expression> || <expression>

Description

A concatenation operator returns strings which connect between values of CHARACTER STRING type or BINARY STRING type.
For more information, refer to || (CONCATENATE), CONCATENATE.

Set Operator

Syntax

<set operator> ::=
        <set operator term>
      | <subquery> UNION [ ALL | DISTINCT ] <set operator term>
      | <subquery> EXCEPT [ ALL | DISTINCT ] <set operator term>
      | <subquery> MINUS [ ALL | DISTINCT ] <set operator term>

<set operator term> ::=
        <subquery>
      | <subquery> INTERSECT [ ALL | DISTINCT ] <set operator term>

Description

A set operator performs a set operation of the subquery results.
For more information, refer to set operator.

INTERSECT ALL/DISTINCT has a higher precedence than other set operators.
Set operators

Operator

Description

UNION ALL

It is the union which does not exclude duplicated rows of the subquery result.

UNION DISTINCT

It is the union which excludes duplicated rows of the subquery result.

EXCEPT ALL

It is the difference set which does not exclude duplicated rows of the subquery result.

EXCEPT DISTINCT

It is the difference set which excludes duplicated rows of the subquery result.

MINUS ALL

It is as same as EXCEPT ALL.

MINUS DISTINCT

It is as same as EXCEPT DISTINCT.

INTERSECT ALL

It is the intersection which does not exclude duplicated rows of the subquery result.

INTERSECT DISTINCT

It is the intersection which excludes duplicated rows of the subquery result.

Compatibility

The SQL standard compatibility for operator is as follows.

SQL standard compatibility for operator

Feature ID

Description

Availability

E011-04

Arithmetic operators

O

E021-07

Character concatenation

O

E071-01

UNION DISTINCT table operator

O

E071-02

UNION ALL table operator

O

E071-03

EXCEPT DISTINCT table operator

O

E071-05

Columns combined via table operators need not have exactly the same data type

O

E071-06

Table operators in subqueries

O

F041-08

All comparison operators are supported (rather than just =)

O

F302-01

INTERSECT DISTINCT table operator

O

F302-02

INTERSECT ALL table operator

O

F304

EXCEPT ALL table operator

O

F846

Octet support in regular expression operators

X

J571

NEW operator

X

Functions

Functions and operators are similar in features. However, to represent arguments, functions use parentheses after its name. A function can have zero or more arguments.
The function has two types as follows. 
• Single row function 
• Aggregate function

Single Row Function

Single row function creates a single result row for each row in the table or view.

The single row functions are as follows.

Numeric Functions

A numeric value is input in numeric function, and the numeric function returns a numeric result.

For more information about the numeric function types, refer to the followings.

Character String Functions Returning Character Values

A character string type value is input in character string functions returning character values, and the function returns the result of character string type.

For more information about character string functions returning character values types, refer to the followings.

Character String Functions Returning Number Values

A character string type value is input in character string functions returning number values the value, and the function returns the result of number type.

For more information about character string functions returning number values types, refer to the followings.

Datetime Functions

The value of date/time/timestamp/interval type is input in datetime function, and the function returns the result of date/time/timestamp/interval type.

For more information about datetime functions types, refer to the followings.

General Comparison Functions

General comparison function returns a minimum value or a maximum value for the value set.

For more information about general comparison function types, refer to the followings.

Conversion Functions

Conversion function sets the value of a particular data type.

For more information about conversion function types, refer to the followings.

Conditional Functions

Conditional function returns a result of specific value depending on a condition.

For more information about conditional function types, refer to the followings.

NULL-related Functions

NULL-related function returns a result of specific value depending on whether the input value is a NULL value.

For more information about null-related function types, refer to the followings.

ROWID-related Functions

ROWID-related function is used to obtain information about the ROWID.

For more information about ROWID-related function types, refer to the followings.

Encryption Functions

encryption function encrypts, decrypts, or hashes the given plain text by using the specific algorithm, then returns the result.

For more information about the encryption function, refer to DIGEST.

System Information Functions

System information function is used to obtain information about sessions and the system.

For more information about system information function type, refer to the followings.

Aggregate Function

Aggregate function creates a single result row for multiple rows.

For more information about aggregate function types, refer to the followings.

Compatibility

The SQL standard compatibility for function is as follows.

SQL standard compatibility for function

Feature ID

Description

Availability

B033

Untyped SQL-invoked function arguments

X

E021-04

CHARACTER_LENGTH function

O

E021-05

OCTET_LENGTH function

O

E021-06

SUBSTRING function

O

E021-08

UPPER and LOWER functions

O

E021-09

TRIM function

O

E021-11

POSITION function

O

E091-01

AVG

O

E091-02

COUNT

O

E091-03

MAX

O

E091-04

MIN

O

E091-05

SUM

O

E091-06

ALL quantifier

O

E091-07

DISTINCT quantifier

O

F131-03

Set functions supported in queries with grouped views

O

F201

CAST function

O

F441

Extended set function support

X

F442

Mixed column references in set functions

X

F801

Full set function

X

F842

OCCURRENCES_REGEX function

X

F843

POSITION_REGEX function

X

S071

SQL paths in function and type name resolution

X

S201-02

Array as result type of functions

X

S211

User-defined cast functions

X

S241

Transform functions

X

T041-03

POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types

X

T312

OVERLAY function

O

T321-01

User-defined functions with no overloading

X

T326

Table functions

X

T341

Overloading of SQL-invoked functions and SQL-invoked procedures

X

T433

Multiargument GROUPING function

X

T441

ABS and MOD functions

O

T571

Array-returning external SQL-invoked functions

X

T572

Multiset-returning external SQL-invoked functions

X

T581

Regular expression substring function

X

T614

NTILE function

X

T615

LEAD and LAG functions

X

T616

Null treatment option for LEAD and LAG functions

X

T617

FIRST_VALUE and LAST_VALUE functions

X

T618

NTH_VALUE function

X

T619

Nested window functions

X

T621

Enhanced numeric functions

O

Conditions

Condition

Condition is an expression which is evaluated as TRUE, FALSE, UNKNOWN.
Condition can be used in the following SQL statements.

• WHERE clauses in DELETE, UPDATE statements
• WHERE and HAVING clauses in SELECT statement
• Where the BOOLEAN TYPE can be used
The condition types are as follows.

• Comparison condition
• Logical condition
• Null condition
• Compound condition
• Pattern-matching condition
• Between condition
• In condition
• Exists condition
Condition precedence

Precedence

Condition type

1

Operators in condition clauses

2

=, !=, <, >, <=, >=

3

IS [NOT] NULL,

[NOT] BETWEEN,

[NOT] IN,

LIKE, EXISTS

4

NOT

5

AND

6

OR

Comparison Conditions

It compares both conditional expressions, and returns the boolean type of TRUE, FALSE, UNKNOWN values.
Comparison conditions

Condition

Description

=

It checks if both conditions are equal.

!=, <>

It checks if both conditions are not equal.

>

It compares which one of both conditions is bigger.

<

It compares which one of both conditions is smaller.

>=

It compares which one of both conditions is bigger or equal.

<=

It compares which one of both conditions is smaller or equal.

ANY, SOME

If there is a condition whose left expr satisfies at least one of right expr_list (or subquery results), then it returns TRUE.

If there is not right subquery result, then it returns FALSE.

ALL

If there is a condition whose left expr satisfies all right expr_list (or subquery results), then it returns TRUE.

If there is not right subquery result, then it returns TRUE.

For more information, refer to Type Comparison.

< Simple Comparison Conditions >

Syntax

<simple_comparison_condition> ::=
        <expr>          <comparison_operator> <expr>
      | <expr>          <comparison_operator> ( <subquery> )
      | ( <subquery> )  <comparison_operator> <expr>
      | ( <subquery> )  <comparison_operator> ( <subquery> ) 
      | ( <expr_list> ) <comparison_operator> ( <expr_list> )
      | ( <expr_list> ) <comparison_operator> ( <subquery> )
      | ( <subquery> )  <comparison_operator> ( <expr_list> )
      | ( <subquery> )  <comparison_operator> ( <subquery> )

<comparison_operator> ::=
        <  =   >
      | <  !=  >
      | <  <   >
      | <  >   >
      | <  <=  >
      | <  >=  >

<expr_list> ::= 
        <expr>
      | <expr>, ... , <expr>
      | ( <expr> )
      | ( <expr> , ... , <expr> )
For more information, refer to Scalar Subquery Expression.

Description

If the expr list or subquery comes to both left and right of comparison_operator, then the number of expr or subquery target to be compared should be same.
If there is a subquery, the number of result records should be one.

Example

Example of simple comparison conditions

Conditional expression

Result

'abc' = 'abc'

TRUE

'abc' != 'abc'

FALSE

'abc' < 'abc'

FALSE

'abc' <= 'abc'

TRUE

'abc' > 'abc'

FALSE

'abc' >= 'abc'

TRUE

( 1, 2, 3 ) = ( 1, 2, 3 )

TRUE

( 1, 2, 3 ) = ( 1, 2, 4 )

FALSE

( 1, 2, 3 ) != ( 4, 5, 6 )

TRUE

( 1, 2, 3 ) != ( 1, 2, 3 )

FALSE

( 1, 2, 3 ) < ( 1, 2, 4 )

TRUE

( 1, 2, 3 ) < ( 1, 2, 3 )

FALSE

( 1, 2, 3 ) <= ( 1, 2, 4 )

TRUE

( 1, 2, 3 ) <= ( 1, 2, 2 )

FALSE

( 1, 2, 3 ) > ( 1, 2, 2 )

TRUE

( 1, 2, 3 ) > ( 1, 2, 4 )

FALSE

( 1, 2, 3 ) >= ( 1, 2, 2 )

TRUE

( 1, 2, 3 ) >= ( 1, 2, 4 )

FALSE

<Group Comparison Conditions>

Syntax

<group_comparison_condition> ::=
   <expr>          <comparison_operator> <quantifier> ( <expr_list> )
 | <expr>          <comparison_operator> <quantifier> ( <subquery> )
 | ( <expr_list> ) <comparison_operator> <quantifier> ( <expr_list_list> )
 | ( <expr_list> ) <comparison_operator> <quantifier> ( <subquery> )
 | ( <subquery> )  <comparison_operator> <quantifier> ( <expr_list> )
 | ( <subquery> )  <comparison_operator> <quantifier> ( <expr_list_list> )
 | ( <subquery> )  <comparison_operator> <quantifier> ( <subquery> )

<comparison_operator> ::=
        <  =   >
      | <  !=  >
      | <  <   >
      | <  >   >
      | <  <=  >
      | <  >=  >

<quantifier> ::=
        ALL
      | ANY
      | SOME

<expr_list> ::= 
        <expr>
      | <expr>, ... , <expr>
      | ( <expr> )
      | ( <expr> , ... , <expr> )

<expr_list_list> ::=
        <expr_list>
      | <expr_list>, ... , <expr_list>
For more information, refer to Scalar Subquery Expression.

Description

If the expr list or subquery comes to both left and right of comparison_operator, then the number of expr or subquery target to be compared should be same.
If a subquery comes to the left of comparison_operator, the number of result records should be one.
If a subquery comes to the right of comparison_operator, the number of result records can be multiple.

Example

Example of group comparison conditions

Conditional expression

Result

1 =any ( 1, 2, 3, 4, 5 )

TRUE

1 =any ( 1, 2, null, 4, 5 )

TRUE

1 =any ( 2, null, 4, 5 )

NULL

1 =any ( 100, 2, 3, 4, 5 )

FALSE

1 =all ( 1, +1, 1E+0 )

TRUE

1 =all ( 1, +1, 1E+0, null )

NULL

1 =all ( 1, 2, 3, 4, 5 )

FALSE

( 1, 2 ) =any ( ( 0, 1 ), ( 1, 2 ), ( 3, 4 ) )

TRUE

( 1, 2 ) =any ( ( 0, 1 ), ( 1, 2 ), ( null, null ) )

TRUE

( 1, 2 ) =any ( ( 0, 1 ), ( 2, 3 ), ( 3, 4 ) )

FALSE

( 1, 2 ) =all ( ( 1, 2 ), ( +1, +2 ), ( 1E+0, 2E+0 ) )

TRUE

( 1, 2 ) =all ( ( 1, 2 ), ( +1, +2 ), ( null, null ) )

NULL

( 1, 2 ) =all ( ( 0, 1 ), ( 2, 3 ), ( 3, 4 ) )

FALSE

When the result record of comparison_operator's right subquery is 0

( 'X' ) =any ( select dummy from dual where dummy = 'Y' )

FALSE

( 'X' ) =all ( select dummy from dual where dummy = 'Y' )

TRUE

Logical Conditions

Logical conditions are such as AND, OR, NOT.

AND

Syntax

<boolean value expression> AND <boolean value expression>

Description

Truth table of AND boolean operator

AND

True

False

Unknown

True

True

False

Unknown

False

False

False

False

Unknown

Unknown

False

Unknown

OR

Syntax

<boolean value expression> OR <boolean value expression>

Description

Truth table of OR boolean operator

OR

True

False

Unknown

True

True

True

True

False

True

False

Unknown

Unknown

True

Unknown

Unknown

NOT

Syntax

NOT <boolean value expression>

Description

Truth table of NOT boolean operator

expr

NOT

True

False

False

True

Unknown

Unknown

Null Condition

Syntax

<expr> IS [NOT] NULL

Description

It checks whether the result value of expr is NULL.
Result table of IS NULL condition

expr

IS NULL

IS NOT NULL

NULL

True

False

NOT NULL

False

True

Compound Conditions

It is a conditional expression in which multiple conditions are combined.

compound_condition ::=
        ( condition )
      | NOT condition
      | condition < AND | OR > condition

Pattern-matching Conditions

Like Condition

Syntax

like_condition ::=
        string [NOT] LIKE pattern [ ESCAPE escape_character ]

Description

It checks if a string matches the specified pattern.
Arguments such as string, pattern, escape_character can be of a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or of the type which is available to be converted to a character type.
If string, pattern, escape_character are NULL, it returns NULL.
If escape_character is omitted, there is not a default value.
If escape_character is specified, the escape_character should be one character.
If pattern does not include '_'  nor '%', it is processed in the same way as equal operation(string = pattern).
If pattern includes '_'  or '%', the string checks if it matches as follows.
• '_': If it corresponds to one arbitrary character.
• '%': If it corresponds to the arbitrary character string which has zero or more characters.
Use ESCAPE syntax to compare '_' or '%' included in the pattern with characters. 
Specify escape_character, and describe the specified escape_character before the pattern's  '_' or '%'.

Example

gSQL> SELECT 'hello%' LIKE 'h%o!%' ESCAPE '!' AS RESULT FROM DUAL;
RESULT
------
TRUE

• 'represent' LIKE 'represent'   => TRUE
• 'represent' LIKE ' represent ' => FALSE
• 'represent' LIKE 'REPRESENT'   => FALSE
• 'represent' LIKE 'r_pr_s_nt'   => TRUE
• 'represent' LIKE 're%t'        => TRUE
• 'represent' LIKE 'rep'         => FALSE

• 'summer_vacation' LIKE 'summer\_vacation' ESCAPE '\'  => TRUE
• NULL LIKE 'summer\_vacation' ESCAPE '\'               => NULL
• 'summer_vacation' LIKE NULL ESCAPE '\'                => NULL
• 'summer_vacation' LIKE 'summer\_vacation' ESCAPE NULL => NULL

BETWEEN Condition

Syntax

<between condition> ::=
   <expr1> [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] <expr2> AND <expr3>

Description

It checks whether expr1 is within the range between expr2 and expr3.
If ASYMMETRIC or SYMMETRIC is omitted, the default is ASYMMETRIC.
If data types among expr1, expr2, expr3 are different, they are converted. 
For more information, refer to Type Comparison, Type Conversion.
Equivalence of between conditions

A

B

X BETWEEN ASYMMETRIC Y AND Z

X BETWEEN Y AND Z

X BETWEEN Y AND Z

X >= Y AND X <= Z

X NOT BETWEEN Y AND Z

NOT( X BETWEEN Y AND Z )

X BETWEEN SYMMETRIC Y AND Z

((X BETWEEN Y AND Z) OR (X BETWEEN Z AND Y)

X NOT BETWEEN SYMMETRIC Y AND Z

NOT( X BETWEEN SYMMETRIC Y AND Z )

Example

Example of between condition

Conditional expression

Result

BETWEEN [ ASYMMETRIC ]

3 BETWEEN 1 AND 5

TRUE

NULL BETWEEN 1 AND 5

3 BETWEEN NULL AND 5

3 BETWEEN 1 AND NULL

NULL

3 BETWEEN 5 AND 1

FALSE

BETWEEN SYMMETRIC

3 BETWEEN SYMMETRIC 1 AND 5

TRUE

NULL BETWEEN SYMMETRIC 1 AND 5

3 BETWEEN SYMMETRIC NULL AND 5

3 BETWEEN SYMMETRIC 1 AND NULL

NULL

3 BETWEEN SYMMETRIC 5 AND 1

TRUE

IN Condition

Syntax

<in_condition> ::=
        <expr>          [NOT] IN ( <expr_list> )  
      | <expr>          [NOT] IN ( <subquery> )  
      | ( <expr_list> ) [NOT] IN ( <expr_list_list> )  
      | ( <expr_list> ) [NOT] IN ( <subquery> )
      | ( <subquery> )  [NOT] IN ( <expr_list> )  
      | ( <subquery> )  [NOT] IN ( <expr_list_list> )  
      | ( <subquery> )  [NOT] IN ( <subquery> )  

<expr_list> ::=          
        <expr>       
      | <expr>, ... , <expr>       
      | ( <expr> )       
      | ( <expr> , ... , <expr> )  

<expr_list_list> ::=         
        <expr_list>       
      | <expr_list>, ... , <expr_list>

Description

In condition returns the same result as = ANY.
NOT IN condition returns the same result as !=ALL.
For more information, refer to Comparison Conditions.

Example

Example of IN condition

Conditional expression

Result

1 IN ( 1, 2, 3, 4, 5 )

TRUE

1 IN ( 1, 2, null, 4, 5 )

TRUE

1 IN ( 2, null, 4, 5 )

NULL

1 IN ( 100, 2, 3, 4, 5 )

FALSE

NULL IN ( 1, 2, 3 )

NULL

1 NOT IN ( 2, 3, 4, 5 )

TRUE

1 NOT IN ( 2, null, 4, 5 )

NULL

1 NOT IN ( 1, 2, null, 4, 5 )

FALSE

1 NOT IN ( 100, 2, 3, 4, 5 )

TRUE

NULL NOT IN ( 1, 2, 3 )

NULL

EXISTS Condition

Syntax

exists_conditions ::= 
        EXISTS ( subquery )

Description

It checks whether the result record of subquery exists.
If the result record of subquery exists, it returns TRUE. Otherwise, it returns FALSE.

Example

gSQL> SELECT * FROM DUAL WHERE EXISTS ( SELECT * FROM DUAL );
      DUMMY
      -----
      X    
      1 row selected.

gSQL> SELECT * FROM DUAL 
       WHERE EXISTS ( SELECT * FROM DUAL WHERE DUMMY = 'Y' );
      no rows selected.

Compatibility

The SQL standard compatibility for condition is as follows.

SQL standard compatibility for condition

Feature ID

Description

Availability

E061-01

Comparison predicate

O

E061-02

BETWEEN predicate

O

E061-03

IN predicate with list of values

O

E061-04

LIKE predicate

O

E061-05

LIKE predicate: ESCAPE clause

O

E061-06

NULL predicate

O

E061-07

Quantified comparison predicate

O

E061-08

EXISTS predicate

O

E061-09

Subqueries in comparison predicate

O

E061-11

Subqueries in IN predicate

O

E061-12

Subqueries in quantified comparison predicate

O

E061-13

Correlated subqueries

O

E061-14

Search condition

O

F051-04

Comparison predicate on DATE, TIME, and TIMESTAMP data types

X

F053

OVERLAPS predicate

X

F263

Comma-separated predicates in simple CASE expression

X

F291

UNIQUE predicate

X

F481

Expanded NULL predicate

O

F841

LIKE_REGEX predicate

X

P008

Comma-separated predicates in a CASE statement Extended CASE

X

S151

Type predicate

X

T141

SIMILAR predicate

X

T151

DISTINCT predicate

X

T152

DISTINCT predicate with negation

X

T461

Symmetric BETWEEN predicate

O

T501

Enhanced EXISTS predicate

X

T631

IN predicate with one list element

X

X090

XML document predicate

X

X091

XML content predicate

X

X141

IS VALID predicate: data-driven case

X

X142

IS VALID predicate: ACCORDING TO clause

X

X143

IS VALID predicate: ELEMENT clause

X

X144

IS VALID predicate: schema location

X

X145

IS VALID predicate outside check constraints

X

X151

IS VALID predicate with DOCUMENT option

X

X152

IS VALID predicate with CONTENT option

X

X153

IS VALID predicate with SEQUENCE option

X

X155

IS VALID predicate: NAMESPACE without ELEMENT clause

X

X157

IS VALID predicate: NO NAMESPACE with ELEMENT clause

X

Built-in Data Type References

Aliases of Built-in Data Types

BINARY

Syntax

BINARY [ (length) ]

Syntax Rules and Parameters

Description

A fixed-length binary string is stored.
If the binary string length to be stored is shorter than the specified length,  X'00 ' is stored in the remaining part.
• Storage size: Bytes of the length value

For More Information

Refer to the followings.

BINARY VARYING

Syntax

BINARY VARYING (length)

Syntax Rules and Parameters

Description

The variable-length binary string is stored.

• Storage size: Bytes of the binary string to be stored
• Alias names: VARBINARY

For More Information

Refer to the followings.

BINARY LONG VARYING

Syntax

BINARY LONG VARYING

Description

The value of the long variable binary string is stored.
• Maximum storage size: 100 megabytes
• Storage size: Bytes of the binary string to be stored
• Alias names: LONG BINARY VARYING, LONG VARBINARY
It can not be used as a column of the key, so there are limitations as follows.
• It can not be used as a key column of an index.
• It can not be used as the expression of ORDER BY clause. 
• It can not be used as the expression of GROUP BY clause.
• It can not be used as the expression of DISTINCT clause.
• It can not be used as the expression of UNION, INTERSECT, EXCEPT clauses.

For More Information

Refer to the followings.

BOOLEAN

Syntax

BOOLEAN

Description

TRUE or FALSE is stored.
• Storage size: 1 byte.

CHARACTER

Syntax

CHARACTER [ (length [ CHARACTERS | OCTETS | CHAR | BYTE ] ) ]

Syntax Rules and Parameters

Description

A fixed-length string is stored.
If the length of the string to be stored is shorter than the specified length, white spaces are stored in the remaining part.

For More Information

Refer to the followings.

CHARACTER VARYING

Syntax

CHARACTER VARYING ( length [ CHARACTERS | OCTETS | CHAR | BYTE ] )

Syntax Rules and Parameters

Description

The variable-length string is stored.

• Storage size: Bytes of the string to be stored
• Alias names: VARCHAR, VARCHAR2

For More Information

Refer to the followings.

CHARACTER LONG VARYING

Syntax

CHARACTER LONG VARYING

Description

The value of the long variable-length string is stored.

• Maximum storage size: 100 megabytes
• Storage size: Bytes of the string to be stored 
• Alias names: LONG CHARACTER VARYING, LONG CHAR VARYING, LONG VARCHAR
It can not be used as a column of the key, so there are limitations as follows.

• It can not be used as a key column of an index.
• It can not be used as the expression of ORDER BY clause. 
• It can not be used as the expression of GROUP BY clause.
• It can not be used as the expression of DISTINCT clause.
• It can not be used as the expression of UNION, INTERSECT, EXCEPT clauses.

For More Information

Refer to the followings.

DATE

Syntax

DATE

Description

It is the date type including YEAR, MONTH, DAY, HOUR, MINUTE and SECOND (excluding fractional seconds).

For More Information

Refer to the followings.

FLOAT

Syntax

FLOAT[ ( precision ) ]

Syntax Rules and Parameters

Description

The floating point value with a binary precision is stored.
It has a binary precision value unlike NUMBER, NUMERIC types.

For More Information

Refer to the followings.

INTERVAL

Syntax

<interval_type> ::=
      INTERVAL YEAR [ ( leading_precision ) ] 
    | INTERVAL MONTH [ ( leading_precision ) ] 
    | INTERVAL DAY [ ( leading_precision ) ] 
    | INTERVAL HOUR [ ( leading_precision ) ] 
    | INTERVAL MINUTE [ ( leading_precision ) ]
    | INTERVAL SECOND [ ( leading_precision  [ , fractional_seconds_precision ] ) ]
    | INTERVAL YEAR [ ( leading_precision ) ] TO MONTH
    | INTERVAL DAY [ ( leading_precision ) ] TO HOUR
    | INTERVAL DAY [ ( leading_precision ) ] TO MINUTE
    | INTERVAL DAY [ ( leading_precision ) ] TO SECOND [ ( fractional_seconds_precision ) ]
    | INTERVAL HOUR [ ( leading_precision ) ] TO MINUTE
    | INTERVAL HOUR [ ( leading_precision ) ] TO SECOND [ ( fractional_seconds_precision ) ]
    | INTERVAL MINUTE [ ( leading_precision ) ] TO SECOND [ ( fractional_seconds_precision ) ]

Syntax Rules and Parameters

Description

INTERVAL types are classified into YEAR TO MONTH family type and DAY TO SECOND family type depending on the range of value representation as follows.
If the number which is bigger than number of the specified digits is in the field to which the leading_precision is specified, then an error is returned.
If the number which is bigger than number of the specified digits is in the field to which the fractional_seconds_precision is specified, it is rounded off.
Precisions and value range of the second or later field in INTERVAL * TO *

Field

Precision

Value range

MONTH

2

0 ~ 11

HOUR

2

0 ~ 23

MINUTE

2

0 ~ 59

SECOND (interger part)

2

0 ~ 59

For More Information

Refer to Interval Literals.

NATIVE_BIGINT

Syntax

NATIVE_BIGINT

Description

Signed 8-byte integer is stored.
It is as same as long long data type of C language (8 bytes integer).

NATIVE_DOUBLE

Syntax

NATIVE_DOUBLE

Description

Double precision floating-point number (8 bytes) is stored.
It is as same as double data type of C language.

NATIVE_INTEGER

Syntax

NATIVE_INTEGER

Description

Signed 4 byte integer is stored.
It is as same as integer data type of C language (4 bytes).

NATIVE_REAL

Syntax

NATIVE_REAL

Description

Single precision floating-point number (4 bytes) is stored.
It is as same as float data type of C language.

NATIVE_SMALLINT

Syntax

NATIVE_SMALLINT

Description

Signed 2 byte integer is stored.
It is as same as short data type of C language.

NUMBER

Syntax

NUMBER  [ ( precision [ , scale ] ) ]

Syntax Rules and Parameters

Description

NUMBER type is similar to NUMERIC type, but if both the precision and scale are omitted, NUMBER type stores the floating point number whose precision and scale are not specified.

For More Information

Refer to the followings.

NUMERIC

Syntax

NUMERIC  [ ( precision [ , scale ] ) ]

Syntax Rules and Parameters

Description

A fixed point number with precision and scale is stored.
If the precision and scale are omitted, it means as follows.
NUMBER type is similar to NUMERIC type, but if both the precision and scale are omitted, NUMBER type stores the floating-point number whose precision and scale is not specified.

For More Information

Refer to the followings.

ROWID

Syntax

ROWID

Description

A record identifier (ROWID) is stored.

A record identifier (ROWID) is the identification information of each record in database.

When querying the ROWID pseudo column, each record identifier (ROWID) is obtained. This ROWID pseudo column has the ROWID data type information.
ROWID type consists of the followings in a standalone system.
• OBJECT_ID 
• TABLESPACE_ID 
• PAGE_ID 
• OFFSET within PAGE
ROWID type consists of the followings in a cluster system.
• GRID_BLOCK_SEQUENCE
• GRID_BLOCK_ID
• MEMBER_ID
• SHARD_ID
ROWID is stored in the base 64 value, which can include A ~ Z, a ~ z, 0 ~ 9, +, /.
Each component information of ROWID is obtained using ROWID-related functions.
• Storage size: 16 bytes

For More Information

Refer to the followings.

TIME

Syntax

TIME [ ( fractional_seconds_precision ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]

Syntax Rules and Parameters

Description

The time which includes HOUR, MINUTE and SECOND is stored.

For More Information

Refer to the followings.

TIMESTAMP

Syntax

TIMESTAMP [ ( fractional_seconds_precision ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]

Syntax Rules and Parameters

Description

The time which includes YEAR, MONTH, DATE, HOUR, MINUTE and SECOND is stored.

For More Information

Refer to the followings.

Built-in Function References

* (MULTIPLICATION)

Syntax

expr1 * expr2

Description

It returns the multiplication result of expr1 and expr2.

The multiplication types and result types are as follows.
For more information, refer to Type Conversion.
Numeric * operation

expr1 (expr2)

expr2 (expr1)

Result type

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_BIGINT

NUMBER

NUMBER

NUMBER

NATIVE DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

INTERVAL * operation

expr1 (expr2)

expr2 (expr1)

Result type

INTERVAL YEAR TO MONTH

Numeric type

INTERVAL YEAR TO MONTH

(The result type is the interval type.)

INTERVAL DAY TO SECOND

Numeric type

INTERVAL DAY TO SECOND

(The result type is the interval type.)

Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

INTERVAL type details which is included in INTERVAL type written in the following table

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

  • INTERVAL YEAR

  • INTERVAL MONTH

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY

  • INTERVAL HOUR

  • INTERVAL MINUTE

  • INTERVAL SECOND

  • INTERVAL DAY TO HOUR

  • INTERVAL DAY TO MINUTE

  • INTERVAL DAY TO SECOND

  • INTERVAL HOUR TO MINUTE

  • INTERVAL HOUR TO SECOND

  • INTERVAL MINUTE TO SECOND

Example

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

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

+ (ADDITION)

Syntax

expr1 + expr2

Description

It returns the addition result of expr1 and expr2.

The addition types and result types are as follows.
For more information, refer to Type Conversion.
Numeric + operation

expr1 (expr2)

expr2 (expr1)

Result type

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_BIGINT

NUMBER

NUMBER

NUMBER

NATIVE DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

(DATETIME/INTERVAL) + operation

expr1 (expr2)

expr2 (expr1)

Result type

DATE

NUMERIC

DATE

DATE

INTERVAL YEAR TO MONTH

DATE

DATE

INTERVAL DAY

DATE

DATE

INTERVAL DAY TO SECOND

TIMESTAMP

TIME

NUMERIC

TIME

TIME

INTERVAL YEAR TO MONTH

TIME

TIME

INTERVAL DAY TO SECOND

TIME

TIME WITH TIME ZONE

NUMERIC

TIME WITH TIME ZONE

TIME WITH TIME ZONE

INTERVAL YEAR TO MONTH

TIME WITH TIME ZONE

TIME WITH TIME ZONE

INTERVAL DAY TO SECOND

TIME WITH TIME ZONE

TIMESTAMP

NUMERIC

TIMESTAMP

TIMESTAMP

INTERVAL YEAR TO MONTH

TIMESTAMP

TIMESTAMP

INTERVAL DAY TO SECOND

TIMESTAMP

TIMESTAMP WITH TIME ZONE

NUMERIC

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

INTERVAL YEAR TO MONTH

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

INTERVAL DAY TO SECOND

TIMESTAMP WITH TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH

(The result type includes all the interval range of expr1 and expr2.)

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND

(The result type includes all the interval range of expr1 and expr2.)

Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

Example

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

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

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

+ (POSITIVE)

Syntax

+ expr

Description

The + sign is displayed in expr.

Example

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

- (NEGATIVE)

Syntax

- expr

Description

The - sign is displayed in expr.

Example

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

- (SUBTRACTION)

Syntax

expr1 - expr2

Description

It returns the subtraction result of expr1 and expr2.

The subtraction types and result types are as follows.
For more information, refer to Type Conversion.
Numeric - operation

expr1

expr2

Result type

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_BIGINT

NUMBER

NUMBER

NUMBER

NATIVE DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

(DATETIME/INTERVAL) - operation

expr1

expr2

Result type

DATE

DATE

NUMBER

DATE

Numeric type

DATE

DATE

INTERVAL YEAR TO MONTH

DATE

DATE

INTERVAL DAY

DATE

DATE

INTERVAL DAY TO SECOND

TIMESTAMP

TIME

TIME

INTERVAL DAY TO SECOND

TIME

Numeric type

TIME

TIME

INTERVAL YEAR TO MONTH

TIME

TIME

INTERVAL DAY TO SECOND

TIME

TIME WITH TIME ZONE

Numeric type

TIME WITH TIME ZONE

TIME WITH TIME ZONE

INTERVAL YEAR TO MONTH

TIME WITH TIME ZONE

TIME WITH TIME ZONE

INTERVAL DAY TO SECOND

TIME WITH TIME ZONE

TIMESTAMP

TIMESTAMP

INTERVAL DAY TO SECOND

TIMESTAMP

Numeric type

TIMESTAMP

TIMESTAMP

INTERVAL YEAR TO MONTH

TIMESTAMP

TIMESTAMP

INTERVAL DAY TO SECOND

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

INTERVAL DAY TO SECOND

TIMESTAMP WITH TIME ZONE

Numeric type

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

INTERVAL YEAR TO MONTH

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

INTERVAL DAY TO SECOND

TIMESTAMP WITH TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH

(The result type includes all the interval range of expr1 and expr2.)

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND

INTERVAL DAY TO SECOND

(The result type includes all the interval range of expr1 and expr2.)

Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

Example

gSQL> SELECT 
      TO_DATE( '2012-05-05' ) - TO_DATE( '2012-01-01' ) AS RESULT 
      FROM DUAL;
RESULT
------
   125
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-11'YEAR TO MONTH - INTERVAL'02-01'YEAR TO MONTH 
      AS RESULT 
      FROM DUAL;
RESULT    
----------
+000003-10
1 row selected.

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

/ (DIVISION)

Syntax

expr1 / expr2

Description

It returns the division result of expr1 and expr2.

The division types and result types are as follows.
For more information, refer to Type Conversion.
Numeric / operation

expr1

expr2

Result type

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NUMBER

NATIVE_DOUBLE

NATIVE_DOUBLE

NATIVE_DOUBLE

(DATETIME/INTERVAL) / operation

expr1

expr2

Result type

INTERVAL YEAR TO MONTH

Numeric type

INTERVAL YEAR TO MONTH

(The result type is interval type.)

INTERVAL DAY TO SECOND

Numeric type

INTERVAL DAY TO SECOND

(The result type is interval type.)

Refer to INTERVAL type details which is included in INTERVAL type written in the following table.

Example

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

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

|| (CONCATENATE)

Syntax

str1 || str2

Description

CONCATENATE returns the string concatenating str1 and str2.
If either str1 or str2 is NULL, the string except NULL is returned. If both of str1 and str2 are NULL, NULL is returned.
The argument can be a type which can be converted to either character string type or binary string type.
For more information, refer to Type Conversion.
It is an alias of CONCAT, CONCATENATE.
The result types are as follows.
The result types of || (CONCATENATE)

Data type

CHAR

VARCHAR

LONG VARCHAR

CHAR

CHAR

VARCHAR

LONG VARCHAR

VARCHAR

VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

LONG VARCHAR

LONG VARCHAR

LONG VARCHAR

Data type

BINARY

VARBINARY

LONG VARBINARY

BINARY

BINARY

VARBINARY

LONG VARBINARY

VARBINARY

VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

LONG VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

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

ABS

Syntax

ABS( num )

Description

ABS returns the absolute value of num.
The num argument can be a numeric type or types which can be converted to number.

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

Example

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

ADDDATE

Syntax

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

Description

ADDDATE adds the second argument to the first argument, then returns the result.
If any of the input argument value is NULL, the result is also NULL.
The first argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and the second argument data type can be INTERVAL or numeric.
The result type is as same as (DATETIME/INTERVAL) + operation.

Example

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

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

ADDTIME

Syntax

ADDTIME( expr1, expr2 )

Description

ADDTIME adds expr2 to expr1, then returns the result.

If expr1 or expr2 is NULL, the result is NULL.
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.
The result type is as same as (DATETIME/INTERVAL) + operation.

Example

gSQL> SELECT 
      ADDTIME( TO_TIMESTAMP( '2001-05-10 11:22:33', 
                             'YYYY-MM-DD HH24:MI:SS' ),
               INTERVAL'0 01:02:03.999999'DAY TO SECOND ) AS RESULT
        FROM DUAL;
RESULT                    
--------------------------
2001-05-10 12:24:36.999999
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.
If any of the input argument is NULL, the result is NULL.
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.
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.

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

Example

gSQL> SELECT ATAN( 1 ) FROM DUAL;
       ATAN( 1 )
----------------
.785398163397448
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.

Example

gSQL> SELECT ATAN2( 1, 0 ) FROM DUAL;
  ATAN2( 1, 0 )
---------------
1.5707963267949
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.
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.
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.
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.
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.

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

gSQL> SELECT BYTE_LENGTH( 'OCTET_LENGTH' ) AS RESULT_1BYTE_CHARACTERS 
        FROM DUAL;
RESULT_1BYTE_CHARACTERS
-----------------------
                     12
1 row selected.
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.
The result type is the data type of result1 (the first result). 
If the data type of result1 (the first result) is a numeric type and a character type then each data type includes the range of result1, ..., resultN.
If result1 (the first result) is CHAR or NULL, then the result type is VARCHAR.
CASE2 can be expressed by using CASE as follows.
CASE WHEN condition1 THEN res1
     WHEN condition2 THEN res2
     ELSE NULL
  END
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 the num argument 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.

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.

An input argument can be a numeric type and 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.
CASE WHEN expr1 IS NOT NULL THEN expr1
       ELSE expr2
  END
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.

Example

% gsql sys gliese

gSQL> SET SESSION AUTHORIZATION test;

Session set.

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

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

1 row selected.

CURRVAL

Syntax

seq_name.CURRVAL
CURRVAL(seq_name)

Description

The current value of the sequence object is obtained.

A sequence value should be set with NEXTVAL(seq_name) at least once.

Example

gSQL> SELECT seq.CURRVAL FROM dual;

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

1 row selected.

DATEADD

Syntax

DATEADD( datepart, number, date )

Description

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

If the number is decimal point, it is not rounded off.
The date data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE.
If number or date is NULL, the result is also NULL.
The result type which is as same as the input date argument type is returned.
Available string format in datepart

datepart

Description

YEAR

Year

QUARTER

Quarter

MONTH

Month

DAYOFYEAR

Day of year

DAY

Day

WEEK

Week

WEEKDAY

Weekday

HOUR

Hour

MINUTE

Minute

SECOND

Second

MILLISECOND

Millisecond

MICROSECOND

Microsecond

Example

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

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

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

DATEDIFF

Syntax

DATEDIFF( datepart, startdate, enddate )

Description

It substracts startdate from enddate, then returns the result to the specified datepart.

If the startdate or enddate is NULL, the result is also NULL.
The data type of startdate and enddate can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME.
The result type is NUMBER.
Available string format in datepart

datepart

Description

YEAR

Year

QUARTER

Quarter

MONTH

Month

DAYOFYEAR

Day of year

DAY

Day

HOUR

Hour

MINUTE

Minute

SECOND

Second

MILLISECOND

Millisecond

MICROSECOND

Microsecond

Example

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

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

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

DATE_ADD

Syntax

DATE_ADD( date, INTERVAL expr unit  )

Description

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

Example

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

DATE_PART

Syntax

DATE_PART( field, datetime )

Description

The result of DATE_PART is as same as the result of the EXTRACT function. It searches for the specified field from the input datetime type, and returns it.
The field argument should be text literal, and YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE can be specified to text literal.
The datetime argument data type can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIME, TIME WITH TIME ZONE, INTERVAL.
If field is not in the range of datetime, an error is returned. For DATE type, field should be YEAR, MONTH, DAY, otherwise an error is returned.

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.

DECODE can be expressed by using CASE as follows.

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

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

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.

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.

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 occurrenceth substr starting from str's position, and returns its location.

The data types of str arguments and substr arguments can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary character type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
The position argument and the occurrence argument can be numeric data type.
If position and occurrence are omitted, the default is 1.
The position and occurrence start from 1, and they are calculated in character unit according to character set (not in byte unit).
The position means the first position to search substr in str, it should not be zero, but an integer value.
  • If the position is positive: It compares forwards
     (toward the right) from the beginning of str until it finds the position of substr.
  •  If the position is negative: It compares backwards  
     (toward the left) from the end of str it finds the position of substr.
  •  If the position is 0: The result is 0.
The occurrence means the number of repeating the subtr in the str, and it should be a positive integer.

Example

gSQL> SELECT INSTR( 'INSTR( STR, SUBSTR )', 'SUB' ) AS RESULT1,
             INSTR( 'INSTR( STR, SUBSTR )', 'SUB', 5 ) AS RESULT2
        FROM DUAL;
RESULT1 RESULT2
------- -------
     13      13
1 row selected.

gSQL> SELECT INSTR( 'INSTR( STR, SUBSTR )', 'STR', 6, 2 ) AS RESULT1,
             INSTR( 'INSTR( STR, SUBSTR )', 'STR', -6, 2 ) AS RESULT2
      FROM DUAL;
RESULT1 RESULT2
------- -------
     16       3
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.

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.
gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

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

1 row selected.
gSQL> INSERT INTO t1(name) VALUES ( 'leekmo' ); 
1 row created.
gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

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

1 row selected.
gSQL> UPDATE t1 SET id = DEFAULT;

1 row updated.
gSQL> SELECT LAST_IDENTITY_VALUE() FROM dual;

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

1 row selected.
INSERT INTO t1 VALUES ( 100, 'jhkim' );

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

gSQL> SELECT LENGTHB( 'OCTET_LENGTH' ) AS RESULT_1BYTE_CHARACTERS 
        FROM DUAL;
RESULT_1BYTE_CHARACTERS
-----------------------
                     12
1 row selected.
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.

Example

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

LOCALTIME

Syntax

LOCALTIME [()]
STATEMENT_LOCALTIME()

Description

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

LOCALTIME is an SQL standard function.
The differences among the functions to obtain the current time are as follows.

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

Example

All rows have the same value.
gSQL>  SELECT LOCALTIME FROM t1;

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

3 rows selected.

LOCALTIMESTAMP

Syntax

LOCALTIMESTAMP [()]
STATEMENT_LOCALTIMESTAMP()

Description

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

LOCALTIMESTAMP is an SQL standard function.
The differences among the functions to obtain the current TIMESTAMP are as follows.

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

Example

All rows have the same value.
gSQL> SELECT LOCALTIMESTAMP FROM t1;

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

3 rows selected.

LOCAL_GROUP_ID

Syntax

LOCAL_GROUP_ID()

Description

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

It is a valid information in a cluster system.

Example

All rows have the same value.
gSQL> SELECT LOCAL_GROUP_ID() FROM DUAL;

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

1 row selected.

LOCAL_GROUP_NAME

Syntax

LOCAL_GROUP_NAME()

Description

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

It is a valid information in a cluster system.

Example

All rows have the same value.
gSQL> SELECT LOCAL_GROUP_NAME() FROM DUAL;

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

1 row selected.

LOCAL_MEMBER_ID

Syntax

LOCAL_MEMBER_ID()

Description

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

It is a valid information in a cluster system.

Example

All rows have the same value.
gSQL> SELECT LOCAL_MEMBER_ID() FROM DUAL;

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

1 row selected.

LOCAL_MEMBER_NAME

Syntax

LOCAL_MEMBER_NAME()

Description

It returns a cluster member name for a server which processes a query from a user.

It is a valid information in a cluster system.

Example

All rows have the same value.
gSQL> SELECT LOCAL_MEMBER_NAME() FROM DUAL;

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

1 row selected.

LOG

Syntax

LOG( num2 )
LOG( num1, num2 )

Description

It returns the logarithm of num2 in the num1 base.
If num1 is omitted, it returns the logarithm value whose base is 10.
num1 should be a positive number except 1 and 0, and num2 should be a positive number.

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.

Example

% gsql test test

gSQL> SELECT LOGON_USER() AS result FROM DUAL;

RESULT
------
TEST  

1 row selected.

LOWER

Syntax

LOWER( str )

Description

It returns lowercases of str.

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

Example

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

LPAD

Syntax

LPAD( str, length, [, fill] )

Description

It adds character string fill to the left side of str until the string length becomes length, then returns the result.

The str argument data type can be character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, and a binary character type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
The length argument is numeric type.
length means the number of characters, and its maximum range is the maximum precision of the result type. 
If fill is omitted, a white space is added.
If str is longer than the length, it cuts the str as long as the length, then returns it.
If any of str, length, fill is NULL, the result is also NULL. 
If length is 0 or a negative number, the result is NULL.
The following table describes the result types.
Result type of LPAD

str type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT LPAD( 'aa', 5, 'b' ) AS RESULT FROM DUAL;
RESULT
------
bbbaa 
1 row selected.

LTRIM

Syntax

LTRIM( trim_source [, trim_character ] )

Description

It removes the matching characters by comparing from the left side of trim_character in trim_source until the matching character does not exist. Then it returns the result.

The data type of trim_character and trim_source arguments can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, and a binary character type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
If any of trim_character, trim_source is NULL, the result is NULL.
If trim_character is omitted, a single blank space (' ') is specified by default.
The following table describes the result types.
Result type of LTRIM

trim_source, trim_character type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

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

MAX

Syntax

MAX( [ ALL | DISTINCT ] expr )

Description

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

If ALL is explicitly specified, aggregation is executed for all values.
If DISTINCT is explicitly specified, aggregation is executed for the values which exclude duplicate values.
If ALL or DISTINCT is not explicitly specified, it is processed in the same way as when ALL is specified.
MAX function returns the same result without being affected by the ALL and DISTINCT.

Example

gSQL> SELECT MAX(c1) FROM t1;

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

1 row selected.

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.

Example

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

MONTHS_BETWEEN

구문

MONTHS_BETWEEN( date1, date2 )

Description

MONTHS_BETWEEN returns the number of months of which days between date2 and date1 are divided by 31.
If date1 or date2 is NULL, then the result is also NULL.
The date1 argument and date2 argument can be DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE type.
The result type is NUMBER.

If the same date (e.g. 2014-01-15 and 2014-02-15), or the last day of the month (e.g. 2014-08-31 and 2014-09-30) is included both in date1 and date2, then it returns the integer result regardless of the agreement of timestamp section (if it exists).

Example

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

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

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

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

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

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

NEXT_DAY

Syntax

NEXT_DAY( date, day )

Description

It obtains a date of the day (day of week) which comes first after the given date (an argument).
The second day argument can be a string or a number which indicates the day.
• String: SUNDAY ~ SATURDAY  or SUN ~ SAT
• Number: 1 (sunday) ~ 7 (saturday)
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

gSQL> SELECT NEXT_DAY( TO_DATE( '2010-05-01', 'YYYY-MM-DD' ),
                       'SUNDAY' ) AS RESULT1 
        FROM DUAL;

RESULT1   
----------
2010-05-02

gSQL> SELECT NEXT_DAY( TO_DATE( '2010-05-01', 'YYYY-MM-DD' ),
                       'SUN' ) AS RESULT1 
        FROM DUAL;

RESULT1   
----------
2010-05-02

gSQL> SELECT NEXT_DAY( TO_DATE( '2010-05-01', 'YYYY-MM-DD' ),
                       1 ) AS RESULT1 
        FROM DUAL;

RESULT1   
----------
2010-05-02

gSQL> SELECT TO_CHAR( NEXT_DAY( TO_DATE( '2010-05-01', 'YYYY-MM-DD' ),
                                'SUNDAY' ),
                      'YYYY-MM-DD HH24:MI:SS' ) AS RESULT2 
        FROM DUAL;

RESULT2            
-------------------
2010-05-02 00:00:00

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

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

gSQL> SELECT OCTET_LENGTH( 'OCTET_LENGTH' ) AS RESULT_1BYTE_CHARACTERS 
        FROM DUAL;
RESULT_1BYTE_CHARACTERS
-----------------------
                     12
1 row selected.
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.
For more information, refer to SUBSTRING.
The following table describes the result types.
Result type of OVERLAY

str1, str2 types

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT 
      OVERLAY( 'RESULT_OF_XXX_FUNC' PLACING 'OVERLAY' FROM 11 FOR 3 ) 
      AS RESULT 
      FROM DUAL;
RESULT                
----------------------
RESULT_OF_OVERLAY_FUNC
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.

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.

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.
Result type of REPEAT

str type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT REPEAT( 'ab', 3 ) AS RESULT FROM DUAL;
RESULT
------
ababab
1 row selected.

REPLACE

Syntax

REPLACE( str, from, to )

Description

It replaces all from strings in str string with to strings, and returns the result.

The str argument, the from argument, and the to argument can be character data types such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.
If str is NULL, the result is also NULL.
If from is NULL, the str is returned without replacement.
If to value is omitted or NULL, the str value of which from is removed is returned.
The following table describes the result types.
Result type of REPLACE

str type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

Example

gSQL> SELECT REPLACE( 'HI GLIESE', 'HI', 'HELLO' ) AS RESULT FROM DUAL;
RESULT      
------------
HELLO GLIESE
1 row selected.

REVERSE

Syntax

REVERSE( str )

Description

REVERSE returns characters of str in reverse order.
The str argument can be types that are convertible to a character string type or a binary string type.
A character string type is performed in a character unit, and a binary string type can be performed in a byte unit.
If str is NULL, then it returns NULL.
The following table describes the arguments and result types.
Argument and result type of REVERSE

str

Result type

CHAR

CHAR

VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY

BINARY

VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT REVERSE( 'GOLDILOCKS' ) AS RESULT FROM DUAL;

RESULT      
----------
SKCOLIDLOG

1 row selected.

gSQL> SELECT REVERSE( '선재소프트 2018' ) AS RESULT FROM DUAL;

RESULT         
---------------
8102 트프소재선

1 row selected.

ROUND( number )

Syntax

ROUND( num [, scale ] )

Description

It rounds off num based on scale, and returns the result.

The num argument and scale argument can be numeric data types.
If scale is omitted, the scale becomes 0 and is executed as if it is ROUND(num, 0).
If scale is a positive number, it is rounded off based on the number of right digit of the decimal point. If scale is a negative number, it is rounded off based on the number of left digit of the decimal point.

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. 
The result type is always DATE regardless of the date argument data type.
If fmt is omitted, the default is DAY.
The following table describes the available format strings.
Available format sting of fmt

String

Description

CC, SCC

It is represented in four digit year by rounding off from 51 year.

(e.g. XX01)

YYYY, YEAR, SYYYY, SYEAR, YYY, YY, Y

It is rounded off from July 1st.

IYYY, IYY, IY, I

It is the year embracing the calendar week defined by ISO 8601 standards, and it is rounded off from July 1st.

Q

It is rounded off from the 16th day in the second month of the quarter.

MONTH, MON, MM, RM

It is rounded off from the 16th day.

WW

A week starts from January 1st of the year, and it is rounded off on wednesday 12 p.m of WEEK.

IW

It is the calendar week defined by ISO 8601 standards (1 ~ 52 weeks or 1 ~ 53 weeks), and it is rounded off on thursday 12 p.m.

W

A week starts from the 1st day of the month, and it is rounded off on wednesday 12 p.m of WEEK.

DDD, DD, J

It is rounded off at 12 p.m.

DAY, DY, D

It is rounded off on wednesday 12 p.m of WEEK.

HH, HH12, HH24

It is rounded off from 30 minutes.

MI

It is rounded off from 30 seconds.

Example

gSQL> SELECT 
      ROUND( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'CC' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2101-01-01
1 row selected.

gSQL> SELECT 
      ROUND( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'YYYY' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2052-01-01
1 row selected.

gSQL> SELECT 
      ROUND( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'MONTH' ) AS   RESULT 
      FROM DUAL;
RESULT    
----------
2051-08-01
1 row selected.

gSQL> SELECT 
      ROUND( TO_TIMESTAMP( '2001-05-05 15:22:33.999999', 
                           'YYYY-MM-DD HH24:MI:SS.FF6' ) ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2001-05-06
1 row selected.

ROWID_GRID_BLOCK_ID

Syntax

ROWID_GRID_BLOCK_ID( rowid )

Description

It returns the GRID block ID.

It is a valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_GRID_BLOCK_ID( ROWID ) FROM T1;
C1 ROWID_GRID_BLOCK_ID( ROWID )
-- ----------------------------
 1                           52
 2                           52
 3                           52

3 rows selected.

ROWID_GRID_BLOCK_SEQ

Syntax

ROWID_GRID_BLOCK_SEQ( rowid )

Description

It returns the GRID block sequence.

It is a valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_GRID_BLOCK_SEQ( ROWID ) FROM T1;
C1 ROWID_GRID_BLOCK_SEQ( ROWID )
-- -----------------------------
 1                        747465
 2                        747466
 3                        747467

3 rows selected.

ROWID_MEMBER_ID

Syntax

ROWID_MEMBER_ID( rowid )

Description

It returns the member ID.

It is a valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_MEMBER_ID( ROWID ) FROM T1;
C1 ROWID_MEMBER_ID( ROWID )
-- ------------------------
 1                        1
 2                        1
 3                        1

3 rows selected.

ROWID_OBJECT_ID

Syntax

ROWID_OBJECT_ID( rowid )

Description

It returns the object ID.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_OBJECT_ID( t1.ROWID ) FROM t1;
ROWID_OBJECT_ID( T1.ROWID )
---------------------------
                      22012
                      22012
                      22012
                      22012
4 rows selected.

ROWID_PAGE_ID

Syntax

ROWID_PAGE_ID( rowid )

Description

It returns the page ID.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_PAGE_ID( t1.ROWID ) FROM t1;
ROWID_PAGE_ID( T1.ROWID )
-------------------------
                     8227
                     8227
                     8227
                     8227
4 rows selected.

ROWID_ROW_NUMBER

Syntax

ROWID_ROW_NUMBER( rowid )

Description

It returns the row number.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_ROW_NUMBER( t1.ROWID ) FROM t1;
ROWID_ROW_NUMBER( T1.ROWID )
----------------------------
                           0
                           1
                           2
                           3
4 rows selected.

ROWID_SHARD_ID

Syntax

ROWID_SHARD_ID( rowid )

Description

It returns the shard ID.

It is a valid information in a cluster system.

Example

gSQL> SELECT C1, ROWID_SHARD_ID( ROWID ) FROM T1;
C1 ROWID_SHARD_ID( ROWID )
-- -----------------------
 1                       0
 2                       1
 3                       2

3 rows selected.

ROWID_TABLESPACE_ID

Syntax

ROWID_TABLESPACE_ID( rowid )

Description

It returns the tablespace ID.

It is an invalid information in a cluster system.

Example

gSQL> SELECT ROWID_TABLESPACE_ID( t1.ROWID ) FROM t1;
ROWID_TABLESPACE_ID( T1.ROWID )
-------------------------------
                              2
                              2
                              2
                              2
4 rows selected.

ROWNUM

Syntax

ROWNUM

Description

It sequentially allocates a number starting from 1 to rows which satisfy the WHERE condition.

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

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

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

C1
--
A 
B 
C 

3 rows selected.
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.

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

C1
--
B 
C 

2 rows selected.
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).

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;
SQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

    ROWNUM       C1
---------- ----------
     1        1
     2        2

SQL> DROP INDEX t1_idx;
SQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

    ROWNUM       C1
---------- ----------
     1        1
     2        2
     3        3
     4        4
     5        5
gSQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

ROWNUM C1
------ --
     1  1
     2  2
     3  3
     4  4
     5  5

5 rows selected.

gSQL> DROP INDEX t1_idx;

Index dropped.

gSQL> SELECT ROWNUM, c1 FROM t1 WHERE c1 < ROWNUM + 3;

ROWNUM C1
------ --
     1  1
     2  2
     3  3
     4  4
     5  5

5 rows selected.

Example

gSQL> SELECT ROWNUM, c1 FROM t1;

ROWNUM C1
------ --
     1 A 
     2 B 
     3 C 
     4 D 
     5 E 

5 rows selected.

RPAD

Syntax

RPAD( str, length, [, fill] )

Description

It adds fill string to the right side of str until the string's length becomes length, and it returns the result.

The str argument can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONGVARYING, or a binary character type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
The length argument can be a numeric type.
length means the number of characters, and its maximum range is the maximum PRECISION of the result type. 
If fill is omitted, a white space is added.
If str is longer than length, it cuts the str as long as the length, then returns it.
If any of str, length, fill is NULL, the result is also NULL. 
If length is 0 or a negative number, the result is NULL.
The following table describes the result types.
Result type of RPAD

str type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT RPAD( 'aa', 5, 'b' ) AS RESULT FROM DUAL;
RESULT
------
aabbb 
1 row selected.

RTRIM

Syntax

RTRIM( trim_source [, trim_character ] )

Description

It removes the matching characters by comparing from the right side of trim_character in trim_source until the matching character does not exist. Then it returns the result.

The data type of trim_character and trim_source arguments can be a character type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING or a binary data type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
If any of trim_character, trim_source is NULL, the result is NULL.
If trim_character is omitted, a single blank space (' ') is specified by default.
The following table describes the result types.
Result type of RTRIM

trim_source type, trim_character type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT RTRIM( 'RTRIM_____', '_' ) AS RESULT FROM DUAL;
RESULT
------
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.

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.

It is a valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_GROUP_ID( T1, T1.C1 ) FROM T1;
C1 SHARD_GROUP_ID( T1, T1.C1 )
-- ---------------------------
A                            1
B                            2
C                            3

3 rows selected.


gSQL> SELECT SHARD_GROUP_ID( T1, 'B' ) FROM DUAL;
SHARD_GROUP_ID( T1, 'B' )
-------------------------
                        2

1 row selected.

SHARD_GROUP_NAME

Syntax

SHARD_GROUP_NAME( table_name, shard_key_value [, ... ] )

Description

It returns the group NAME managing the shard which stores shard_key_value when the shard strategy is defined in the table_name.
The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.
The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.
The result type is VARCHAR.

It is a valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_GROUP_NAME( T1, T1.C1 ) FROM T1;

C1 SHARD_GROUP_NAME( T1, T1.C1 )
-- -----------------------------
A  G1                           
B  G2                           
C  G3                           

3 rows selected.

gSQL> SELECT SHARD_GROUP_NAME( T1, 'B' ) FROM DUAL;

SHARD_GROUP_NAME( T1, 'B' )
---------------------------
G2                         

1 row selected.

SHARD_ID

Syntax

SHARD_ID( table_name, shard_key_value [, ... ] )

Description

It returns the ID for the shard which stores shard_key_value when the shard strategy is defined in the table_name.
The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.
The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.
The result type is NATIVE_BIGINT.

It is a valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_ID( T1, T1.C1 ) FROM T1;
C1 SHARD_ID( T1, T1.C1 )
-- ---------------------
A                      0
B                      1
C                      2

3 rows selected.


gSQL> SELECT SHARD_ID( T1, 'B' ) FROM DUAL;
SHARD_ID( T1, 'B' )
-------------------
                  1

1 row selected.

SHARD_NAME

Syntax

SHARD_NAME( table_name, shard_key_value [, ... ] )

Description

It returns the NAME for the shard which stores shard_key_value when the shard strategy is defined in the table_name.
The table_name (an input argument) should be described by an identifier. If an object corresponding to the table_name is not a base table, or if the shard strategy is not defined, then an error occurs.
The shard_key_value (an input argument) should be listed in an order of shard key column in the shard strategy defined in the table_name. If the number of shard_key_value and the number of shard key columns is not same, then an error occurs.
The result type is VARCHAR.

It is a valid information in a cluster system.

Example

gSQL> SELECT T1.C1, SHARD_NAME( T1, T1.C1 ) FROM T1;

C1 SHARD_NAME( T1, T1.C1 )
-- -----------------------
A  S1                     
B  S2                     
C  S3                     

3 rows selected.

gSQL> SELECT SHARD_NAME( T1, 'B' ) FROM DUAL;

SHARD_NAME( T1, 'B' )
---------------------
S2                   

1 row selected.

SHIFT_LEFT

Syntax

SHIFT_LEFT( num, cnt )

Description

It moves num to the left as many as cnt bits, and returns the movement values.

The data type of input num argument and cnt argument can be NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, or the type which can be converted to NATIVE_BIGINT.
When converting to NATIVE_BIGINT type, the decimal point is truncated.
cnt is masked with 6 bit, and it is processed to a value in the range within 6 bit.
The result type is NATIVE_BIGINT.

Example

gSQL> SELECT SHIFT_LEFT( 7, 3 ) AS RESULT FROM DUAL;
RESULT
------
    56
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.
The result type is NATIVE_BIGINT.

Example

gSQL> SELECT SHIFT_RIGHT( 56, 3 ) AS RESULT FROM DUAL;
RESULT
------
     7
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.

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.

Example

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

SPLIT_PART

Syntax

SPLIT_PART( string, delimiter, field )

Description

It returns a character string of the field by specifying a character as delimiter within a string.

The data type of string argument and delimiter argument can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING.
The field argument can be a numeric data type.
If any of string, delimiter, field is NULL, the result is also NULL.
The value of field should be a numeric value above 1, and if it is 0 or a negative number, an error is returned.
The following table describes the result types.
Result type of SPLIT_PART

string type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

Example

gSQL> SELECT SPLIT_PART( 'AB;CD;EF;GH', ';', 3  ) AS RESULT FROM DUAL;
RESULT
------
EF    
1 row selected.

SQRT

Syntax

SQRT( num )

Description

It returns the square root of num.

The num argument can be a numeric type, and it should not be a negative number, but above 0.

Example

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

STATEMENT_DATE

Syntax

STATEMENT_DATE()
CURRENT_DATE [()]

Description

The current date(DATE type) value is obtained.

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

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

Example

gSQL> SELECT STATEMENT_DATE() AS result FROM t1;

RESULT    
----------
2013-12-12
2013-12-12
2013-12-12

3 rows selected.

STATEMENT_LOCALTIME

Syntax

STATEMENT_LOCALTIME()
LOCALTIME [()]

Description

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

LOCALTIME is an SQL standard function.
The differences among the functions to obtain the current time are as follows.

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

Example

All rows have the same time value.
gSQL> SELECT STATEMENT_LOCALTIME() AS result FROM t1;

RESULT         
---------------
16:18:50.775870
16:18:50.775870
16:18:50.775870

3 rows selected.

STATEMENT_LOCALTIMESTAMP

Syntax

STATEMENT_LOCALTIMESTAMP()
LOCALTIMESTAMP [()]

Description

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

LOCALTIMESTAMP is an SQL standard function.
The differences among the functions to obtain the current timestamp are as follows.

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

Example

All rows have the same value.
gSQL> SELECT STATEMENT_LOCALTIMESTAMP() FROM t1;

STATEMENT_LOCALTIMESTAMP()
--------------------------
2013-12-12 16:23:39.782187
2013-12-12 16:23:39.782187
2013-12-12 16:23:39.782187

3 rows selected.

STATEMENT_TIME

Syntax

STATEMENT_TIME()
CURRENT_TIME [()]

Description

The current TIME WITH TIME ZONE type value is obtained.

CURRENT_TIME is an SQL standard function.
The differences among the functions to obtain the current time are as follows.

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

Example

All rows have the same time value.
gSQL> SELECT STATEMENT_TIME() AS result FROM t1;

RESULT                
----------------------
16:28:19.268513 +09:00
16:28:19.268513 +09:00
16:28:19.268513 +09:00

3 rows selected.

STATEMENT_TIMESTAMP

Syntax

STATEMENT_TIMESTAMP()
CURRENT_TIMESTAMP [()]

Description

The current TIMESTAMP WITH TIME ZONE type value is obtained.

CURRENT_TIMESTAMP is an SQL standard function.
The differences among the functions to obtain the current timestamp are as follows.

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

Example

All rows have the same value.
gSQL> SELECT STATEMENT_TIMESTAMP() AS result FROM t1;

RESULT                           
---------------------------------
2013-12-12 16:36:11.032957 +09:00
2013-12-12 16:36:11.032957 +09:00
2013-12-12 16:36:11.032957 +09:00

3 rows selected.

STATEMENT_VIEW_SCN

Syntax

STATEMENT_VIEW_SCN()

Description

It obtains VIEW SCN of the current STATEMENT.

Example

gSQL> SELECT STATEMENT_VIEW_SCN() FROM dual;

STATEMENT_VIEW_SCN()
--------------------
17697.658.17880     

1 row selected.

STATEMENT_VIEW_SCN_DCN

Syntax

STATEMENT_VIEW_SCN_DCN()

Description

It obtains the Domain Change Number (DCN) value of the current STATEMENT's VIEW SCN.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_DCN() FROM dual;

STATEMENT_VIEW_SCN_DCN()
------------------------
                     658

1 row selected.

STATEMENT_VIEW_SCN_GCN

Syntax

STATEMENT_VIEW_SCN_GCN()

Description

It obtains the Global Change Number (GCN) value of the current STATEMENT's VIEW SCN.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_GCN() FROM dual;

STATEMENT_VIEW_SCN_GCN()
------------------------
                   17697

1 row selected.

STATEMENT_VIEW_SCN_LCN

Syntax

STATEMENT_VIEW_SCN_LCN()

Description

It obtains the Local Change Number (LCN) value of the current STATEMENT's VIEW SCN.

Example

gSQL> SELECT STATEMENT_VIEW_SCN_LCN() FROM dual;

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

1 row selected.

STDDEV

Syntax

STDDEV( [ ALL | DISTINCT ] expr )

Description

It is an aggregation function, and it obtains the standard deviation of an expr set.
If ALL is specified, this function is performed for all values. If DISTINCT is specified, this function is performed for the values of which the duplicates were deleted from. If it is not specified, it is processed as if ALL is apecified.
If the number of expr sets except for NULL after deleting the duplicates by using DISTINCT is one, then it returns 0 like as VARIANCE.
The following table describes the arguments and result types.
Argument and result type of STDDEV

expr

Result type

NATIVE_INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NATIVE_DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

GOLDILOCKS gets the standard deviation as follows.
　• If the number of expr sets is 1, then it returns 0.
　• If the number of expr sets is bigger than 1, It returns the value of STDDEV_SAMP( expr ).

The standard deviation is a positive square root of a variance, and it is obtained calculating the square root of the variance. In other words, the STDDEV function is as same as the square root of VARIANCE function.


STDDEV( [ ALL ] expr )

= SQRT( VARIANCE( [ ALL ] expr ) )


STDDEV( DISTINCT expr )

= SQRT( VARIANCE( DISTINCT expr ) )

Example

gSQL> SELECT STDDEV(c1) FROM t1;

      STDDEV(C1)
----------------
11.4978258814438

1 row selected.


gSQL> SELECT STDDEV(ALL c1) FROM t1;

  STDDEV(ALL C1)
----------------
11.4978258814438

1 row selected.


gSQL> SELECT STDDEV(DISTINCT c1) FROM t1;

STDDEV(DISTINCT C1)
-------------------
   13.2759180473518

1 row selected.

STDDEV_POP

Syntax

STDDEV_POP( expr )

Description

It is an aggregation function, and it obtains the population standard deviation of an expr set. If the number of expr sets except for NULL is one, then it returns 0.
The following table describes the arguments and result types.
Argument and result type of STDDEV_POP

expr

Result type

NATIVE_INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NATIVE_DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

The population standard deviation is a positive square root of a population variance, and it is obtained by calculating the square root of the population variance. In other words, the STDDEV_POP function is as same as the square root of VAR_POP function.


STDDEV_POP( expr )

= SQRT( VAR_POP( expr ) )

Example

gSQL> SELECT STDDEV_POP(c1) FROM t1;

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

1 row selected.

STDDEV_SAMP

Syntax

STDDEV_SAMP( expr )

Description

It is an aggregation function, and it obtains the sample standard deviation of an expr set. If the number of expr sets except for NULL is one, then it returns NULL.
The following table describes the arguments and result types.
Argument and result type of STDDEV_SAMP

expr

Result type

NATIVE_INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NATIVE_DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

The sample standard deviation is a positive square root of a sample variance, and it is obtained by calculating the square root of the sample variance. In other words, the STDDEV_SAMP function is as same as the square root of VAR_SAMP function.


STDDEV_SAMP( expr )

= SQRT( VAR_SAMP( expr ) )

Example

gSQL> SELECT STDDEV_SAMP(c1) FROM t1;

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

1 row selected.

SUBSTR

Syntax

SUBSTR( str FROM start_position [ FOR string_length ] )
SUBSTR( str, start_position [ , string_length ] )

Description

It is an alias of SUBSTRING.

Example

gSQL> SELECT 
      SUBSTR( 'DATABASE MANAGEMENT SYSTEM', 10, 10 ) AS RESULT 
      FROM DUAL;
RESULT    
----------
MANAGEMENT
1 row selected.
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

gSQL> SELECT 
      SUBSTRB( 'DATABASE MANAGEMENT SYSTEM', 10, 10 ) AS RESULT 
      FROM DUAL;
RESULT    
----------
MANAGEMENT
1 row selected.
gSQL> SELECT SUBSTRB( '“αβ≠ΑΒ”', 4, 11 ) AS RESULT FROM DUAL;
RESULT
------
αβ≠ΑΒ 
1 row selected.

SUBSTRING

Syntax

SUBSTRING( str FROM start_position [ FOR string_length ] )  
SUBSTRING( str, start_position [ , string_length ] )

Description

It extracts characters which are within string_length range from start_position, and returns the result for str.

The str argument data type can be a character data type such as CHARACTER, CHARACTER VARYING, CHARACTER LONG VARYING, or a binary data type such as BINARY, BINARY VARYING, BINARY LONG VARYING.
The start_position argument and string_length argument can be a numeric data type.
If any of str, start_position, string_length is NULL, the result is NULL. 
The start_position and string_length start from 1, and they are calculated in character unit according to character set (not in byte unit).
If start_position is 0, the start_position is assigned to 1. 
If start_position is a positive number, it searches for the position forwards (towards right) from the beginning of str. 
If start_ position is a negative number, it searches for the position backwards (towards left) from the end of str. 
If string_length is omitted, characters from the start_position to the last character of str, are returned.
If string_length is 0 or a negative number, the result is NULL.
If start_position > (str length), the result is NULL.
If (str length + start_position) < 0, the result is NULL.
It is an alias of SUBSTR.
For more information, refer to SUBSTRB.
The following table describes the result types.
Result type of SUBSTRING

str type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT 
      SUBSTRING( 'DATABASE MANAGEMENT SYSTEM' FROM 10 FOR 10 ) AS RESULT 
      FROM DUAL;
RESULT    
----------
MANAGEMENT
1 row selected.
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 21:13:32.123456 +09:00',
                           'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM') 
          ) AS RESULT 
      FROM DUAL;
RESULT                    
--------------------------
2017-05-25 12:13:32.123456
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.

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 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.
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.
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_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_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_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.
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.
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.
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.
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 converts all characters which are same as the characters in from to its corresponding characters in to. Then it returns the result.

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.
Characters in string which are not same as characters in from, are not replaced.
Characters in string which are same as characters in from, are replaced to its corresponding characters in to.
If the number of characters in from is bigger than the number of characters in to, the characters in from which does not correspond to the characters in to, are removed, and returned.
If the same characters are repeated multiple times in from, they are replaced with the first mapped character.
The following table describes the result types.
Result type of TRANSLATE

string type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

Example

gSQL> SELECT TRANSLATE( '12345', '15', 'FL'  ) AS RESULT FROM DUAL;
RESULT
------
F234L 
1 row selected.

gSQL> SELECT TRANSLATE( 'ABC12345', 'ABC12345', 'XYZ' ) AS RESULT FROM DUAL;
RESULT
------
XYZ   
1 row selected.

gSQL> SELECT TRANSLATE( 'ABC12345ABC', 'ABCABCABC', 'XYZ^&*xyz' ) AS RESULT 
      FROM DUAL;
RESULT     
-----------
XYZ12345XYZ
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.
The following table describes the result types.
Result type of TRIM

trim_character, trim_source type

Result type

CHAR or VARCHAR

VARCHAR

LONG VARCHAR

LONG VARCHAR

BINARY or VARBINARY

VARBINARY

LONG VARBINARY

LONG VARBINARY

Example

gSQL> SELECT TRIM( LEADING '_' FROM '___TRIM FUNCTION___' ) AS RESULT 
      FROM DUAL;
RESULT          
----------------
TRIM FUNCTION___
1 row selected.

gSQL> SELECT TRIM( TRAILING '_' FROM '___TRIM FUNCTION___' ) AS RESULT 
      FROM DUAL;
RESULT          
----------------
___TRIM FUNCTION
1 row selected.

gSQL> SELECT TRIM( BOTH '_' FROM '___TRIM FUNCTION___' ) AS RESULT 
      FROM DUAL;
RESULT       
-------------
TRIM FUNCTION
1 row selected.

TRUNC( number )

Syntax

TRUNC( num [ , scale ] )

Description

It truncates the num based on scale, then returns the result.

The num argument and scale argument can be a numeric type.
If 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.
The result type is always DATE regardless of the input date type.
If fmt is omitted, the default is DAY, and the available format string is described in the following table.
Available format string in fmt

Format string

Description

CC, SCC

Century

YYYY, YEAR, SYYYY, SYEAR, YYY, YY, Y

Year

IYYY, IYY, IY, I

The year embracing the calendar week defined by ISO 8601 standards

Q

Quarter

MONTH, MON, MM, RM

Month

WW

The week whose first week starts from January 1st of the year

IW

The week containing the first thursday of the year designated as the calendar week by ISO 8601 standards (1 ~ 52 weeks or 1 ~ 53 weeks) becomes the first week.

W

The week whose first week starts from the first day of the month

DDD, DD, J

Day

DAY, DY, D

Day of the week

HH, HH12, HH24

Hour

MI

Minute

Example

gSQL> SELECT 
      TRUNC( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'CC' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2001-01-01
1 row selected.

gSQL> SELECT 
      TRUNC( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'YYYY' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2051-01-01
1 row selected.

gSQL> SELECT 
      TRUNC( TO_DATE( '2051-07-16', 'YYYY-MM-DD' ), 'MONTH' ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2051-07-01
1 row selected.

gSQL> SELECT 
      TRUNC( TO_TIMESTAMP( '2001-05-05 11:22:33.999999',         
             'YYYY-MM-DD HH24:MI:SS.FF6' ) ) AS RESULT 
      FROM DUAL;
RESULT    
----------
2001-05-05
1 row selected.

UPPER

Syntax

UPPER( str )

Description

It returns the uppercase characters of str.

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

Example

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

UNHEX

Syntax

UNHEX( str )

Description

str argument is a hexadecimal character and this function represents it as each byte and returns it as a binary string.
The input argument can be a character type such as CHARACTER VARYING, CHARACTER LONG VARYING. The result type is a binary character type such as BINARY VARYING or BINARY LONG VARYING.
If str is NULL, then the result value is also NULL.
If str includes a character which does not belong to the hexadecimal range, then it returns an error.
For more information, refer to HEX.

Example

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

UNHEX_TO_CHARSTR

Syntax

UNHEX_TO_CHARSTR( str )

Description

str argument is a hexadecimal character and this function represents it as each byte and returns it as a character string.
The input argument can be a character type such as CHARACTER VARYING, CHARACTER LONG VARYING. The result type is a character type such as CHARACTER VARYING, CHARACTER LONG VARYING.
If str is NULL, then the result value is also NULL.
If str includes a character which does not belong to the hexadecimal range, then it returns an error.
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.

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

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

Example

% gsql test test

gSQL> SELECT USER_ID() FROM dual;

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

1 row selected.

UUID

Syntax

UUID()

Description

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

Example

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

VAR_POP

Syntax

VAR_POP( expr )

Description

It is an aggregation function, and it obtains the population variance of an expr set. If the number of expr sets except for NULL is one, then it returns 0.
The following table describes the arguments and result types.
Argument and result type of VAR_POP

expr

Result type

NATIVE_INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NATIVE_DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

The population variance is a variance of the population (entire) group, and it is the average of the square value of deviation. In other words, it is calculated by extracting the population average (the entire average) from each value of the data, and squaring each value, then adding them together and dividing them by the number of datas in the population group.

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

For more information, refer to STDDEV_POP.

Example

gSQL> SELECT VAR_POP(c1) FROM t1;

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

1 row selected.

VAR_SAMP

Syntax

VAR_SAMP( expr )

Description

It is an aggregation function, and it obtains the sample variance of an expr set. If the number of expr sets except for NULL is one, then it returns NULL.
The following table describes the arguments and result types.
Argument and result type of VAR_SAMP

expr

Result type

NATIVE_INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NATIVE_DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

Unlike the population variance dealing with the population (entire) group, the sample variance deals with the average and deviation of extracted samples. In other words, it is calculated by extracting the sample average from each value of the data, and squaring each value, then adding them together and dividing them by the number of datas in the population group minus 1.

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

For more information, refer to STDDEV_SAMP.

Example

gSQL> SELECT VAR_SAMP(c1) FROM t1;

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

1 row selected.

VARIANCE

Syntax

VARIANCE( [ ALL | DISTINCT ] expr )

Description

It is an aggregation function, and it obtains the variance of an expr set.
If ALL is specified, this function is performed for all values. If DISTINCT is specified, this function is performed for the values of which the duplicates were deleted from. If it is not specified, it is processed as if ALL is apecified.
If the number of expr sets except for NULL after deleting the duplicates by using DISTINCT is one, then it returns 0.
The following table describes the arguments and result types.
Argument and result type of VARIANCE

expr

Result type

NATIVE_INTEGER family

  • NATIVE_SMALLINT

  • NATIVE_INTEGER

  • NATIVE_BIGINT

NATIVE_DOUBLE

NUMBER

NUMBER

NATIVE_DOUBLE family

  • NATIVE_REAL

  • NATIVE_DOUBLE

NATIVE_DOUBLE

GOLDILOCKS gets the variance as follows.

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

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

For more information, refer to STDDEV.

Example

gSQL> SELECT VARIANCE(c1) FROM t1;

VARIANCE(C1)
------------
       132.2

1 row selected.


gSQL> SELECT VARIANCE(ALL c1) FROM t1;

VARIANCE(ALL C1)
----------------
           132.2

1 row selected.


gSQL> SELECT VARIANCE(DISTINCT c1) FROM t1;

VARIANCE(DISTINCT C1)
---------------------
               176.25

1 row selected.

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.