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 first +/- means a positive or negative value of the entire number, and it can be omitted. If omitted, the default value is a positive value.

• In <digits>, numbers from 0 to 9 can be listed without white space, the number with decimal point can be written by using decimal point (.).

• An exponent form can be written after the first <digits>, and E or e is written, and it stands for exponent. Then +/- is written according to exponent sign, and <digits> is written. +/- of exponent can be omitted. If omitted, the default value is a positive value.

• Lastly, character such as f, F, d, D can be written after numbers. It means that it is a number of BINARY_FLOAT, BINARY_DOUBLE. If the corresponding character is omitted, the number is considered as NUMBER type.

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

• DATE'string literal'

  • The format of date type is ' SYYYY-MM-DD'.

  • DATE'2002-07-15'

• TO_DATE(string_literal [, format])

  • If the format is not specified, the format of date type is NLS_DATE_FORMAT by default.

  • If the format is specified, the specified format is applied.

• The date type includes year, month, day, hour, minute, second (except for fractional seconds).

• If the date is omitted, the default value is the first day of the current month.

• If the hour, minute, second are omitted, the default value is midnight.

  • HH24 format: '00:00:00'

  • HH12 format: '12:00:00'

• To set the hour, minute, second to the default value (midnight) when the date value includes hour, minute, second, then use the TRUNC(date) function.

• For example, in TRUNC(SYSDATE), SYSDATE includes the values of the year, month, day, hour, minute, second.

• To compare only the values of the year, month, day among date values, then set the values of the hour, minute, second are set to midnight using the TRUNC function.

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

• The following is an example of date literals.

DATE'2002-07-15'

• The following is an example of when the format is not specified, so NLS_DATE_FORMAT is 'YYYY-MM-DD'.

TO_DATE( '2002-07-15' )

• The followings are examples of when the format is specified.

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

• The following is an example of when the date is omitted. (It is set to the first day of the current month).

gSQL> SELECT TO_DATE( '2000-07', 'YYYY-MM' ) FROM DUAL;
TO_DATE( '2000-07', 'YYYY-MM' )
-------------------------------
2000-07-01

• The following is an example of when the hour, minute, second are omitted. (It is set to the midnight).

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

• The following is an example of setting the hour, minute, second of DATE value (SYSDATE) to the default value (midnight).

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

• The following is an example of comparing only the values of the year, month, day among date values.

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

• TIME'string literal'

  • The format of time type is 'HH24:MI:SS[.[FF6]]'.

  • TIME'15:30:59.999999'

• TO_TIME(string_literal [, format])

  • If the format is not specified, the format of time type is NLS_TIME_FORMAT by default.

  • If the format is specified, the specified format is applied.

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.

• The following is an example of time literals.

TIME'15:30:59.999999'

• The following is an example of when the format is not specified, so NLS_TIME_FORMAT is 'HH24:MI:SS.FF6'.

TO_TIME( '15:30:59.999999' )

• The following is an example of when the format is specified.

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'string literal' or TIME WITH TIME ZONE'string literal'

  • The format of time with time zone type is 'HH24:MI:SS[.[FF6]] TZH:TZM'.

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

  • TIME WITH TIME ZONE'15:30:59.999999 +09:00'

• TO_TIME_WITH_TIME_ZONE(string_literal [, format] )

  • If the format is not specified, the format of time with time zone type is NLS_TIME_WITH_TIME_ZONE_FORMAT by default.

  • If the format is specified, the specified format is applied.

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.

• The followings are examples of time with time zone literals.

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

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

• The following is an example of when the format is not specified, so NLS_TIME_WITH_TIME_ZONE_FORMAT is 'HH24:MI:SS.FF6 TZH:TZM'.

TO_TIME_WITH_TIME_ZONE( '15:30:59.999999 +09:00' )
TO_TIME_TZ( '15:30:59.999999 +09:00' )

• The following is an example of when the format is specified.

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'string literal'

  • The format of timestamp type is 'SYYYY-MM-DD HH24:MI:SS[.[FF6]]'.

  • TIMESTAMP'2002-07-15 15:39:59.999999'

• TO_TIMESTAMP(string_literal [, format] )

  • If the format is not specified, the format of timestamp type is NLS_TIMESTAMP_FORMAT by default.

  • If the format is specified, the specified format is applied.

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.

• The following is an example of timestamp literals.

TIMESTAMP'2002-07-15 15:39:59.999999'

• The following is an example of when the format is not specified, so NLS_TIMESTAMP_FORMAT is 'YYYY-MM-DD HH24:MI:SS.FF6'.

TO_TIMESTAMP( '2002-07-15 15:39:59.999999' )

• The following is an example of when the format is specified.

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'string literal' or TIMESTAMP WITH TIME ZONE'string literal'

  • The format of timestamp with time zone type is 'SYYYY-MM-DD HH24:MI:SS[.[FF6]] TZH:TZM'.

  • 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(string_literal [, formt] )

  • If the format is not specified, the format of timestamp with time zone type is NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT by default.

  • If the format is specified, the specified format is applied.

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.

• The following is an example of timestamp with time zone literals.

TIMESTAMP'2002-07-15 15:39:59.999999 +09:00'
TIMESTAMP WITH TIME ZONE'2002-07-15 15:39:59.999999 +09:00'

• The following is an example of when the format is not specified, so NLS_TIMESTAMP_WITH_TIME_ZONE_FORMAT is 'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'.

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

• The following is an example of when the format is specified.

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.

• Year-month INTERVAL values

  • It includes YEAR and MONTH.

  • Display string expression: 'year-month'

  • It can be written in a form as INTERVAL 'string literal' YEAR[leading precision] TO MONTH or NUMTOYMINTERVAL( num, interval_indicator )

• Day-time INTERVAL values

  • It includes DAY, HOUR, MINUTE, SECOND (fractional seconds).

  • Display string expression: 'day hour:minute:second.fractional_seconds'

  • It can be written in a form as INTERVAL 'string literal' DAY[leading precision] TO SECOND[fractiona l seconds precision] or NUMTODSINTERVAL( num, interval_indicator ).

• The interval value's sign is specified only once at the beginning of the string representation.

  e.g. INTERVAL'+3 11:22:33.999999'DAY TO SECOND (O)

  INTERVAL'-3 +11:22:33.999999'DAY TO SECOND (X)

The followings are the list of interval types.

• INTERVAL YEAR (leading precision)

• INTERVAL MONTH (leading precision)

• INTERVAL YEAR (leading precision) TO MONTH

• INTERVAL DAY (leading precision)

• INTERVAL HOUR (leading precision)

• INTERVAL MINUTE (leading precision)

• INTERVAL SECOND (leading precision[, fractional seconds precision] )

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

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 *, NUMTOYMINTERVAL, NUMTODSINTERVAL.

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 MONTH

The followings are examples of using interval MONTH literals.

Interval YEAR TO MONTH

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

Interval DAY

The followings are examples of using interval DAY literals.

Interval HOUR

The followings are examples of using interval HOUR literals.

Interval MINUTE

The following are examples of using interval MINUTE literals.

Interval SECOND

The followings are examples of using interval SECOND literals.

Interval DAY TO HOUR

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

Interval DAY TO MINUTE

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

Interval DAY TO SECOND

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

Interval HOUR TO MINUTE

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

Interval HOUR TO SECOND

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

Interval MINUTE TO SECOND

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

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 other than specified location.

SQL reserved words can be used as identifiers by using double quotes ("), but it is not recommended to use it as follows, because readability decreases.

gSQL> CREATE TABLE "SELECT" ( "FROM" INTEGER );

Table created.

gSQL> INSERT INTO "SELECT" ( "FROM" ) VALUES ( 1 );

1 row created.

gSQL> SELECT "FROM" FROM "SELECT";

FROM
----
   1

1 row selected.

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.

Data Type

Numeric Type

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

• Classification by the storage method

  • Decimal numeric type

    • It stores decimal numbers in 100 digits basis.

    • Types: NUMBER, NUMERIC, FLOAT

  • Binary numeric type

    • It stores numbers of C language as it is.

    • Types: NATIVE_INTEGER, NATIVE_DOUBLE

• Classification by fractional part representation

  • Fixed point data type (exact numeric)

    • It is a numeric type whose scale is fixed.

    • Types: NUMERIC(precision, scale), NATIVE_INTEGER

  • Floating point data type (approximate numeric)

    • It is a numeric type whose scale is not fixed.

    • Types: FLOAT(precision), NATIVE_DOUBLE

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 Floating Point Number Type

The decimal floating point number type is defined in SQL.

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

CHARACTER STRING Type

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

• Classification by whether it is a variable length string

  • Fixed length string

    • Refer to CHARACTER.

  • Variable length string

    • Refer to CHARACTER VARYING, CHARACTER LONG VARYING.

• Classification by the maximum length of a string

  • 2000 [ characters or bytes ]

    • Refer to CHARACTER.

  • 4000 [ characters or bytes ]

    • Refer to CHARACTER VARYING.

  • 100 megabytes

    • Refer to CHARACTER LONG VARYING.

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.

• Classification by whether it is a variable length binary string

  • Fixed length binary string

    • Refer to BINARY.

  • Variable length binary string

    • Refer to BINARY VARYING, BINARY LONG VARYING.

• Classification by the maximum length of binary string

  • 2000

    • Refer to BINARY.

  • 4000

    • Refer to BINARY VARYING.

  • 100 Mega Bytes

    • Refer to BINARY LONG VARYING.

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

The following literals can be stored in the boolean data type.

• TRUE

  • Keyword: TRUE

  • Literal: 't', 'true' , 'y', 'yes' , 'on' ,'1'

• FALSE

  • Keyword: FALSE

  • Literal: 'f', 'false', 'n', 'no', 'off', '0'

• UNKNOWN

  • Keyword: UNKNOWN, NULL

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.

• Type conversion for the VC comparison

• Type conversion for the LC comparison

• Type conversion for the VB comparison

• Type conversion for the LB comparison

• Type conversion for the NB comparison

• Type conversion for the ND comparison

• Type conversion for the NU comparison

• Type conversion for the DA comparison

• Type conversion for the TI comparison

• Type conversion for the TZ comparison

• Type conversion for the TS comparison

• Type conversion for the SZ comparison

• Type conversion for the YM comparison

• Type conversion for the DS comparison

• Type conversion for the BO comparison

• Type conversion for the RI comparison

Type Conversion

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

• Implicit type conversion occurs in an expression, an operator, a function, a condition, for select, insert, delete, update.

• Explicit type conversion occurs through the CAST operator.

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

• Conversion to CHARACTER type

  • When the source type is CHARACTER type:

    • If the source type precision is bigger than the CHARACTER type precision, then an error occurs.

    • If the source type precision is equal to the CHARACTER type precision, then the string is not changed.

    • If the source type precision is smaller than the CHARACTER type precision, then space characters are added to the string as many as the precision difference.

  • When the source type is CHARACTER VARYING type or CHARACTER LONG VARYING type:

    • If the source type string length is bigger than the CHARACTER type precision, then an error occurs.

    • If the source type string length is equal to the CHARACTER type precision, then the string is not changed.

    • If the source type string length is smaller than the CHARACTER type precision, then space characters are added to the string as many as precision difference.

  • When the source type is numeric type, date/time type, INTERVAL type, BOOLEAN type or ROWID type:

    • If the converted string length of the source type is bigger than the CHARACTER type precision, then an error occurs.

    • If the converted string length of the source type is equal to the CHARACTER type precision, the string is not changed.

    • If the converted string length of the source type is smaller than the CHARACTER type precision, then space characters are added to the string as many as precision difference.

• Conversion to CHARACTER VARYING type

  • When the source type is CHARACTER type:

    • If the source type precision is bigger than the CHARACTER VARYING type precision, then an error occurs.

    • If the source type precision is equal or smaller than the CHARACTER VARYING type precision, then the string is not changed.

  • When the source type is CHARACTER VARYING type or CHARACTER LONG VARYING type:

    • If the source type string length is bigger than the CHARACTER VARYING type precision, then an error occurs.

    • If the source type string length is equal or smaller than the CHARACTER VARYING type precision, then the string is not changed.

  • When the source type is numeric type, date/time type, INTERVAL type, BOOLEAN type or ROWID type:

    • If the converted string length of the source type is bigger than the CHARACTER VARYING type precision, then an error occurs.

    • If the converted string length of the source type is equal or smaller than the CHARACTER VARYING type precision, then the string is not changed.

• Conversion to CHARACTER LONG VARYING type

  • When the source type is CHARACTER STRING type:

    • The source type string is not changed.

  • When the source type is numeric type, date/time type, INTERVAL type, BOOLEAN type or ROWID type:

    • The converted string of the source type is not changed.

• Conversion to BINARY type

  • When the source type is BINARY type:

    • If the source type precision is bigger than the BINARY type precision, then an error occurs.

    • If the source type precision is equal to the BINARY type precision, then the binary string is not changed.

    • If the source type precision is smaller than the BINARY type precision, the X'00' characters are added to the binary string as many as the precision difference.

  • When the source type is BINARY VARYING type or BINARY LONG VARYING type:

    • If the source type binary string length is bigger than the BINARY type precision, then an error occurs.

    • If the source type binary string length is equal to the BINARY type precision, then the binary string is not changed.

    • If the source type binary string length is smaller than the BINARY type precision, the X'00' characters are added to a binary string as many as precision difference.

• Conversion to BINARY VARYING type

  • When the source type is BINARY type:

    • If the source type precision is bigger than the BINARY VARYING type precision, then an error occurs.

    • If the source type precision is equal or smaller than the BINARY VARYING type precision, then the binary string is not changed.

  • When the source type is BINARY VARYING type, BINARY LONG VARYING type:

    • If the source type binary string length is bigger than the BINARY VARYING type precision, then an error occurs.

    • If the source type binary string length is equal or smaller than the BINARY VARYING type precision, the binary string is not changed.

• Conversion to BINARY LONG VARYING type

  • If the source type is BINARY STRING type, the source type binary string is not changed.

• Conversion to numeric type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the numeric format, then an error occurs.

    • An overflow or rounding can occur due to the precision and scale which is defined in the converted type.

  • When the source type is numeric type:

    • An overflow or rounding can occur due to the precision and scale which is defined in the converted type.

  • When the source type is INTERVAL type:

    • It can be converted to the numeric type only when the source type is the single field (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND).

    • An overflow or rounding can occur due to the precision and scale which is defined in the converted type.

• Conversion to DATE type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the DATE type format, then an error occurs.

    • An overflow can occur due to a value range which is defined in the converted type.

  • When the source type is DATE type or TIMESTAMP type:

    • The error does not occur.

  • When the source type is TIMESTAMP WITH TIME ZONE type:

    • It is converted to the DATE type value upon consideration of the time zone offset.

• Conversion to TIME type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the TIME type format, then an error occurs.

    • A rounding can occur due to a value range which is defined in the converted type.

  • When the source type is TIME type or TIMESTAMP type:

    • The error does not occur.

  • When the source type is TIME WITH TIME ZONE type or TIMESTAMP WITH TIME ZONE type:

    • It is converted to the value of TIME type upon consideration of the time zone offset.

• Conversion to TIME WITH TIME ZONE type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the TIME WITH TIME ZONE type format, then an error occurs.

    • A rounding can occur due to a value range which is defined in the converted type.

  • When the source type is TIME type, TIME WITH TIME ZONE type or TIMESTAMP WITH TIME ZONE type:

    • It is converted to the value of TIME WITH TIME ZONE type upon consideration of the time zone offset.

• Conversion to TIMESTAMP type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the TIMESTAMP type format, then an error occurs.

    • An overflow or rounding can occur due to a value range which is defined in the converted type.

  • When the source type is DATE type or TIMESTAMP type:

    • The error does not occur.

  • When the source type is TIMESTAMP WITH TIME ZONE type:

    • It is converted to the TIMESTAMP type value upon consideration of the time zone offset.

• Conversion to TIMESTAMP WITH TIME ZONE type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the TIMESTAMP WITH TIME ZONE type format, then an error occurs.

    • An overflow or rounding can occur due to a value range which is defined in the converted type.

  • When the source type is DATE type, TIMESTAMP type or TIMESTAMP WITH TIME ZONE type:

    • It is converted to the value of TIMESTAMP WITH TIME ZONE type upon consideration of the time zone offset.

• Conversion to INTERVAL YEAR TO MONTH family type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the year-month interval literal format, then an error occurs.

    • For more information, refer to Interval Literals.

    • An overflow can occur due to a precision which is defined in the converted type.

  • When the source type is NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, NUMBER, NUMERIC or FLOAT type:

    • The converted type should be a single field (YEAR, MONTH).

    • An overflow can occur due to a precision which is defined in the converted type.

  • When the source type is INTERVAL YEAR TO MONTH family type:

    • An overflow can occur due to a precision which is defined in the converted type.

• Conversion to INTERVAL DAY TO SECOND family type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the day-time interval literal format, then an error occurs.

    • For more information, refer to Interval Literals.

    • An overflow or rounding can occur due to the precision which is defined in the converted type.

  • When the source type is NATIVE_SMALLINT, NATIVE_INTEGER, NATIVE_BIGINT, NUMBER, NUMERIC, or FLOAT type:

    • The converted type should be a single field (DAY, HOUR, MINUTE, SECOND)

    • An overflow or rounding can occur due to the precision which is defined in the converted type.

  • When the source type is INTERVAL DAY TO SECOND family type:

    • An overflow or rounding can occur due to the precision which is defined in the converted type.

• Conversion to BOOLEAN type

  • When the source type is CHARACTER STRING type:

    • The conversion is available when the string is "TRUE" or "FALSE", and it is case-insensitive. (It is convertible even when the string includes a space before and after it.)

  • When the source type is BOOLEAN type:

    • The error does not occur.

• Conversion to ROWID type

  • When the source type is CHARACTER STRING type:

    • If the string does not comply with the ROWID type format, then an error occurs.

  • When the source type is ROWID type:

    • The error does not occur.

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.

• The following describes an execution result of a CASE operator.

SELECT CASE expr WHEN expr THEN char(3)
                 WHEN expr THEN char(5)
                 ELSE char(1)
       END   
  FROM t1;

• The following describes an execution result of a set operator.

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

  • set operator

  • CASE operator

    • CASE Expression

    • COALESCE

    • NULLIF

Result Type Combination Rule

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

• Examples of applying result type combination rule

  • set operator

  • CASE operator

    • CASE Expression

    • COALESCE

    • NULLIF

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

• The result type of when all is CHAR type

  • If the lengths are different, then it is VARCHAR type.

  • If the lengths are equal, then it is CHAR type.

• The result type of when all is BINARY type

  • If the lengths are different, then it is VARBINARY type.

  • If the lengths are equal, then it is BINARY type.

• The result type of INTERVAL YEAR TO MONTH type

  • If YEAR type and MONTH type are mixed, then it is INTERVAL YEAR TO MONTH type.

  • If there is only YEAR type, then it is INTERVAL YEAR type.

  • If there is only MONTH type, then it is INTERVAL MONTH type.

• The result type of INTERVAL DAY TO SECOND type

  • The start field is the biggest range field of each target expression.

  • The end field is the smallest range field of each target expression.

  • e.g. {INTERVAL DAY, INTERVAL HOUR} => INTERVAL DAY TO HOUR

• The precision, scale of the result type

  • CHARACTER STRING type

    • The maximum character length of the target expression

  • BINARY STRING type

    • The maximum character length of the target expression

  • NUMERIC type

    • It specifies an acceptable maximum range of the type's value.

  • TIME/TIMESTAMP type

    • The maximum fractional seconds precision of the target expression

  • INTERVAL YEAR TO MONTH

    • The maximum leading precision of the target expression

  • INTERVAL DAY TO SECOND

    • Leading precision is the maximum leading precision of the start field

    • Fractional seconds precision is the maximum fractional seconds precision.

• For more information, refer to Type Comparison.

Compatibility for Data Type

The SQL standard compatibility for data type is as follows.