SQL References (H~Z)

INSERT INTO

Function

It creates new rows in the table.

Syntax

<insert statement> ::=
    INSERT [ /*+ <append insert hint clause> */ ]
        INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
    ;

<append insert hint clause> ::=
    APPEND [ ( append insert option element [, ...] ) ]

<append insert option element> ::=
      PARALLEL [NOLOGGING]
    | STATEMENT_NOFORCE
    | <index maintenance options>

<insert maintenance options> ::=
      IMMEDIATE_INDEX_MAINTENANCE
    | DEFERRED_INDEX_MAINTENANCE
    | SKIP_INDEX_MAINTENANCE

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

Invocation and Access Rules

The user must satisfy the following conditions to perform <insert statement>.

Syntax Rules and Parameters

<append insert hint clause>

It specifies the hint to perform data insertion using the APPEND INSERT method.

<append insert option element>

It specifies the options that can be used when inserting data using the APPEND INSERT method. If the specified option cannot be applied, the insert statement fails.

<index maintenance options>

It specifies the index maintenance options that can be used when inserting data using the APPEND INSERT method.

table_name

It is the name of the target table where the row will be created.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used.

[ ( column_name [, ...] ) ]

It is the name of a column in the table.
The column list can be omitted.
The number of columns must match the number of <insert source> values. If any columns are omitted, their values will be set to DEFAULT.

<values clause>

It is a list of values to be assigned to the corresponding columns.

Multiple rows can be created as follows.

INSERT INTO table_name VALUES ( 1, 'A' ), ( 2, 'B' ), ( 3, 'C' )

<from subquery>

It is a query used to create rows.
For more information, refer to the query expression clause of the SELECT statement.

DEFAULT VALUES

It fills all columns with their default values.
The DEFAULT VALUES clause is equivalent to the following:
VALUES ( DEFAULT, DEFAULT, ..., DEFAULT )

Description

Differences Between INSERT Statements

Examples

The following is an example of creating a single row using the INSERT statement.

gSQL> INSERT INTO region VALUES ( 0, 'AFRICA' );

1 row created.

The following examples demonstrate how to use DEFAULT or identity values for columns in an INSERT statement.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.

• DEFAULT is inserted into all columns.

gSQL> INSERT INTO region DEFAULT VALUES;

1 row created.

• DEFAULT is inserted into all columns.

gSQL> INSERT INTO region VALUES (DEFAULT, DEFAULT);

1 row created.

• If a column is omitted, the DEFAULT value of the r_name column is used.

gSQL> INSERT INTO region(r_regionkey) VALUES (-100);

1 row created.

• If a column is omitted, the identity value of the r_regionkey column is used.

gSQL> INSERT INTO region(r_name) VALUES ('ASIA');

1 row created.


gSQL> SELECT * FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          1 N/A                      
          2 N/A                      
       -100 N/A                      
          3 ASIA                     

4 rows selected.

The following is an example of creating multiple rows by specifying them in the VALUES clause.

gSQL> INSERT INTO region
       VALUES ( 1, 'AFRICA' ),
              ( 2, 'ASIA'   ),
              ( 3, 'EUROPE' );

3 rows created.

The following is an example of creating multiple rows using a subquery.

gSQL> INSERT INTO region SELECT r_regionkey, r_name FROM tmp_region WHERE r_regionkey < 3;

3 rows created.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

F222

INSERT statement: DEFAULT VALUES clause

O

S204

Enhanced structured types

X

S043

Enhanced reference types

X

T111

Updatable joins, unions, and columns

X

For More Information

Refer to the following.

INSERT INTO name RETURNING

Function

It creates new rows in the table and retrieves them.

Syntax

<insert statement> ::=
    INSERT [ /*+ <append insert hint clause> */ ]
        INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <returning clause>
    ;

<append insert hint clause> ::=
    APPEND [ ( append insert option element [, ...] ) ]

<append insert option element> ::=
      PARALLEL [NOLOGGING]
    | STATEMENT_NOFORCE
    | <index maintenance options>

<insert maintenance options> ::=
      IMMEDIATE_INDEX_MAINTENANCE
    | DEFERRED_INDEX_MAINTENANCE
    | SKIP_INDEX_MAINTENANCE

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

<returning clause> ::=
      [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...]

Invocation and Access Rules

The user must satisfy the following conditions to execute an <insert returning query statement>.

Syntax Rules and Parameters

<append insert hint clause>

It specifies the hint to perform data insertion using the APPEND INSERT method.

<append insert option element>

It specifies the options that can be used when inserting data using the APPEND INSERT method. If the specified option cannot be applied, the insert statement fails.

<index maintenance options>

It specifies the index maintenance options that can be used when inserting data using the APPEND INSERT method.

table_name

It is the name of the target table in which the row will be created.

[ ( column_name [, ...] ) ]

It is the names of the columns in the table.
For more information, refer to the INSERT INTO statement.

<values clause>

It is a list of values to be assigned to the corresponding columns.
For more information, refer to the INSERT INTO statement.

<from subquery>

It is a query used to create rows.
For more information, refer to the INSERT INTO statement.

DEFAULT VALUES

It fills all columns with their default values.
For more information, refer to the INSERT INTO statement.

<returning clause>

It returns the inserted rows.

RETURN and RETURNING are equivalent keywords.

Description

For more information, refer to Differences Between INSERT Statements.

Examples

The following is an example of retrieving column values created using the INSERT statement.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.
gSQL> INSERT INTO region VALUES ( DEFAULT, DEFAULT ) RETURNING r_regionkey, r_name;

R_REGIONKEY R_NAME                   
----------- -------------------------
          1 N/A                      

1 row created.
gSQL> INSERT INTO region(r_name) VALUES ('ASIA') RETURNING r_regionkey;

R_REGIONKEY
-----------
          2

1 row created.

The following is an example of retrieving rows created from a subquery.

gSQL> INSERT INTO region 
      SELECT r_regionkey, r_name FROM tmp_region WHERE r_regionkey < 3 
      RETURNING r_regionkey, r_name;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     

3 rows created.

Compatibility

The SQL standard does not define the <insert returning query statement>.

For More Information

Refer to the following.

INSERT INTO name RETURNING .. INTO

Function

It creates a row in the table and retrieves its values into host variables.

Syntax

<insert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <returning into clause>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | <from default>

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<from default> ::=
    DEFAULT VALUES

<returning into clause> ::=
      [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...] INTO variable_name [, ...]

Invocation and Access Rules

The user must satisfy the following conditions to execute an <insert returning into statement>.

Syntax Rules and Parameters

<append insert hint clause>

It specifies the hint to perform data insertion using the APPEND INSERT method.

<append insert option element>

It specifies the options that can be used when inserting data using the APPEND INSERT method. If the specified option cannot be applied, the insert statement fails.

<index maintenance options>

It specifies the index maintenance options that can be used when inserting data using the APPEND INSERT method.

table_name

It is the name of the target table in which the row will be created.

[ ( column_name [, ...] ) ]

It is the names of the columns in the table.
For more information, refer to the INSERT INTO statement.

<values clause>

It is a list of values to be assigned to the corresponding columns.
For more information, refer to the INSERT INTO statement.

<from subquery>

It is a query used to create rows.
For more information, refer to the INSERT INTO statement.

DEFAULT VALUES

It fills all columns with their default values.
For more information, refer to the INSERT INTO statement.

<returning clause>

It returns the inserted rows.
For more information, refer to <returning clause> in INSERT INTO name RETURNING statement.

INTO variable_name [, ...]

The number of variables specified in the INTO clause must match the number of expressions in the RETURNING clause.
Only one or less row can be inserted. If more than one row is inserted, an error occurs.

Description

For more information, refer to Differences Between INSERT Statements.

Example

The following is an example of retrieving the values of an created row into host variables.

gSQL> CREATE TABLE region
(
    r_regionkey   BIGINT    GENERATED BY DEFAULT AS IDENTITY
  , r_name        CHAR(25)  DEFAULT 'N/A'
);

Table created.

gSQL> COMMIT;

Commit complete.

• Declares the host variables.

\VAR v_key  BIGINT
\VAR v_name VARCHAR(128)

• Retrieving DEFAULT values into host variables.

gSQL> INSERT INTO region 
      VALUES ( DEFAULT, DEFAULT ) 
      RETURNING r_regionkey, r_name 
      INTO :v_key, :v_name;

V_KEY V_NAME                   
----- -------------------------
    1 N/A                      

1 row created.

• Retrieving the values of omitted columns into host variables.

gSQL> INSERT INTO region(r_name) 
      VALUES ('ASIA') 
      RETURNING r_regionkey 
      INTO :v_key;

V_KEY
-----
    2

1 row created.

Compatibility

The SQL standard does not define the <insert returning into statement>.

For More Information

Refer to the following.

INSERT INTO name ... UPDATE

Function

It creates new rows into the table. If a unique constraint is violated, the existing rows are updated instead.

Syntax

<upsert statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
    ;

<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

Invocation and Access Rules

The user must satisfy the following conditions to execute a <upsert statement>.

Syntax Rules and Parameters

table_name

It is the name of the target table in which the row will be created.
If an update is performed due to a unique constraint violation, this specifies the name of the target table to be updated.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used.

[ ( column_name [, ...] ) ]

It is the names of the columns in the table.
For more information, refer to [ ( column_name [, ...] ) ] clause of the INSERT INTO statement.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to <values clause> of the INSERT INTO statement.

<from subquery>

It is a query used to create rows.
For more information, refer to query expression clause of the SELECT statement.

DEFAULT VALUES

It fills all columns with their default values.
For more information, refer to DEFAULT VALUES clause of the INSERT INTO statement.

<duplicate key clause>

It defines the action to perform when a unique constraint is violated.

DO NOTHING

It does nothing if a unique constraint is violated.

<do update clause>

If a unique constraint is violated, it updates column values according to the <set clause>.

<set value clause>

It defines the values to assign to the columns to be updated.

The values can be specified in the following way:

DO UPDATE SET column1 = value1, column2 = value2, column3 = value3
DO UPDATE SET column1 = DEFAULT, column2 = DEFAULT, column3 = DEFAULT

Use the values from the <insert source> as the update values.

DO UPDATE SET column1 = VALUES(column1), column2 = VALUES(column2), column3 = VALUES(column2)

<set clause>

It defines the columns to be updated and the values to assign. The number of columns in the <set clause> must match the number of values.

The values can be defined in the following ways:

ON DUPLICATE KEY
   DO UPDATE SET column1 = value1, column2 = value2, column3 = value3
ON DUPLICATE KEY
   DO UPDATE SET ( column1, column2, column3 ) = ( value1, value2, value3 )
ON DUPLICATE KEY
   DO UPDATE SET column1 = ( SELECT max(value1) FROM other_table_name )

The <query expression> must be a query that returns exactly one row.

If DEFAULT is used as a column value, the default value (refer to <default clause>.) in the CREATE TABLE statement is used. If no default is defined, NULL is assigned instead.

Description

Differences Between INSERT INTO name ... UPDATE Statements

<upsert statement> is a deterministic statement.

The following are two different but equivalent UPSERT statements that must produce the same result.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 );

3 rows created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

3 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4

3 rows selected.
gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 );

3 rows created.

gSQL> INSERT INTO t1 VALUES( 3 ),( 2 ),( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

3 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4

3 rows selected.

Examples

The following is an example where a row is updated due to a unique constraint violation.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

1 row created.

gSQL> SELECT * FROM t1;

C1
--
 2

1 row selected.

The following is an example where no update is performed when a unique constraint is violated.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY DO NOTHING;

no rows created.

gSQL> SELECT * FROM t1;

C1
--
 1

1 row selected.

The following is an example of inserting or updating multiple rows using a subquery.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ),( 4 );

4 rows created.

gSQL> INSERT INTO t1 ( SELECT c1 FROM t1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1;

4 rows created.

gSQL> SELECT * FROM t1;

C1
--
 2
 3
 4
 5

4 rows selected.

Compatibility

The SQL standard does not define the <upsert statement>.

For More Information

Refer to the following.

INSERT INTO name ... UPDATE RETURNING

Function

It creates new rows into the table. If a unique constraint is violated, the existing rows are updated. Then, the inserted or updated rows are retrieved.

Syntax

<upsert returning statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        <returning clause>    
    ;
<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

<returning clause> ::=
    [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...]

Invocation and Access Rules

The user must satisfy the following conditions to execute a <upsert returning statement>.

Syntax Rules and Parameters

table_name

It is the name of the target table in which the row will be created.
If an update is performed due to an unique constraint violation, this specifies the name of the target table to be updated. 
For more information, refer to table_name clause of  the INSERT INTO name ... UPDATE statement.

[ ( column_name [, ...] ) ]

It is the names of the columns in the table.
For more information, refer to [ ( column_name [, ...] ) ] clause of the INSERT INTO statement.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to <values clause> of the INSERT INTO statement.

<from subquery>

It is a query used to create rows.
For more information, refer to query expression clause of the SELECT statement.

DEFAULT VALUES

It fills all columns with their default values.
For more information, refer to DEFAULT VALUES clause of the INSERT INTO statement.

<duplicate key clause>

It defines the action to perform when a unique constraint is violated.

DO NOTHING

It does nothing if a unique constraint is violated.

<do update clause>

If a unique constraint is violated, it updates column values according to the <set clause>.

<set value clause>

It defines the values to assign to the columns to be updated.
For more information, refer to <set value clause> of the INSERT INTO name ... UPDATE statement.

<set clause>

It defines the columns to be updated and the values to assign. The number of columns in the <set clause> must match the number of values.
For more information, refer to <set clause> of the INSERT INTO name ... UPDATE.

<returning clause>

It returns the inserted or updated rows.

Description

For more information, refer to the Differences Between INSERT INTO name ... UPDATE Statements.

The following is an example of inserting four rows and returning the inserted results.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ), ( 2 ), ( 3 ), ( 4 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1;

C1
--
 1
 2
 3
 4

4 rows created.

The following is an example of updating rows due to a unique constraint violation and returning the updated results.

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ),( 2 ),( 3 ),( 4 );

4 rows created.

gSQL> INSERT INTO t1 ( SELECT c1 FROM t1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1;

C1
--
 2
 3
 4
 5

4 rows created.

Compatibility

The SQL standard does not define the <upsert returning statement>.

For More Information

Refer to the following.

INSERT INTO name ... UPDATE RETURNING ... INTO

Function

It creates a single row in the table. If a unique constraint is violated, the existing row is updated instead. Then, the created or updated row is retrieved into the host variable.

Syntax

<upsert returning into statement> ::=
    INSERT INTO table_name [ ( column_name [, ...] ) ]
        <insert source>
        <duplicate key clause>
        <returning clause>    
        <into clause>    
    ;
<insert source> ::=
      <values clause>
    | <from subquery>
    | DEFAULT VALUES

<values clause> ::=
    VALUES { ( { <value expression> | DEFAULT } [, ...] ) } [, ...]

<from subquery> ::=
    <query expression>

<duplicate key clause>
    ON DUPLICATE KEY { DO NOTHING | <do update clause> }

<do update clause> ::=
    [DO] UPDATE [SET] <set clause>  [, ...]

<set value clause> ::= 
      <value expression>
    | DEFAULT
    | VALUES( column_name )

<set clause> ::=
      column_name = <set value clause>
    | ( column_name [, ...] ) = ( <set value clause> [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )

<returning clause> ::=
    [ RETURN | RETURNING ] { * | { <value expression> [ [AS] alias_name ] } [, ...] 

<into clause> ::= INTO variable_name [, ...]

Invocation and Access Rules

The user must satisfy the following conditions to execute a <upsert returning into statement>.

Syntax Rules and Parameters

table_name

It is the name of the target table in which the row will be created.
If an update is performed due to an unique constraint violation, this specifies the name of the target table to be updated. 
For more information, refer to table_name clause of the INSERT INTO name ... UPDATE statement.

[ ( column_name [, ...] ) ]

It is the names of the columns in the table.
For more information, refer to [ ( column_name [, ...] ) ] clause of the INSERT INTO statement.

<values clause>

It is the list of values to be assigned to the corresponding columns.
For more information, refer to <values clause> of the INSERT INTO statement.

<from subquery>

It is a query used to create rows.
For more information, refer to query expression clause of the SELECT statement.

DEFAULT VALUES

It fills all columns with their default values.
For more information, refer to DEFAULT VALUES clause of the INSERT INTO statement.

<duplicate key clause>

It defines the action to perform when a unique constraint is violated.

DO NOTHING

It does nothing if a unique constraint is violated.

<do update clause>

If a unique constraint is violated, it updates column values according to the <set clause>.

<set value clause>

It defines the values to assign to the columns to be updated.
For more information, refer to <set value clause> of the INSERT INTO name ... UPDATE statement.

<set clause>

It defines the columns to be updated and the values to assign. The number of columns in the <set clause> must match the number of values.
For more information, refer to <set clause> of the INSERT INTO name ... UPDATE.

<returning clause>

It returns the inserted or updated rows.
For more information, refer to <returning clause> of the INSERT INTO name ... UPDATE RETURNING.

<into clause>

The number of variables specified in the INTO clause must match the number of expressions specified in the RETURNING clause. 
At most one row can be created. If two or more rows are created, an error occurs.

Description

For more information, refer to the Differences Between INSERT INTO name ... UPDATE Statements.

The following is an example of inserting a single row and retrieving the inserted result into a host variable.

gSQL> \VAR v_c1 INTEGER;

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1 INTO :v_c1;

V_C1
----
   1

1 row created.

The following is an example of updating a single row due to a unique constraint violation and retrieving the updated result into a host variable.

gSQL> \VAR v_c1 INTEGER;

gSQL> CREATE TABLE t1 ( c1 INTEGER UNIQUE );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 );

1 row created.

gSQL> INSERT INTO t1 VALUES( 1 ) ON DUPLICATE KEY UPDATE c1 = c1 + 1 RETURNING c1 INTO :v_c1;

V_C1
----
   2

1 row created.

Compatibility

The SQL standard does not define the <upsert returning into statement>.

For More Information

Refer to the following.

LOCK TABLE

Function

It locks one or more tables.

Syntax

<lock table statement> ::=
    LOCK TABLE lock target [, ...] 
    IN <lock mode> MODE [<wait clause>]
    ;

<lock mode> ::=
    SHARE
    | EXCLUSIVE
    | ROW SHARE
    | ROW EXCLUSIVE
    | SHARE ROW EXCLUSIVE


<wait clause> ::=
    NOWAIT
    | WAIT time

Invocation and Access Rules

One of the following privileges is required to execute the <lock table statement>.

Syntax Rules and Parameters

<lock target>

It specifies the target table to be locked.

<lock mode>

It specifies the LOCK mode.

<wait clause>

It specifies the wait time for acquiring a lock.

Description

When a transaction is committed or rolled back, all acquired locks are automatically released. If the ROLLBACK TO SAVEPOINT statement is used, all locks acquired after the specified savepoint are released.

Examples

The following is an example of locking the TABLE t1 to prevent any update operations by another transaction.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE;

Table locked.

The following is an example of executing a LOCK statement on multiple tables.

gSQL> LOCK TABLE t1, t2 IN EXCLUSIVE MODE;

Table locked.

The following is an example of acquiring a SHARE ROW EXCLUSIVE lock for the TABLE t1.

gSQL> LOCK TABLE t1 IN SHARE ROW EXCLUSIVE MODE;

Table locked.
The following statement can be executed only if the lock can be acquired on the TABLE immediately. If the lock cannot be acquired, an error occurs.
gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE NOWAIT;

Table locked.

The following is an example of waiting 10 seconds to acquire the lock.

gSQL> LOCK TABLE t1 IN EXCLUSIVE MODE WAIT 10;

Table locked.

Compatibility

The SQL standard does not define the concept of a lock table.

For More Information

Refer to the following.

MERGE

Function

It inserts, updates, or deletes the record that meets the condition in the target table.

Syntax

<merge statement> ::=
    MERGE [ <hint clause> ] INTO <target table> [ [ AS ] <target alias> ]
    USING <source relation>
    ON <merge join condition>
    <merge operation specification>
    ;

<target table> ::=
    <table name>

<target alias> ::=
    <correlation name>
    
<source relation> ::=
    {
        <table name> [ [ AS ] <source alias> ]
      | <table subquery> [ [ AS ] <source alias> ]    
    }

<source alias> ::=
    <correlation name>

<merge join condition> ::=
    <search condition>
    
<merge operation specification> ::=
    <merge when clause> [...]

<merge when clause> ::=
      <merge when matched clause>
    | <merge when not matched clause>

<merge when matched clause> ::=
    WHEN MATCHED [ AND <search condition> ] 
        THEN { <merge update> | <merge delete> | <merge do nothing> }

<merge when not matched clause> ::=
    WHEN NOT MATCHED [ AND <search condition> ] 
        THEN { <merge insert> | <merge do nothing> }

<merge update> ::=
    UPDATE SET
    {
        <column name> = { <value expression> | DEFAULT }
      | <left paren> <column name> [, ...] <right paren>
        = <left paren> { <value expression> | DEFAULT } [, ...] <right paren>
    } [, ...]

<merge delete> ::=
    DELETE

<merge insert> ::=
    INSERT
    [ <left paren> <column name> [, ...] <right paren> ]
    {
        VALUES <left paren> <merge insert value element> [, ...] <right paren>
      | DEFAULT VALUES
    }

<merge do nothing> ::=
    DO NOTHING

<merge insert value element> ::=
      <value expression>
    | DEFAULT

Invocation and Access Rules

There is no separate privilege specifically for the MERGE statement.

The user must satisfy the following conditions to execute a <merge statement>.

Syntax Rules and Parameters

<hint clause>

It specifies a hint required to perform the query that retrieves the join result of the <target table> and <source relation>.
For more information, refer to SQL Hint.

<target table>

It specifies the table to be modified.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used.
The target table can be either a regular table or a temporary table.

<target alias>

It is an alternative name (alias) for the <target table>.

<source relation>

It is the source relation, which provides the rows to be merged into the target table (<target table>).

<source alias>

It is an alternative name (alias) for the <source relation>.

<merge join condition>

It specifies the join condition between the <target table> and the <source relation>.
The <search condition> may include columns from both the <target table> and the <source relation>.

<merge operation specification>

It describes one or more <merge when clause>.

<merge when clause>

A <merge when matched clause> without a <search condition> can be specified only once.
If a <merge when matched clause> without a <search condition> is specified, no additional <merge when matched clause> may be defined afterward.
gSQL> 
MERGE INTO t1
USING t2
ON t1.c1 = t2.c1
WHEN MATCHED THEN UPDATE SET ( c1, c2 ) = ( t2.c1, t2.c2 )
WHEN MATCHED AND t1.c1 = 100 THEN DO NOTHING;

ERR-42000(16614): unreachable WHEN clause specified after unconditional WHEN clause : 
WHEN MATCHED AND t1.c1 = 100 THEN DO NOTHING
*
ERROR at line 5:
A <merge when not matched clause> without a <search condition> can be specified only once.
If a <merge when not matched clause> without a <search condition> is specified, no additional <merge when not matched clause> may be defined afterward.
gSQL> 
MERGE INTO t1
USING t2
ON t1.c1 = t2.c1
WHEN NOT MATCHED THEN INSERT VALUES ( c1, c2 )
WHEN NOT MATCHED AND c1 = 4 THEN INSERT DEFAULT VALUES;

ERR-42000(16614): unreachable WHEN clause specified after unconditional WHEN clause : 
WHEN NOT MATCHED AND c1 = 4 THEN INSERT DEFAULT VALUES
*
ERROR at line 5:

<merge when matched clause>

The <merge when matched clause> is evaluated against the join result of the <target table> and the <source relation>.
The <merge when matched clause> is executed for records in the <target table> that meet one of the following conditions:
• The <search condition> in the <merge when matched clause> evaluates to true
• The <merge when matched clause> has no <search condition>
The <search condition> of the <merge when matched clause> may reference columns from both the <target table> and the <source relation>.
The <merge when matched clause> performs one of the following actions:
• <merge update>: Updates the matched candidate record
• <merge delete>: Deletes the matched candidate record
• <merge do nothing>: Takes no action on the matched candidate record
After a record is selected as a matched candidate, it is no longer eligible for evaluation by any subsequent <merge when matched clause>.

<merge when not matched clause>

The <merge when not matched clause> is evaluated for records in the <source relation> that do not satisfy the join condition with the <target table>.
The <merge when not matched clause> is executed for records in the <source relation> that meet one of the following conditions:
• The <search condition> in the <merge when not matched clause> evaluates to true
• The <merge when not matched clause> has no <search condition>
The <search condition> of the <merge when not matched clause> may reference only columns from the <source relation>.
The <merge when not matched clause> performs one of the following actions:
• <merge insert>: Inserts a new record into the <target table>
• <merge do nothing>: Takes no action on the matched candidate record
After a record is selected as a matched candidate, it is no longer eligible for evaluation by any subsequent <merge when not matched clause>.

<merge update>

It updates the matched candidate records selected by the <merge when matched clause>.

Only the <set clause> of the UPDATE statement can be specified.

For more information, refer to the <set clause> of the UPDATE statement.

In the <set clause>, only columns from the <target table> may be specified in the <column name>, while the <value expression> may reference columns from both the <target table> and the <source relation>.

<merge delete>

It deletes the matched candidate records selected by the <merge when matched clause>.

<merge do nothing>

It takes no action on the matched candidate record selected by either the <merge when matched clause> or the <merge when not matched clause>.

<merge insert>

If there are matched candidate records selected by the <merge when not matched clause>, specifies the records to be inserted into the <target table>.
The <value expression> in the <merge insert value element> may reference columns from the <source relation>.

Description

The MERGE statement is a single SQL statement that conditionally performs an INSERT, UPDATE, or DELETE operation.
Its execution yields the same result as executing separate INSERT, UPDATE, or DELETE statements.
In the MERGE statement, the INSERT, UPDATE, and DELETE operations do not include clauses for specifying the target table, nor do they support WHERE, OFFSET, or LIMIT clauses.
The MERGE statement uses the join result of the <target table> and the <source relation> to perform INSERT, UPDATE, or DELETE operations on the <target table> based on specified conditions.

The execution proceeds in the following order:

  1. Candidate records are identified based on the join result between the <target table> and the <source relation>.

  2. For each candidate record, a status of either MATCHED or NOT MATCHED is determined.

    1. MATCHED

      1. A record from the join result that satisfies the join condition between the <target table> and the <source relation>.

    2. NOT MATCHED

      1. A record from the <source relation> that does not satisfy the join condition with the <target table>.

  3. Each candidate record, once classified as MATCHED or NOT MATCHED, is evaluated in the order of the specified WHEN clauses.

    1. For each candidate record, the first WHEN clause that evaluates to TRUE is executed.

      1. The <search condition> evaluates to TRUE

      2. The WHEN clause does not have a <search condition>

  4. No more than one WHEN clause is executed for each candidate record.

    1. Candidate records that have been processed in step 3 are excluded from evaluation by any subsequent WHEN clauses.

Example of MERGE execution process

### table information

gSQL> 
SELECT * FROM t_target ORDER BY c1, c2;
C1 C2
-- --
 2  2
 4  4
 6  6
 8  8
4 rows selected.

gSQL>  
SELECT * FROM t_source ORDER BY c1, c2;
C1 C2
-- --
 2  1
 4  2
 6  3
 8  4
10  5
12  6
14  7
7 rows selected.
### MERGE statement

MERGE INTO t_target
USING t_source
ON t_target.c1 = t_source.c1
WHEN MATCHED AND t_target.c1 = 4 THEN DELETE
WHEN MATCHED AND t_target.c1 = 2 THEN DO NOTHING
WHEN MATCHED THEN UPDATE SET c1 = t_target.c1 + 100
WHEN NOT MATCHED AND t_source.c1 = 14 THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES ( t_source.c1, t_source.c1 );
### Candidate records are determined based on the join result of the <target table> and the <source relation>.

t_target.c1 t_target.c2 t_source.c1 t_source.c2
----------- ----------- ----------- -----------
          2           2           2           1  <-- MATCHED
          4           4           4           2  <-- MATCHED
          6           6           6           3  <-- MATCHED
          8           8           8           4  <-- MATCHED
       null        null          10           5  <-- NOT MATCHED
       null        null          12           6  <-- NOT MATCHED
       null        null          14           7  <-- NOT MATCHED
### Records classified as MATCHED or NOT MATCHED are evaluated in the order of the specified WHEN clauses.

WHEN MATCHED AND t_target.c1 = 4 THEN DELETE                      1
WHEN MATCHED AND t_target.c1 = 2 THEN DO NOTHING                  2
WHEN MATCHED THEN UPDATE SET c1 = t_target.c1 + 100               3
WHEN NOT MATCHED AND t_source.c1 = 14 THEN DO NOTHING             4
WHEN NOT MATCHED THEN INSERT VALUES ( t_source.c1, t_source.c1 ); 5

t_target.c1 t_target.c2 t_source.c1 t_source.c2
----------- ----------- ----------- -----------
          2           2           2           1  <-- MATCHED     2 DO NOTHING
          4           4           4           2  <-- MATCHED     1 DELETE
          6 (106)     6           6           3  <-- MATCHED     3 UPDATE 
          8 (108)     8           8           4  <-- MATCHED     3 UPDATE
       null (10)   null (10)     10           5  <-- NOT MATCHED 5 INSERT
       null (12)   null (12)     12           6  <-- NOT MATCHED 5 INSERT
       null        null          14           7  <-- NOT MATCHED 4 DO NOTHING
### Result of MERGE execution 

gSQL> 
MERGE INTO t_target
USING t_source
ON t_target.c1 = t_source.c1
WHEN MATCHED AND t_target.c1 = 4 THEN DELETE
WHEN MATCHED AND t_target.c1 = 2 THEN DO NOTHING
WHEN MATCHED THEN UPDATE SET c1 = t_target.c1 + 100
WHEN NOT MATCHED AND t_source.c1 = 14 THEN DO NOTHING
WHEN NOT MATCHED THEN INSERT VALUES ( t_source.c1, t_source.c1 );
5 rows merged.

gSQL> 
SELECT * FROM t_target ORDER BY c2, c1;
 C1 C2
--- --
  2  2
106  6
108  8
 10 10
 12 12
5 rows selected.

Examples

The following is an example query that reflects changes such as department transfers of employees in the employee table.

DROP TABLE employee;
CREATE TABLE employee ( id              INTEGER,
                        department_id   INTEGER,
                        name            VARCHAR( 10 ) );

INSERT INTO employee VALUES ( 1, 10, 'KIM' );
INSERT INTO employee VALUES ( 2, 10, 'LEE' );
INSERT INTO employee VALUES ( 3, 20, 'PARK' );
INSERT INTO employee VALUES ( 4, 20, 'JUNG' );
INSERT INTO employee VALUES ( 5, 30, 'SONG' );
COMMIT;

DROP TABLE dep_transfer;
CREATE TABLE dep_transfer( emp_id              INTEGER,
                           curr_department_id  INTEGER,
                           new_department_id   INTEGER,
                           name                VARCHAR( 10 ),
                           is_retire           BOOLEAN );

INSERT INTO dep_transfer VALUES ( 1,   10,   30,   'KIM', FALSE );
INSERT INTO dep_transfer VALUES ( 3,   20,   30,  'PARK', FALSE );
INSERT INTO dep_transfer VALUES ( 4,   20, NULL,  'JUNG', TRUE );
INSERT INTO dep_transfer VALUES ( 5,   30,   10,  'SONG', FALSE );
INSERT INTO dep_transfer VALUES ( 6, NULL,   10, 'HWANG', FALSE );
COMMIT;

gSQL> 
MERGE INTO employee
USING dep_transfer
ON employee.id = dep_transfer.emp_id
WHEN MATCHED AND is_retire = TRUE THEN DELETE
WHEN MATCHED THEN UPDATE SET department_id = new_department_id
WHEN NOT MATCHED THEN INSERT VALUES ( emp_id, new_department_id, name );
5 rows merged.

gSQL> 
SELECT * FROM employee;      
ID DEPARTMENT_ID NAME 
-- ------------- -----
 1            30 KIM  
 2            10 LEE  
 3            30 PARK 
 5            10 SONG 
 6            10 HWANG
5 rows selected.

Compatibility

The SQL standard does not define a DO NOTHING clause in the MERGE statement.

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

S024

Enhanced structured types

X

F312

MERGE statement

O

F313

Enhanced MERGE statement

O

F314

MERGE statement with DELETE branch

O

For More Information

Refer to the following.

NOAUDIT POLICY

Function

It disables an audit policy.

Syntax

<noaudit policy statement> ::= 
    NOAUDIT POLICY policy_name
    [ <specified_user_option> ]
    ;

<specified_user_option> ::=
      BY user_name [, ...]

Invocation and Access Rules

The AUDIT SYSTEM ON DATABASE privilege is required to execute the <noaudit policy statement>.

Syntax Rules and Parameters

policy_name

It is the name of the audit policy object to be disabled.
A disabled audit policy does not affect existing sessions; it only applies to sessions created afterward.

<specified_user_option>

It specifies the users to be excluded from auditing.

Unlike the AUDIT POLICY statement, the NOAUDIT POLICY statement does not support the EXCEPT clause.

If the AUDIT POLICY name BY clause was used for activation, it must be deactivated using the NOAUDIT POLICY name BY statement.
If the AUDIT POLICY name EXCEPT clause was used, it must be deactivated using the NOAUDIT POLICY name statement without the BY clause.

Depending on how the AUDIT POLICY statement was used, the corresponding NOAUDIT POLICY statement should be used to deactivate it, as shown below:

Activating/ deactivating audit policy

Type

AUDIT POLICY statement

NOAUDIT POLICY statement

All users

AUDIT POLICY p1

NOAUDIT POLICY p1

Using BY

AUDIT POLICY p1 BY u1

NOAUDIT POLICY p1 BY u1

Using EXCEPT

AUDIT POLICY p1 EXCEPT u1

NOAUDIT POLICY p1

When all activated users are deactivated, the audit policy object becomes fully deactivated.

Description

The activation information of an audit policy object can be queried as follows.

SELECT policy_name
     , enabled_opt
     , user_name
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';
The NOAUDIT POLICY statement removes individual activation entries depending on how the AUDIT POLICY was defined.

If no activation entries are found through the above query, the audit policy is considered fully deactivated.

If the audit policy was activated for all users, the NOAUDIT POLICY BY clause has no effect.

AUDIT POLICY p1;
NOAUDIT POLICY p1 BY u1;
NOAUDIT POLICY p1;

If one or more users were individually activated, the NOAUDIT POLICY statement must be used appropriately according to the method used in the AUDIT POLICY definition.

When Activated by Using BY

If the audit policy is activated as follows:

AUDIT POLICY p1 WHENEVER NOT SUCCESSFUL;
AUDIT POLICY p1 BY u1;
AUDIT POLICY p1 BY u2;

The activation information appears as shown below.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS  WHEN_FAILURE
-----------  -----------  ---------    ------------  ------------
P1           BY           ALL USERS    NO            YES
P1           BY           U1           YES           YES
P1           BY           U2           YES           YES

The following is an example of executing the NOAUDIT POLICY statement and checking the activation information.

NOAUDIT POLICY p1;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS    WHEN_FAILURE
-----------  -----------  ---------    ------------    ------------
P1           BY           U1           YES             YES
P1           BY           U2           YES             YES
Auditing for failures on ALL USERS has been deactivated, while auditing for users u1 and u2 remains active.

By additionally using the NOAUDIT POLICY statement with the BY option as shown below, the audit policy p1 becomes fully deactivated:

NOAUDIT POLICY p1 BY u1, u2;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

no rows selected.

When Activated by Using EXCEPT

If the audit policy is activated as follows:

AUDIT POLICY p1 EXCEPT u1, sys;

The activation information appears as shown below.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

POLICY_NAME  ENABLED_OPT  USER_NAME    WHEN_SUCCESS    WHEN_FAILURE
-----------  -----------  ---------    ------------    ------------
P1           EXCEPT       U1           YES             YES
P1           EXCEPT       SYS          YES             YES
Unlike the AUDIT POLICY statement, the NOAUDIT POLICY statement does not support the EXCEPT option, so it must be executed without any options, as shown below.
NOAUDIT POLICY p1;

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
 WHERE policy_name = 'P1';

no rows selected.
In other words, if the audit policy was activated using the EXCEPT option, it is not possible to individually deactivate users using the NOAUDIT POLICY statement.

Examples

The following is an example of deactivating all users.

NOAUDIT POLICY table_pol;

The following is an example of deactivating specific users who were activated using the BY clause.

NOAUDIT POLICY table_pol BY u1;

Compatibility

The SQL standard does not define audit policies.

For More Information

Refer to the following.

OPEN cursor_name

Function

It opens a cursor.

Syntax

<open statement> ::=
    OPEN cursor_name [ <parameter using clause> ]
    ;

<parameter using clause> ::=
      <using parameter arguments>

<using parameter arguments> ::=
    USING variable_name [, ...]

Invocation and Access Rules

If cursor_name is a dynamic cursor declared using the PREPARE statement_name and DECLARE cursor_name statements, it can be used in embedded SQL.
It inherits the same privileges as the <cursor query> specified in the DECLARE cursor_name statement that declared the cursor_name.

Syntax Rules and Parameters

cursor_name

It should be a cursor declared with DECLARE cursor_name within the session.

<parameter using clause>

It can be used in embedded SQL.
When <parameter using clause> is used, cursor_name should be a dynamic cursor declared by using PREPARE statement_name and DECLARE cursor_name.

<using parameter arguments>

When <using parameter arguments> is used, the number of variable_name should be equal to the number of the parameter included in a query which is referenced by PREPARE statement_name.
The listed variable_name corresponds to the dynamic parameter in an order of its description.
{
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT c1, c2 FROM t1 WHERE c1 IN ( ?, ?, ? )';
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
    EXEC SQL OPEN cur1 USING :sValue1, :sValue2, :sValue3;
    ...
    EXEC SQL WHENEVER NOT FOUND DO break;
    for(;;)
    {
        EXEC SQL FETCH cur1 INTO :sC1, :sC2;    
    }
    EXEC SQL WHENEVER NOT FOUND CONTINUE;
    ...
    EXEC SQL CLOSE cur1;    
    ... 
}

Description

The cursor is a distinguishable object in a session. The cursor being used in the current session has nothing to do with the cursor being used in another session.
To use OPEN cursor_name statement, it should be a cursor declared with DECLARE cursor_name, and it should be a closed cursor.

Examples

The following is an example of declaring a cursor and using OPEN cursor statement in an interactive SQL (gsql).

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur1;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

For More Information

Refer to the following.

PREPARE statement_name

Function

It prepares a dynamic SQL statement for a repeated execution.

Syntax

<prepare statement> ::=
    PREPARE statement_name FROM <SQL statement variable>
    ;

<SQL statement variable> ::=
      variable_name
    | 'sql statement'
    | "sql statement"
    | sql statement

Invocation and Access Rules

It can be used in embedded SQL.
An appropriate privilege according to the type of a dynamic SQL statement is required.

Syntax Rules and Parameters

statement_name

It is the name of the statement to be prepared.
The length of the statement name should be shorter than 128 bytes.
EXECUTE statement_name and DECLARE cursor_name, which are to be performed later, refers to the statement_name.
If the same statement_name exists, the previously prepared dynamic SQL is dropped.
{
    ...

    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1';
    ...
    EXEC SQL PREPARE stmt1 FROM 'UPDATE t1 SET c1 = c1 + 10';
    ...
}

<SQL statement variable>

<SQL statement variable> can be used as following four types.

The single quote (') is used twice as follows to represent string data within single-quoted string.

{
    ...
    PREPARE stmt_name FROM 'INSERT INTO t1 VALUES ( ''literal data'' )'; 
    ...
}
The dynamic SQL statement referenced by <SQL statement variable> can use a host variable (:var) or parameter marker (?). 
However, if the unquoted SQL statement is used, the parameter marker (?) can not be used.
Depending on the characteristics of the referenced dynamic SQL statements, the variable can be either input or output dynamic parameter. 
The dynamic parameter described in the dynamic SQL statement does not have a meaning for the variable name, and it is identified by the specified order regardless of its type.
{
    ...
    int sValue1;
    int sValue2;
    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 BETWEEN ? AND ?';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2;   
    ...
}
{
    ...
    int sValue1;
    int sValue2;
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT SUM(c2) INTO :v1 FROM t1 WHERE c1 > :v2';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2;
    ...
}

variable_name

The type corresponding to variable_name should be a character string.
The dynamic SQL statement defined in variable_name should be valid.

sql statement

The dynamic SQL statement defined in the sql statement should be valid.

Description

PREPARE statement_name FROM sql_string statement analyzes SQL statement to use EXECUTE or cursor statement. Statement_name is an identifier which informs the precompiler the statement in an embedded SQL source code. A separate type or declaration is not required because statement_name is not a host variable.
For more information, refer to Embedded Dynamic SQL.

Example

The following is an example of using PREPARE statement_name in an embedded SQL source code.

{
    ...
    sprintf( sUpdateSql, "UPDATE EMP SET sal = sal * :v1 WHERE JOB = 'SALES'");
    EXEC SQL PREPARE UPDATE_STMT FROM :sUpdateSql;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }

    sRatio = 1.1;
    EXEC SQL EXECUTE UPDATE_STMT USING :sRatio;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }
    ...
}

The full source code in which PREPARE statement_name was used can be viewed in Dynamic Embedded SQL Example Program.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

B034

Dynamic specification of cursor attributes

X

For More Information

Refer to the following.

PURGE

Function

It permanently drops objects stored in the recycle bin.

Syntax

<purge statement> :==
    PURGE <purge action>
    ;

<purge action> :==
     TABLE table_name
   | INDEX index_name
   | CONSTRAINT constraint_name
   | TRIGGER trigger_name  
   | TABLESPACE tablespace_name [ USER user_name ]
   | RECYCLEBIN 
   | USER_RECYCLEBIN
   | DBA_RECYCLEBIN

Invocation and Access Rules

One of the following privileges is required for a user to perform <purge statement>.

Syntax Rules and Parameters

table_name

It is the name of the object stored or of the dropped table in the recycle bin.
It can define the schema to which the table belongs in the dropped table name, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used. In this case, indexes and constraints which are related to the table are also dropped.

index_name

It is the name of the object stored or of the dropped index in the recycle bin. 
It can define the schema to which the index belongs such as schema_name.index_name. 
If schema_name is omitted, the default schema name of the user performing the statement is used. 
The key index which is created with a constraint should be dropped with the constraint.

constraint_name

It is the name of the object stored or of the dropped constraint in the recycle bin.

trigger_name

It is the name of an object stored in the recycle bin or the name of a deleted trigger.

tablespace_name

It is the name of the tablespace. 
When assigning USER, DROP ANY TABLE ON DATABASE privilege is required.

user_name

It is the name of the user.

recyclebin

It is the alias of user_recyclebin.

user_recyclebin

It drops all recycle bins owned by a user.

dba_recyclebin

It drops all recycle bins in the database. 
PURGE DBA_RECYCLEBIN ON DATABASE privilege is required.

Description

It permanently drops objects stored in the recycle bin by using the object name or the dropped table name stored in the recycle bin. If the name which is the same as that of the dropped table exists, then the oldest table object is dropped.
When specifying a tablespace in the recycle bin object owned by a user, then only the objects included in the tablespace are dropped. In this case, if a user is assigned, then only the objects included in the specified tablespace owned by the user are dropped.
PURGE TABLE, INDEX, CONSTRAINT statements  can be rolled back if it is before when the transaction is committed. However, PURGE TABLESPACE, RECYCLEBIN, DBA_RECYCLEBIN statements can not be rolled back, and the transaction which performed the statement is automatically committed.

Example

The following is an example of dropping a table stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$135B9926166111EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE TABLE t1;

Table purged.

The following is an example of dropping an index stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$135B9926166111EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE INDEX t1_idx1;

Index purged.

The following is an example of dropping constraints stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$135B9908166111EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$135B993A166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$135B991C166111EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      

3 rows selected.

gSQL> PURGE CONSTRAINT t1_primary_key;

Constraints purged.

The following is an example of dropping objects included in the tablespace stored in the recycle bin.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE, TABLESPACE_NAME FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE TABLESPACE_NAME
------------------------------------ ------------- ----------- ---------------
BIN$02C76B24166311EA9C5C835D3E4BBBF7 T1            TABLE       MEM_DATA_TBS   

1 row selected.

gSQL> PURGE TABLESPACE MEM_DATA_TBS;

Tablespace purged.

The following is an example of dropping all recycle bins owned by a user.

gSQL> SELECT OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
------------------------------------ -------------------- -----------
BIN$64F6BFFC166311EA9C5C835D3E4BBBF7 T1                   TABLE      
BIN$64F6C042166311EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY       CONSTRAINT 
BIN$64F6C010166311EA9C5C835D3E4BBBF7 T1_PRIMARY_KEY_INDEX INDEX      
BIN$64F6C024166311EA9C5C835D3E4BBBF7 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE USER_RECYCLEBIN;

Recyclebin purged.

The following is an example of dropping all recycle bins in the system.

gSQL> SELECT OWNER, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

OWNER OBJECT_NAME                          ORIGINAL_NAME        OBJECT_TYPE
----- ------------------------------------ -------------------- -----------
TEST  BIN$F0FB26F0166311EAA7C5D51B86D72AB6 T1                   TABLE      
TEST  BIN$F0FB272C166311EAA7C5D51B86D72AB6 T1_PRIMARY_KEY       CONSTRAINT 
TEST  BIN$F0FB2704166311EAA7C5D51B86D72AB6 T1_PRIMARY_KEY_INDEX INDEX      
TEST  BIN$F0FB2718166311EAA7C5D51B86D72AB6 T1_IDX1              INDEX      

4 rows selected.

gSQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Compatibility

The SQL standard does not define the <purge statement>.

For More Information

Refer to the following.

RELEASE SAVEPOINT savepoint_specifier

Function

It releases a savepoint.

Syntax

<release savepoint statement> ::=
    RELEASE SAVEPOINT savepoint_name 
    ;

Syntax Rules and Parameters

savepoint_name

It is a name of the savepoint, and it should exist. 
The length of the savepoint name should be shorter than 128 bytes.

Description

If multiple savepoints are defined and RELEASE SAVEPOINT savepoint_name statement is performed, all savepoints defined since the savepoint_name are also released.

Example

The following is an example of releasing a savepoint.

gSQL> RELEASE SAVEPOINT sp2;

Savepoint dropped.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T271

Savepoints

O

For More Information

Refer to the following.

REVOKE privileges FROM

Function

It revokes the granted privilege from a user or a role.

Syntax

<revoke privilege statement> ::=
    REVOKE [ <revoke option extention> ] <privilege>
      FROM <grantee> [, ...]
      [ <revoke behavior> ]
    ;

<revoke option extention> ::=
      GRANT OPTION FOR

<grantee> ::=
      PUBLIC
    | <user_identifier>
    | <role_name>

<revoke behavior> ::=
      RESTRICT
    | CASCADE
    | CASCADE CONSTRAINTS

Syntax Rules and Parameters

<privilege>

It is a privilege which is to be revoked from the revokee (the user or the role whose privilege is to be revoked).
The revoker (the user who performs the statement) should satisfy one of the following conditions.
When using ALL [PRIVILEGES], it succeeds even when the satisfying <privilege> does not exist.
For more information about the types of <privilege>, refer to <privilege> clause of GRANT privileges TO statement.

<grantee>

It is a user or a role whose privilege is to be revoked.

GRANT OPTION FOR

It revokes WITH GRANT OPTION included in the privilege. 
It also revokes WITH GRANT OPTION of the dependent privilege.
The privilege is maintained.

<revoke behavior>

Description

Data Definition Language (DDL) such as REVOKE privilege can be rolled back if it is before when the transaction is committed.
When performing the following DROP statement, all privilege information related to the object is revoked even without performing any separate REVOKE statement.

Examples

The following is an example of revoking multiple privileges for the table t1.

gSQL> REVOKE INSERT, UPDATE, DELETE, LOCK, ALTER, INDEX ON t1 FROM u1;

Revoke succeeded.

The following is an example of revoking SELECT ON TABLE t1 privilege granted to the PUBLIC account, which means all authorizations (users and roles). However, only the privilege for PUBLIC account is revoked, and SELECT ON TABLE t1 privilege which was explicitly granted to a specific user or a role is not revoked.

gSQL> REVOKE SELECT ON t1 FROM PUBLIC;

Revoke succeeded.

The following is an example that SELECT ON TABLE t1 privilege granted to user u1 is remained, and only REVOKE GRANT OPTION which can grant the privilege to another user is revoked.

gSQL> REVOKE GRANT OPTION FOR SELECT ON t1 FROM u1;

Revoke succeeded.

The following is an example that an error occurs when the privilege granted to the user u1 is revoked by using RESTRICT option and the user u1 grants it to another user. CASCADE option is used to revoke these dependent privileges as well.

gSQL> REVOKE SELECT ON t1 FROM u1 RESTRICT;

ERR-2B000(16235): dependent privilege descriptors still exist

gSQL> REVOKE SELECT ON t1 FROM u1 CASCADE;

Revoke succeeded.

The following is an example of REVOKing multiple privileges for table t1 from role1.

gSQL> REVOKE INSERT, UPDATE, DELETE, LOCK, ALTER, INDEX ON t1 FROM role1;

Revoke succeeded.

Compatibility

The SQL standard does not define the following privileges.

<revoke behavior> of the SQL standard has the following differences.

SQL standard compatibility

Feature ID

Description

Compatibility

T331

Basic roles

O

T332

Extended roles

X

F034

Extended REVOKE statement

O

S081

Subtables

X

For More Information

Refer to the following.

REVOKE role FROM

Function

It revokes the granted role from another user or a role.

Syntax

<revoke role statement> ::=
     REVOKE [ ADMIN OPTION FOR ] <role revoked> [ , ...... ] 
            FROM <grantee> [ , ...... ]
     ;

<grantee> ::=
      PUBLIC
    | <user_identifier>
    | <role_name>

<role revoked> ::=
     <role_name>

Invocation and Access Rules

One of the following conditions should be satisfied to perform <revoke role statement>.

Syntax Rules and Parameters

<role revoked>

It is a name of the role whose role is to be revoked.

<grantee>

It is a user or a role whose role is to be revoked.

ADMIN OPTION FOR

It drops WITH ADMIN OPTION for the role.
The granted role is maintained.

Description

It revokes the role from another user or a role.
Data Definition Language (DDL) such as REVOKE role can be rolled back if it is before when the transaction is committed.
When performing DROP ROLE, all granted role information is deleted even without performing any separate REVOKE role statement.

Examples

The following is an example of a user with GRANT ROLE ON DATABASE privilege revoking the role.

gSQL> GRANT GRANT ROLE ON DATABASE TO u1;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 
U1      GRANT ROLE ON DATABASE     

2 rows selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE ADMIN_OPTION
------- ------------ ------------
ROLE2   ROLE1        NO          

1 row selected.

gSQL> \connect u1 u1

gSQL> REVOKE role1 FROM role2;

Revoke succeeded.

The following is an example of a user with WITH ADMIN OPTION for the role, revoking the role.

gSQL> GRANT role1 TO u1 WITH ADMIN OPTION;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 

1 row selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE ADMIN_OPTION
------- ------------ ------------
ROLE2   ROLE1        NO          
U1      ROLE1        YES         

2 rows selected.


gSQL> \connect u1 u1

gSQL> REVOKE role1 FROM role2;

Revoke succeeded.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T331

Basic roles

O

T332

Extended roles

X

F034

Extended REVOKE statement

O

S081

Subtables

X

For More Information

Refer to the following.

ROLLBACK

Function

It rolls back a transaction, or the operation after the savepoint.

Syntax

<rollback statement> ::=
    ROLLBACK [ WORK ] [ <rollback force clause> | <savepoint clause> ]
    ;

<rollback force clause> ::=
    FORCE 'xid_string' [ COMMENT 'comment_string' ]

<savepoint clause> ::=
    TO SAVEPOINT savepoint_name

Syntax Rules and Parameters

WORK

It is a reserved word which does not affect the operation.

<rollback force clause>

It is used to manually rollback the distributed transaction.

<savepoint clause>

It specifies the rollback scope of the current transaction.

Description

ROLLBACK statement undoes the following statements performed in the transaction.

Exceptionally, the following statements of DDL which deals with OS resources or alters the DATA TYPE can not be rolled back, but are automatically committed when executing the statement.

Examples

The following is an example of rolling back the INSERT statement.

gSQL> INSERT INTO t1 VALUES ( 1, 'anonymous' );

1 row created.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

gSQL> ROLLBACK;

Rollback complete.

gSQL> SELECT * FROM t1;

no rows selected.

The following is an example of ROLLBACK after performing the DROP TABLE statement.

gSQL> DROP TABLE t1;

Table dropped.

gSQL> SELECT * FROM t1;

ERR-42000(16040): table or view does not exist : 
SELECT * FROM t1
              *
ERROR at line 1:

gSQL> ROLLBACK;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T271

Savepoints

O

T261

Chained transactions

X

For More Information

Refer to the following.

SAVEPOINT savepoint_specifier

Function

It defines a savepoint.

Syntax

<savepoint statement> ::=
    SAVEPOINT savepoint_name 
    ;

Syntax Rules and Parameters

savepoint_name

It is a name of the savepoint.
If the savepoint name is the same as the existing savepoint name, then the existing savepoint is deleted.
The length of the savepoint name should be shorter than 128 bytes.

Description

The defined savepoint is used by ROLLBACK TO SAVEPOINT statement (refer to ROLLBACK.), and DML or DDL statement which has been performed up to the savepoint is rolled back. Then the locks acquired by using that statement are released, too.

The defined savepoint is automatically deleted when the transaction is committed or rolled back, or it can be explicitly deleted by using RELEASE SAVEPOINT savepoint_specifier.

Example

The following is an example of defining the savepoint and using ROLLBACK TO SAVEPOINT statement.

gSQL> SAVEPOINT sp1;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 1, 'anonymous' );

1 row created.

gSQL> SAVEPOINT sp2;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 2, 'someone' );

1 row created.

gSQL> SAVEPOINT sp3;

Savepoint created.

gSQL> INSERT INTO t1 VALUES ( 3, 'anyone' );

1 row created.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous
 2 someone  
 3 anyone   

3 rows selected.

gSQL> ROLLBACK TO SAVEPOINT sp3;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous
 2 someone  

2 rows selected.

gSQL> ROLLBACK TO SAVEPOINT sp2;

Rollback complete.

gSQL> SELECT * FROM t1;

ID DATA     
-- ---------
 1 anonymous

1 row selected.

gSQL> ROLLBACK TO SAVEPOINT sp1;

Rollback complete.

gSQL> SELECT * FROM t1;

no rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T271

Savepoints

O

For More Information

Refer to the following.

SELECT

query expression

Function

It retrieves desired rows from one or more tables or views.

Syntax

<query expression> ::=
    [ <with clause> ] <query expression body> [ <order by clause> ] [ <offset limit clause> ]

<query expression body> ::=
      <query term>
    | <set operator>

<query term> ::=
      <query specification>
    | <left paren> <query expression body> [ <order by clause> ] [ <offset limit clause> ] <right paren>

Invocation and Access Rules

One of the following privileges for all tables used in the statement is required for a user to perform <query expression>.

Syntax Rules and Parameters

<with clause>

<with clause> defines the temporary result set, and it refers to that result set. 
For more information, refer to with clause.

<set operator>

It performs a set operation among the subqueries.
For more information, refer to set operator.

<query specification>

It specifies a single subquery. 
For more information, refer to <query specification>.

<order by clause>

It specifies sorting information of a query result. 
For more information, refer to order by clause.

<offset limit clause>

It specifies the number of rows to skip and the number of rows to fetch from the query result set. 
For more information, refer to offset limit clause.

Description

It specifies a query with SELECT statement. 
<with clause>, <order by clause>, <offset limit clause> can be omitted.
Two or more subqueries can be specified by using <set operator>.

Examples

The following is an example of SELECT statement.

gSQL> SELECT s_name, s_nation FROM supplier;

S_NAME                    S_NATION     
------------------------- -------------
Supplier#1                FRANCE       
Supplier#2                KOREA        
Supplier#3                GERMANY      
Supplier#4                UNITED STATES
Supplier#5                CANADA       

5 rows selected.

The following is an example of the SELECT statement which uses <order by clause>.

gSQL> SELECT s_name, s_nation FROM supplier ORDER BY s_name DESC;

S_NAME                    S_NATION
------------------------- -------------
Supplier#5                CANADA
Supplier#4                UNITED STATES
Supplier#3                GERMANY
Supplier#2                KOREA
Supplier#1                FRANCE

5 rows selected.

The following is an example of the SELECT statement which uses <offset limit clause>.

gSQL> SELECT s_name, s_nation FROM supplier OFFSET 1;

S_NAME                    S_NATION
------------------------- -------------
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

4 rows selected.

gSQL> SELECT s_name, s_nation FROM supplier LIMIT 1; 

S_NAME                    S_NATION
------------------------- --------
Supplier#1                FRANCE  

1 row selected.

The following is an example of the SELECT statement which uses <order by clause> and <offset limit clause>.

gSQL> SELECT s_name, s_nation FROM supplier ORDER BY s_name DESC OFFSET 3 LIMIT 1; 

S_NAME                    S_NATION
------------------------- --------
Supplier#2                KOREA   

1 row selected.

The following is an example of SELECT statement which uses <with clause>.

* Non Recursive CTE

gSQL> WITH revenue ( supplier_no, total_revenue ) AS
      (
            SELECT
                   l_suppkey,
                   SUM(l_extendedprice * (1 - l_discount))
              FROM lineitem
             WHERE l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
             GROUP BY
               l_suppkey
      )
    select
        s_suppkey,
        s_name,
        s_address,
        s_phone,
        ROUND( total_revenue, 2 ) as total_revenue
    from
        supplier,
        revenue
    where
          s_suppkey = supplier_no
      and total_revenue = (
                            select
                                max(total_revenue)
                            from
                                revenue
                          )
    order by
       s_suppkey;

S_SUPPKEY S_NAME                    S_ADDRESS         S_PHONE         TOTAL_REVENUE
--------- ------------------------- ----------------- --------------- -------------
     8449 Supplier#000008449        Wp34zim9qYFbVctdW 20-469-856-8873    1772627.21

1 row selected.


* Recursive CTE

gSQL> WITH GenerateRecord ( c1, c2 ) AS 
     (
          SELECT 1, 11
            FROM dual
          UNION ALL
          SELECT c1 + 1, c2 + 1
            FROM GenerateRecord
           WHERE c1 < 10
     )
SELECT c1, c2 FROM GenerateRecord;

C1 C2
-- --
 1 11
 2 12
 3 13
 4 14
 5 15
 6 16
 7 17
 8 18
 9 19
10 20

10 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T121

WITH(excluding RECURSIVE) in query expression

O

T122

WITH(excluding RECURSIVE) in subquery

O

T131

Recursive query

O

T132

Recursive query in subquery

O

F661

Simple tables

O

F302

INTERSECT table operator

O

F301

CORRESPONDING in query expressions

X

T551

Optional key words for default syntax

O

F304

EXCEPT ALL table operator

O

F850

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

O

F851

<order by clause> in subqueries

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

F858

<fetch first clause> in subqueries

O

F860

dynamic <fetch first row count> in <fetch first clause>

X

F861

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

O

F862

<result offset clause> in subqueries

O

F863

Nested <result offset clause> in <query expression>

O

F865

dynamic <offset row count> in <result offset clause>

X

F866

FETCH FIRST clause: PERCENT option

X

F867

FETCH FIRST clause: WITH TIES option

X

with clause

Function

<with clause> defines the temporary result set, and it refers to that result set. 
The result set a temporary result set with a given name, and it is defined and referred in SELECT statement. It is called as Common Table Expression (CTE).

Syntax

<with clause> ::=
    WITH <with list>

<with list> ::=
    <with list element> [ { <comma> <with list element> }... ]

<with list element> ::=
    <query name> [ <left paren> <with column list> <right paren> ] 
        AS <table subquery> [ <search or cycle clause> ]

<with column list> ::=
    <column name list>   
 
<search or cycle clause> ::=
    <search clause>
  | <cycle clause>
  | <search clause> <cycle clause>

<search clause> ::=
    SEARCH <recursive search order> SET <sequence column>

<recursive search order> ::=
    DEPTH FIRST BY <ordering column list>
  | BREADTH FIRST BY <ordering column list>

<ordering column list> ::= 
    <ordering column> [ { <comma> <ordering column> }... ]

<ordering column> ::= 
    <column name> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

<sequence column> ::=
    <column name>

<cycle clause> ::=
    CYCLE <cycle column list> SET <cycle mark column> TO <cycle mark value>
        DEFAULT <non-cycle mark value>

<cycle column list> ::=
    <cycle column> [ { <comma> <cycle column> }... ]

<cycle column> ::=
    <column name>
    
<cycle mark column> ::=
    <column name>
 
<cycle mark value> ::=
    <value expression>

<non-cycle mark value> ::=
    <value expression>

Invocation and Access Rules

It is supported in <query expression> statement, and the user should satisfy the access privilege of  <query expression> to perform it. 
For more information, refer to query expression.

Syntax Rules and Parameters

<with list>

It can define multiple <with list element>.

<with list element>

It defines the temporary result set of specified <query name>.
<with list element> is called as Common Table Expression (CTE).
CTE is divided into a recursive CTE and a non-recursive CTE.
For more information, refer to Description.
--# success : non recursive CTE
WITH CTE_1( c1 ) AS
    (
         SELECT i1
           FROM t1
    ),
    CTE_2( c2 ) AS
    (
         SELECT c1
           FROM CTE_1      1 Referring to the leading CTE
    )
SELECT c2 FROM CTE_2;

--# error : non recursive CTE
WITH CTE_1( c1 ) AS
    (  
         SELECT c2
           FROM CTE_2      2 Referring to the trailing CTE
    ),
    CTE_2( c2 ) AS
    (  
         SELECT i1
           FROM t1
    )
SELECT c1 FROM CTE_1;
--# success : recursive CTE
WITH CTE_1( c1 ) AS
     (  
          SELECT i1
            FROM t1
     ),
     CTE_2( c2 ) AS
     (  
          SELECT c1
            FROM CTE_1          1 Referring to the leading CTE
     ),
     CTE_3( c3 ) AS
     (  
          SELECT 1
            FROM CTE_1
          UNION ALL
          SELECT 1
           FROM CTE_2, CTE_3     2 Referring to the leading CTE or self CTE
     )
SELECT c3 FROM CTE_3;

--# error : recursive CTE
WITH CTE_RECURSIVE( c1 ) AS  
   (  
          SELECT i1
            FROM t1
          WHERE i1 IS NULL
          UNION ALL
          SELECT 1
            FROM CTE_RECURSIVE A, CTE_RECURSIVE B   3 Self-reference CTE is allowed only once
          WHERE 1 = 0
     )
SELECT c1 FROM CTE_RECURSIVE;

<query name>

<query name> should not be duplicated within WITH clause.

<with column list>

Recursive CTE can not omit <with column list>.

<search clause>

<cycle clause>

It stores <cycle mark value> or <non-cycle mark value> in <cycle mark column> according to whether cycle occurs.

Description

<with clause> defines a temporary result set, and it can refer to that result set.  
It is defined and referenced within SELECT statement, and it is a temporary result set with a given name.
It is called as Common Table Expression (CTE).
CTE is classified into recursive CTE and non-recursive CTE.
WITH RECURSIVE_CTE ( c1 ) AS
     (
          SELECT 1 
            FROM dual
          UNION ALL
          SELECT c1 + 1 
            FROM RECURSIVE_CTE
           WHERE c1 < 10
     )
SELECT c1 FROM RECURSIVE_CTE;
WITH NON_RECURSIVE_CTE ( c1 ) AS 
     (
          SELECT i1
            FROM t1
          UNION ALL
          SELECT i1
            FROM t2 
     )
SELECT c1 FROM NON_RECURSIVE_CTE;
<with clause> can be described in  SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, CREATE VIEW statement.

<with list element>

It defines the temporary result set of the described <query name>.
<with list element> is called as Common Table Expression (CTE).
CTE is classified into recursive CTE and non-recursive CTE.
WITH CTE_RECURSIVE( c1, c2 ) AS
    (  
         SELECT i1, i2                          1 Anchor member query
           FROM t1
          WHERE i2 IS NULL
         UNION ALL
         SELECT i1, i2                          2 Recursive member query
           FROM CTE_RECURSIVE, t1      3 Self reference
          WHERE CTE_RECURSIVE.c1 = t1.i2
    )
SELECT c1, c2 FROM CTE_RECURSIVE;

<search clause>

It describes the sort order of CTE result records.
Sibling rows are sorted with <ordering column list>, it specifies the returning order of sibling rows and child rows for the sorted records.
The sequence of result records are stored in <sequence column>.
gSQL>
SELECT * FROM t1;

I1  I2 
--- ---
A   ---
AA  A  
AB  A  
AC  A  
AAX AA 
ABX AB 
ACX AC 

7 rows selected.

* SEARCH BREADTH FIRST BY

gSQL> 
WITH w1( w_i1, w_i2 ) AS
    ( 
         SELECT i1, i2
           FROM t1
          WHERE i1 = 'A'
         UNION ALL
         SELECT i1, i2
           FROM w1, t1
          WHERE w_i1 = i2
    ) SEARCH BREADTH FIRST BY w_i1, w_i2 SET w_seq
SELECT w_i1, w_i2, w_seq
 FROM w1;

W_I1 W_I2 W_SEQ
---- ---- -----
A    ---      1
AA   A        2
AB   A        3
AC   A        4
AAX  AA       5
ABX  AB       6
ACX  AC       7

7 rows selected.

* SEARCH DEPTH FIRST BY

gSQL> 
WITH w1( w_i1, w_i2 ) AS
    ( 
         SELECT i1, i2
           FROM t1
          WHERE i1 = 'A'
         UNION ALL
         SELECT i1, i2
           FROM w1, t1
          WHERE w_i1 = i2
    ) SEARCH DEPTH FIRST BY w_i1, w_i2 SET w_seq
SELECT w_i1, w_i2, w_seq
  FROM w1;

W_I1 W_I2 W_SEQ
---- ---- -----
A    ---      1
AA   A        2
AAX  AA       3
AB   A        4
ABX  AB       5
AC   A        6
ACX  AC       7

7 rows selected.

<cycle clause>

If <cycle clause> statement is not described, then an error occurs when cycle occurs.
<cycle column list> is used to check cycle. 
It stores <cycle mark value> or <non-cycle mark value> in <cycle mark column> according to whether cycle occurs. 
Only 1 byte character can be described in <cycle mark value> or <non-cycle mark value>. 
<cycle mark value> is stored in <cycle mark column> of the record where cycle occurred. In this case, it stops further recursion, and returns only until the record where cycle occurred
The recursion continuously proceeds for sibling rows where cycle does not occurred.
gSQL>
SELECT * FROM t1;

I1  I2 
--- ---
A   ---
AA  A  
AB  A  
AC  A  
AA  AA 
AAX AA 
ABX AB 
ACX AC 

8 rows selected.
gSQL> 
WITH w1( w_i1, w_i2 ) AS
     (     
          SELECT i1, i2
            FROM t1
           WHERE i1 = 'A'
          UNION ALL
          SELECT i1, i2
            FROM w1, t1
           WHERE w_i1 = i2
     )
SELECT w_i1, w_i2
  FROM w1;

ERR-42000(16511): cycle detected while executing recursive WITH query
gSQL> 
WITH w1( w_i1, w_i2 ) AS
     ( 
          SELECT i1, i2
            FROM t1
           WHERE i1 = 'A'
          UNION ALL
          SELECT i1, i2
            FROM w1, t1
           WHERE w_i1 = i2
     ) CYCLE w_i1, w_i2 SET c_cycle TO 'T' DEFAULT 'F'
SELECT w_i1, w_i2, c_cycle
  FROM w1;

W_I1 W_I2 C_CYCLE
---- ---- -------
A    ---  F      
AC   A    F      
AB   A    F      
AA   A    F      
ACX  AC   F      
ABX  AB   F      
AAX  AA   F      
AA   AA   F      
AAX  AA   F      
AA   AA   T      

10 rows selected.

Examples

The following is an example of SELECT statement which uses WITH clause.
gSQL>
WITH revenue ( supplier_no, total_revenue ) AS
      (
            SELECT
                   l_suppkey,
                   SUM(l_extendedprice * (1 - l_discount))
              FROM lineitem
             WHERE l_shipdate >= DATE '1996-01-01'
               AND l_shipdate < DATE '1996-01-01' + INTERVAL '3' MONTH
             GROUP BY
                   l_suppkey
      )
select
       s_suppkey,
       s_name,
       s_address,
       s_phone,
       ROUND( total_revenue, 2 ) as total_revenue
  from
       supplier,
       revenue
 where
       s_suppkey = supplier_no
   and total_revenue = (
                           select
                                  max(total_revenue)
                             from
                                  revenue
                       )
order by
      s_suppkey;

S_SUPPKEY S_NAME                    S_ADDRESS         S_PHONE         TOTAL_REVENUE
--------- ------------------------- ----------------- --------------- -------------
     8449 Supplier#000008449        Wp34zim9qYFbVctdW 20-469-856-8873    1772627.21

1 row selected.
gSQL> 
WITH GenerateRecord ( c1, c2 ) AS 
     (
          SELECT 1, 11
            FROM dual
          UNION ALL
          SELECT c1 + 1, c2 + 1
            FROM GenerateRecord
           WHERE c1 < 10
     )
SELECT c1, c2 FROM GenerateRecord;

C1 C2
-- --
 1 11
 2 12
 3 13
 4 14
 5 15
 6 16
 7 17
 8 18
 9 19
10 20

10 rows selected.
The following is the result of retrieving record in emp table which will be used in the example of WITH clause.
gSQL>
SELECT * FROM emp;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Jackson Kelly  
Joe     Kelly  
Scott   Bill   
Larry   Bill   
Paul    Jackson
Bill    Bill   

8 rows selected.
The following is an example of using SEARCH BREADTH FIRST BY.
gSQL> 
WITH w_emp( w_name, w_mgr ) AS
     (
         SELECT name, mgr
           FROM emp
          WHERE mgr IS NULL
         UNION ALL
         SELECT name, mgr
           FROM emp, w_emp
          WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
SELECT w_name, w_mgr, w_seq
  FROM w_emp;

ERR-42000(16511): cycle detected while executing recursive WITH query
gSQL> 
WITH w_emp( w_name, w_mgr ) AS
     (
         SELECT name, mgr
           FROM emp
          WHERE mgr IS NULL
         UNION ALL
         SELECT name, mgr
           FROM emp, w_emp
          WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

W_NAME  W_MGR   W_SEQ W_CYCLE
------- ------- ----- -------
Kelly   null        1 F      
Bill    Kelly       2 F      
Jackson Kelly       3 F      
Joe     Kelly       4 F      
Bill    Bill        5 T      
Larry   Bill        6 F      
Paul    Jackson     7 F      
Scott   Bill        8 F      

8 rows selected.
The following is an example of using SEARCH DEPTH FIRST BY.
gSQL>
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH DEPTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

W_NAME  W_MGR   W_SEQ W_CYCLE
------- ------- ----- -------
Kelly   null        1 F      
Bill    Kelly       2 F      
Bill    Bill        3 T      
Larry   Bill        4 F      
Scott   Bill        5 F      
Jackson Kelly       6 F      
Paul    Jackson     7 F      
Joe     Kelly       8 F      

8 rows selected.
The following is an example of using with clause in CREATE TABLE AS SELECT statement.
gSQL> 
CREATE TABLE new_emp AS
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

Table created.
The following is an example of using with clause in INSERT statement.
gSQL>
INSERT INTO new_emp
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr = 'Bill'
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

6 rows created.
The following is an example of using with clause in UPDATE statement.
gSQL>
UPDATE new_emp SET w_name = NULL
 WHERE ( w_name, w_mgr ) 
       IN ( WITH w_emp( w_name, w_mgr ) AS
                (
                    SELECT name, mgr
                      FROM emp
                     WHERE mgr = 'Bill'
                    UNION ALL
                    SELECT name, mgr
                      FROM emp, w_emp
                     WHERE mgr = w_emp.w_name
                ) SEARCH BREADTH FIRST BY w_name SET w_seq
                  CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
            SELECT w_name, w_mgr
              FROM w_emp );

9 rows updated.
The following is an example of using with clause in DELETE statement.
gSQL>
DELETE FROM new_emp
WHERE ( w_mgr ) 
      IN ( WITH w_emp( w_name, w_mgr ) AS
               (
                   SELECT name, mgr
                     FROM emp
                    WHERE mgr = 'Bill'
                   UNION ALL
                   SELECT name, mgr
                     FROM emp, w_emp
                    WHERE mgr = w_emp.w_name
               ) SEARCH BREADTH FIRST BY w_name SET w_seq
                 CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
           SELECT w_mgr
             FROM w_emp );

9 rows deleted.
The following is an example of using with clause in CREATE VIEW statement.
gSQL>
CREATE VIEW v_emp AS
WITH w_emp( w_name, w_mgr ) AS
     (
          SELECT name, mgr
            FROM emp
           WHERE mgr IS NULL
          UNION ALL
          SELECT name, mgr
            FROM emp, w_emp
           WHERE mgr = w_emp.w_name
     ) SEARCH BREADTH FIRST BY w_name SET w_seq
       CYCLE w_name SET w_cycle TO 'T' DEFAULT 'F'
SELECT w_name, w_mgr, w_seq, w_cycle
  FROM w_emp;

View created.

query specification

Function

It specifies the table which is derived from the result of <table expression>.

Syntax

<query specification> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list> <table expression>

<set quantifier> ::=
      ALL
    | DISTINCT

<table expression> ::=
      <from clause> [ <where clause> ] [ <hierarchical query clause> ] [ <group by clause> ] [ <having clause> ] [ <window clause> ]

Invocation and Access Rules

The user should satisfy one of the following conditions to perform <query specification>.

Syntax Rules and Parameters

<hint clause>

It specifies the hint for query execution. 
For more information, refer to SQL Hint.

<set quantifier>

It specifies whether to remove a duplicate of the query result. 
If it is omitted, it operates in the same way as ALL.

<select list>

It specifies the column to be retrieved among query results. 
For more information, refer to select list.

<from clause>

It specifies the tables to be retrieved.
For more information, refer to from clause.

<where clause>

It specifies conditions for retrieving.
For more information, refer to where clause.

<hierarchical query clause>

It specifies to retrieve the hierarchical model data in a hierarchy.
For more information, refer to hierarchical query clause.

<group by clause>

It specifies grouping of the query result. 
For more information, refer to group by clause.

<having clause>

It specifies conditions for the grouping result.
For more information, refer to having clause.

<window clause>

It defines the execution range of the window function.
For more information, refer to window clause.

Description

<hint clause>

<hint clause> is a comment which the user uses to directly command an optimizer how to execute SQL statement.

The optimizer of GOLDILOCKS preferentially applies <hint clause> specified by a user. 
If it is not applicable, the optimizer selects the best execution plan through the cost calculation.

Even when a syntactic error occurs in <hint clause>, GOLDILOCKS is set to ignore and perform it by default. Set HINT_ERROR property to on, then execute the query to check if a syntactic error exist in <hint clause>.

<set quantifier>

<set quantifier> sets whether to remove duplicates from the result set consisting of the <select list> expressions.

<select list>

It specifies columns to be retrieved from the query result. 
They are listed by separating by a comma (,). 
An asterisk (*) is used to specify all columns in <from clause>.

<from clause>

<from clause> specifies the tables or views to be retrieved.

<where clause>

<where clause> specifies the conditions to get only the desired results from the result obtained from <from clause>.

<hierarchical query clause>

It specifies to retrieve the hierarchical model data in a hierarchy.
It returns table records in a hierarchy of depth-first sequence by using the launch condition and sub-connectivity condition.

<group by clause>

<group by clause> specifies the method of grouping the result set to which <where clause> was applied.
When <group by clause> is specified, the following expressions can be used in <select list>.

<having clause>

<having clause> specifies the retrieving condition for the grouped result set.
It is generally used together with <group by clause>.

<window clause>

It specifies the execution range of the window function described in select list and order by clause

Examples

The following is an example of SELECT statement which uses <hint clause>.

gSQL> SELECT /*+ INDEX_DESC(supplier, supplier_pk_index) */ s_name, s_nation FROM supplier;

S_NAME                    S_NATION
------------------------- -------------
Supplier#5                CANADA
Supplier#4                UNITED STATES
Supplier#3                GERMANY
Supplier#2                KOREA
Supplier#1                FRANCE

5 rows selected.

The following is an example of SELECT statement which uses <set quantifier>.

gSQL> SELECT ALL p_type FROM part;

P_TYPE
------
COPPER
NICKEL
STEEL
NICKEL
STEEL

5 rows selected.

gSQL> SELECT DISTINCT p_type FROM part;

P_TYPE
------
COPPER
STEEL
NICKEL

3 rows selected.

The following is an example of SELECT statement which uses <where clause>.

gSQL> SELECT p_name, p_brand, p_type, p_size FROM part where p_size < 10;

P_NAME P_BRAND    P_TYPE P_SIZE
------ ---------- ------ ------
Part#1 Brand#1    COPPER      7
Part#2 Brand#1    NICKEL      1

2 rows selected.

The following is an example of retrieving the hierarchy data of SELECT statement by using <hierarchical query clause>.

gSQL> SELECT *
        FROM emp
      START WITH mgr IS NULL
      CONNECT BY NOCYCLE mgr = PRIOR name
      ORDER SIBLINGS BY name;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Larry   Bill   
Scott   Bill   
Jackson Kelly  
Paul    Jackson
Joe     Kelly  

7 rows selected.

The following is an example of SELECT statement which uses <group by clause>.

gSQL> SELECT ps_partkey, SUM(ps_availqty) FROM partsupp GROUP BY ps_partkey;

PS_PARTKEY SUM(PS_AVAILQTY)
---------- ----------------
         1            11401
         2             8025
         3            13864
         4            11564
         5             8744

5 rows selected.

The following is an example of SELECT statement which uses <having clause>.

gSQL> SELECT ps_partkey, SUM(ps_availqty) FROM partsupp GROUP BY ps_partkey having SUM(ps_availqty) > 10000;

PS_PARTKEY SUM(PS_AVAILQTY)
---------- ----------------
         1            11401
         3            13864
         4            11564

3 rows selected.

The following is an example of SELECT statement which uses <window clause>.

gSQL> 
SELECT item_no,
       sales_date,
       sales,
       SUM( sales ) OVER W1 cumulative_sales, 
       AVG( sales ) OVER w1 avg_sales
  FROM store
WINDOW w1 AS ( PARTITION BY item_no
               ORDER BY sales_date
               ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );

ITEM_NO SALES_DATE SALES CUMULATIVE_SALES AVG_SALES
------- ---------- ----- ---------------- ---------
    100 2001-01-01   150              150       150
    100 2001-01-02   100              250       125
    100 2001-01-03   170              420       140
    100 2001-01-04    90              510     127.5
    100 2001-01-05   200              710       142
    235 2001-01-01    70               70        70
    235 2001-01-02   130              200       100
    235 2001-01-03   190              390       130
    235 2001-01-04   150              540       135
    235 2001-01-05    50              590       118

10 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F801

Full set function

X

T051

Row types

X

T301

Functional dependencies

X

T325

Qualified SQL parameter references

X

T053

Explicit aliases for all-fields reference

O

T285

Enhanced derived column names

O

For More Information

Refer to query expression.

select list

Function

It specifies the columns to be retrieved from the query result.

Syntax

<select list> ::=
      <asterisk>
    | <select sublist> [ { <comma> <select sublist> } ... ]

<select sublist> ::=
      <derived column>
    | <qualified asterisk>

<qualified asterisk> ::=
      <asterisked identifier chain> <period> <asterisk>

<asterisked identifier chain> ::=
    <asterisked identifier> [ { <period> <asterisked identifier> } ... ]

<derived column> ::=
    <value expression> [ <as clause> ]

<as clause> ::=
    [ AS ] <column name>

Invocation and Access Rules

If columns or subqueries exist in <select list> statement, the user should satisfy the following.

Syntax Rules and Parameters

<select list>

It has <asterisk> or <select sublist>.

<asterisk>

<select sublist>

Description

<select list>

<select list> specifies the columns to be included in the result set.

<asterisk>

<asterisk> sets all columns in <from clause> as a select list.

<select sublist>

<select sublist> has <derived column> or <qualified asterisk>.

If two or more <select sublist> are specified, each <select sublist> should be separated by a comma (',').

Names to Be Set in select list

Examples

The following is an example of SELECT statement which uses <asterisk>.

gSQL> SELECT * FROM supplier;

S_SUPPKEY S_NAME                    S_NATION      S_PHONE
--------- ------------------------- ------------- ---------------
        1 Supplier#1                FRANCE        27-918-335-1736
        2 Supplier#2                KOREA         15-679-861-2259
        3 Supplier#3                GERMANY       11-383-516-1199
        4 Supplier#4                UNITED STATES 25-843-787-7479
        5 Supplier#5                CANADA        21-151-690-3663

5 rows selected.

The following is an example of SELECT statement which uses <select sublist>.

gSQL> SELECT revenue.* FROM revenue;

SUPPLIER_NO TOTAL_REVENUE
----------- -------------
          1      11978.64
          2       20321.5
          3      41844.68

3 rows selected.

gSQL> SELECT supplier_no suppno, total_revenue AS TOTAL FROM revenue;

SUPPNO    TOTAL
------ --------
     1 11978.64
     2  20321.5
     3 41844.68

3 rows selected.

gSQL> SELECT 1, revenue.*, CAST( total_revenue AS NATIVE_INTEGER ) TOTAL FROM revenue;

1 SUPPLIER_NO TOTAL_REVENUE TOTAL
- ----------- ------------- -----
1           1      11978.64 11979
1           2       20321.5 20322
1           3      41844.68 41845

3 rows selected.

For More Information

Refer to query specification.

from clause

Function

It specifies the table which is derived from one or more tables.

Syntax

<from clause> ::=
    FROM <table reference list>

<table reference list> ::=
    <table reference> [ { , <table reference> } ... ]

<table reference> ::=
      <table factor>
    | <joined table>
    | <table reference> <pivot clause>
    | <table reference> <unpivot clause>

<table factor> ::=
      <table primary> [ <sample clause> ]

<table primary> ::=
      <table name> [ <cluster domain> ] [ [ AS ] <correlation name> ]
    | <derived table> [ <cluster domain> ] [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
    | <lateral derived table> [ <cluster domain> ] [ [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] ]
    | <table function derived table> [ [ AS ] <correlation name> ]
    | <parenthesized joined table>

<derived table> ::=
    <table subquery>

<lateral derived table> ::=
    LATERAL <table subquery>

<parenthesized joined table> ::=
      <left paren> <parenthesized joined table> <right paren>
    | <left paren> <joined table> <right paren>

<derived column list> ::=
    <column name list>

<cluster domain> ::=
    @ <cluster domain name>

<cluster domain name> ::=
      GLOBAL
    | LOCAL
    | LOCAL_OFFLINE
    | <identifier>

<table function derived table> ::=
      TABLE <left paren> <table function expression> <right paren>

<table function expression> ::=
      <table function name> <left paren> [ <table function argument list> ] <right paren>

<table function argument list> ::=
      <value expression> [ <comma> ... ]

Invocation and Access Rules

The access privilege for the table or view specified in <table reference list> is required.

Syntax Rules and Parameters

<table reference list>

<table reference>

<table factor>

<table primary>

SELECT col1, col2 
FROM ( SELECT i1, i2 FROM t1 ) AS a( col1, col2 ) 
WHERE col1 = 1 AND col2 = 1;
SELECT t1.col1, ft.rf2 
FROM t1, TABLE( tablefunc( t1.col1 ) );

<correlation name>

<derived column list>

The same <column name> should not exist two or more in <derived column list>.

<cluster domain>

<cluster domain name>

Only a cluster group name or a cluster member name can be <identifier> of <cluster domain name>.

Description

<table reference list>

Two or more tables can be specified in <table reference list> by using a comma (,).

<table reference>

A single table, or view, table subquery, joined table can be <table reference>. Others except for the joined table can have a correlation name.
For more information about joined table, refer to joined table.

<table primary>

The tables, views, table subqueries and <parenthesized joined table> can be <table primary>.

The table, view, table subquery can have a correlation name, and AS can be omitted. If the correlation name is specified, it should be used in everywhere referring to the table, view, table subquery such as <select list>, <where clause>.

The table subquery can specify <derived column list>. The name specified in <derived column list> should be used in everywhere referring to table subquery column as like correlation name. To use <derived column list> in the table subquery, the correlation name should be specified.

<parenthesized joined table> specifies the logical join order for the table participating in the join operation. At this time, if all join operations for the tables enclosed with parentheses are cross join, inner join, the join order can be changed by an optimizer.

<cluster domain>

When <cluster domain> is omitted, it means the same as using GLOBAL as <cluster domain name>.
For more information, refer to Cluster Domain.

<cluster domain name>

The reserved words defined in <cluster domain name> mean as follows.

If <identifier> is specified in <cluster domain name>, a cluster group or a cluster member with the corresponding name is selected as Cluster Domain.

Examples

The following is an example of SELECT statement to query a single table by using <table name>.

gSQL> SELECT c_name, c_nation FROM customer;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.

The following is an example of SELECT statement which uses <derived table>.

gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.


gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer) AS CUST ("CUSTOMER_NAME", "CUSTOMER_NATION");

CUSTOMER_NAME CUSTOMER_NATION
------------- ---------------
Customer#1    KOREA          
Customer#2    CANADA         
Customer#3    KOREA          
Customer#4    GERMANY        
Customer#5    UNITED STATES  

5 rows selected.

The following is an example of SELECT statement using <lateral derived table>.

gSQL> SELECT r_name, n_name
  FROM region, (  SELECT n_name
                    FROM nation
                   WHERE n_regionkey = r_regionkey
               ) v_nation
 WHERE r_name = 'ASIA';

ERR-42000(16036): 'R_REGIONKEY': invalid identifier :
                   WHERE n_regionkey = r_regionkey
                                       *

gSQL> SELECT r_name, n_name
  FROM region, LATERAL (  SELECT n_name 
                            FROM nation
                           WHERE n_regionkey = r_regionkey
                       ) v_nation
 WHERE r_name = 'ASIA';
R_NAME                    N_NAME
------------------------- -------------------------
ASIA                      INDIA
ASIA                      INDONESIA
ASIA                      JAPAN
ASIA                      CHINA
ASIA                      VIETNAM

5 rows selected.

The following is an example of SELECT statement for a joined table which uses parentheses.

gSQL> SELECT customer.c_name, o_totalprice FROM (customer INNER JOIN orders ON customer.c_custkey = orders.o_custkey);

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

The following is an example of SELECT statement which uses two table separated by a comma (,).

gSQL> SELECT c_name, o_totalprice FROM customer, orders;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#1     46929.18
Customer#1    193846.25
Customer#1     32151.78
Customer#1     144659.2
Customer#2    173665.47
Customer#2     46929.18
Customer#2    193846.25
Customer#2     32151.78
Customer#2     144659.2
Customer#3    173665.47
Customer#3     46929.18
Customer#3    193846.25
Customer#3     32151.78
Customer#3     144659.2
Customer#4    173665.47
Customer#4     46929.18
Customer#4    193846.25
Customer#4     32151.78
Customer#4     144659.2

C_NAME     O_TOTALPRICE
---------- ------------
Customer#5    173665.47
Customer#5     46929.18
Customer#5    193846.25
Customer#5     32151.78
Customer#5     144659.2

25 rows selected.

The following is an example of SELECT statement which uses <cluster domain>.

gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@GLOBAL);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer)@LOCAL;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@G1);

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.
gSQL> SELECT * FROM (SELECT c_name, c_nation FROM customer@G2N1);

C_NAME     C_NATION
---------- -------------
Customer#3 KOREA
Customer#4 GERMANY

2 rows selected.

For More Information

Refer to subquery.

joined table

Function

It specifies the table derived from a cartesian product, inner join, outer join.

Syntax

<joined table> ::=
      <cross join>
    | <qualified join>
    | <natural join>

<cross join> ::=
    <table reference> CROSS JOIN <table factor>

<qualified join> ::=
    <table reference> [ <join type> ] JOIN <table reference> <join specification>

<natural join> ::=
    <table reference> NATURAL [ <join type> ] JOIN <table factor>

<join specification> ::=
      <join condition>
    | <named columns join>

<join condition> ::=
    ON <search condition>

<named columns join> ::=
    USING ( <join column list> )

<join type> ::=
      INNER
    | { LEFT | RIGHT | FULL } [ OUTER ]

<join column list> ::=
    <column name list>

Invocation and Access Rules

The access privilege for all tables and views specified in a joined table is required.

Syntax Rules and Parameters

<cross join>

<join specification> specifying the join condition does not appear at the location of <cross join>.
A single table, <table subquery> or <parenthesized joined table> can appear on the right of <cross join>.

<qualified join>

<natural join>

<join specification>

Description

<cross join>

<cross join> returns a result which combines each left row with all right rows.
T1 ( 1, 1 ), ( 2, 2 )
T2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 CROSS JOIN t2;
C1 C2 C1 C2
-- -- -- --
 1  1  2  2
 1  1  3  3
 2  2  2  2
 2  2  3  3
4 rows selected.
The explicit join condition can not be specified in <cross join>, but the join condition for the two tables can be specified in <where clause>. In this case, it performs inner join.
• SELECT * FROM t1 CROSS JOIN t2 WHERE t1.c1 = t2.c1;
• <=> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
T1 ( 1, 1 ), ( 2, 2 )
T2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 CROSS JOIN t2 WHERE t1.c1 = t2.c1;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
1 row selected.

<qualified join>

<qualified join> combines each left row with all right rows, then returns only the rows satisfying the join condition as a result.

If <where clause> exists in <table expression>, then conditions in <where clause> are applied to the result set of <qualified join>.

The result is same, even when inner join processes the conditions in <where clause> as join conditions. But result differs when outer join processes the conditions in <where clause> as join conditions.

INNER JOIN
t1 ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )
t2 ( 2, 2 ), ( 3, 3 )
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
( 2,  2,    2,    2 )                        ( 2,  2,    2,    2 )
  ( 3,  3,    3,    3 )                   →   ( 3,  3,    3,    3 )
OUTER JOIN
t1 ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ), ( 5, 5 )
t2 ( 2, 2 ), ( 3, 3 )
gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
C1 C2   C1   C2
-- -- ---- ----
 1  1 null null
 2  2    2    2
 3  3    3    3
 4  4 null null
 5  5 null null
5 rows selected.
gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1 WHERE t1.c2 = t2.c2;
C1 C2 C1 C2
-- -- -- --
 2  2  2  2
 3  3  3  3
2 rows selected.
( 1,  1, null, null )
  ( 2,  2,    2,    2 )                        ( 2,  2,    2,    2 )
  ( 3,  3,    3,    3 )                   →   ( 3,  3,    3,    3 ) 
  ( 4,  4, null, null )
  ( 5,  5, null, null )

Left outer join combines right rows satisfying the join condition for the left rows, then returns the combined rows as a result. If right rows satisfying the join condition does not exist, then it returns the result whose left row values are as they are and whose right row values are filled with NULL.

LEFT OUTER JOIN
t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.c1 = t2.c1;
C1 C2   C1   C2
-- -- ---- ----
 1  1 null null
 2  2    2    2
2 rows selected.

Right outer join is operated in an opposite way of left outer join.

RIGHT OUTER JOIN

t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.c1 = t2.c1;
  C1   C2 C1 C2
---- ---- -- --
   2    2  2  2
null null  3  3
2 rows selected.

Full outer join returns the left rows filled with NULL for all right rows which do not satisfy the join condition together with left outer join results.

FULL OUTER JOIN

t1 ( 1, 1 ), ( 2, 2 )
t2 ( 2, 2 ), ( 3, 3 )

gSQL> SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.c1 = t2.c1;
  C1   C2   C1   C2
---- ---- ---- ----
   1    1 null null
   2    2    2    2
null null    3    3
3 rows selected.

<natural join>

<natural join> joins all columns with same names in two tables participating in join as equal. In other words, it is the same as specifying all columns with same names of two tables participating in join in USING clause of inner join.

t1 ( C1 INTEGER, C2 INTEGER )
t2 ( C1 INTEGER, C3 INTEGER )

t1 ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
t2 ( 1, 100 ), ( 2, 200 ), ( 3, 300 )

gSQL> SELECT * FROM t1 NATURAL JOIN t2; 
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

gSQL> SELECT * FROM t1 INNER JOIN t2 USING ( c1 );
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

<join specification>

It specifies the join condition.
<join condition> specifies the condition for joining left rows and right rows of a join statement.
<named columns join> specifies the join condition by listing that <column name>, if the same <column name> exist in left rows and right rows.
t1 ( C1 INTEGER, C2 INTEGER )
t2 ( C1 INTEGER, C3 INTEGER )

t1 ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
t2 ( 1, 100 ), ( 2, 200 ), ( 3, 300 )

• <join condition>
gSQL> SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1;
C1 C2 C1  C3
-- -- -- ---
 1 10  1 100
 2 20  2 200
 3 30  3 300
3 rows selected.

• <named columns join>
gSQL> SELECT * FROM t1 INNER JOIN t2 USING ( c1 );
C1 C2  C3
-- -- ---
 1 10 100
 2 20 200
 3 30 300
3 rows selected.

Examples

The following is an example of SELECT statement which uses <cross join>.

gSQL> SELECT c_name, o_totalprice FROM customer CROSS JOIN orders;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#1     46929.18
Customer#1    193846.25
Customer#1     32151.78
Customer#1     144659.2
Customer#2    173665.47
Customer#2     46929.18
Customer#2    193846.25
Customer#2     32151.78
Customer#2     144659.2
Customer#3    173665.47
Customer#3     46929.18
Customer#3    193846.25
Customer#3     32151.78
Customer#3     144659.2
Customer#4    173665.47
Customer#4     46929.18
Customer#4    193846.25
Customer#4     32151.78
Customer#4     144659.2

C_NAME     O_TOTALPRICE
---------- ------------
Customer#5    173665.47
Customer#5     46929.18
Customer#5    193846.25
Customer#5     32151.78
Customer#5     144659.2

25 rows selected.

The following is an example of SELECT statement which uses inner join.

gSQL> SELECT c_name, o_totalprice FROM customer INNER JOIN orders ON c_custkey = o_custkey;

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

The following is an example of SELECT statement which uses outer join.

gSQL> SELECT c_name, o_totalprice FROM customer LEFT OUTER JOIN orders ON c_custkey = o_custkey AND o_orderdate < '1996-01-01';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1         null
Customer#2         null
Customer#3     32151.78
Customer#4    193846.25
Customer#5     144659.2

5 rows selected.

gSQL> SELECT c_name, o_totalprice FROM customer RIGHT OUTER JOIN orders ON c_custkey = o_custkey AND c_nation = 'KOREA';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
null           46929.18
null          193846.25
Customer#3     32151.78
null           144659.2

5 rows selected.

gSQL> SELECT c_name, o_totalprice FROM customer FULL OUTER JOIN orders ON c_custkey = o_custkey AND c_nation = 'KOREA' AND o_orderdate < '1996-01-01';

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1         null
Customer#2         null
Customer#3     32151.78
Customer#4         null
Customer#5         null
null          173665.47
null           46929.18
null          193846.25
null           144659.2

9 rows selected.

The following is an example of SELECT statement which uses natural join.

gSQL> SELECT c_name, o_totalprice FROM (SELECT c_custkey custkey, c_name FROM customer) NATURAL JOIN (SELECT o_custkey custkey, o_totalprice FROM orders);

C_NAME     O_TOTALPRICE
---------- ------------
Customer#1    173665.47
Customer#2     46929.18
Customer#4    193846.25
Customer#3     32151.78
Customer#5     144659.2

5 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F401

Extended joined table

O

F402

Named column joins for LOBs, arrays, and multisets

X

F403

Partitioned join tables

X

For More Information

Refer to from clause.

pivot clause

Function

It describes a cross table which converts a row (value) to a column.

Syntax

<pivot clause> ::=
    PIVOT
    <left paren>
       <aggregation function> [[AS] alias]
       [, <aggregation function> [[AS] alias]] ...
       <pivot for clause>
       <pivot in clause>
    <right paren>

<pivot for clause> ::=
      FOR column
    | FOR <left paren> column [, column] ... <right paren>

<pivot in clause> ::=
    IN
    <left paren>
    { <pivot value list> [[AS] alias] [, <pivot value list> [[AS] alias]] ... }
    <right paren>

<pivot value list> ::=
      expr
    | <left paren> expr [, expr] ... <right paren>

Syntax Rules and Parameters

<pivot clause>

A nested aggregation function is not available in <aggregation function>.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( SUM( c2 ) ) 
                       FOR c1
                       IN (
                             1
                           , 2
                          )
                        );

ERR-42000(16160): group function is nested too deeply : 
                 SUM( SUM( c2 ) ) 
                      *
ERROR at line 3:

The number of columns described in <pivot for clause> and the number of expr in <pivot value list> should be same.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR ( c1, c2 )
                       IN (
                             ( 1, 2 )
                           , ( 3 )
                          )
                        );

ERR-42000(16606): the number of elements in pivot values mismatch the pivot columns : 
                           , ( 3 )
                             *
ERROR at line 7:

<pivot for clause>

Only column_name is allowed for the column within <pivot for clause>.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR 1
                       IN (
                             1
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(40000): syntax error: 
                       FOR 1
                           ^
Error at line 4


gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1 + 1
                       IN (
                             1
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(40000): syntax error: 
                       FOR c1 + 1
                              ^
Error at line 4

<pivot in clause>

expr within <pivot in clause> supports a constant only.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1
                       IN (
                             c1
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(16608): non-constant expression is not allowed for pivot|unpivot values : 
                             c1
                             *
ERROR at line 6:


gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1
                       IN (
                             CLOCK_DATE()
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(16608): non-constant expression is not allowed for pivot|unpivot values : 
                             CLOCK_DATE()
                             *
ERROR at line 6:

An expression whose value is different among records or whose evaluation value changes each time is not supported.

gSQL> SELECT *
        FROM t1 PIVOT(
                       SUM( 1 ) 
                       FOR c1
                       IN (
                             RANDOM( 1, 2 )
                           , 2
                          )
                        );

    2     3     4     5     6     7     8     9 
ERR-42000(16608): non-constant expression is not allowed for pivot|unpivot values : 
                             RANDOM( 1, 2 )
                             *
ERROR at line 6:

Description

<pivot clause>

<pivot clause> statement defines a new cross table by using the source relation.

gSQL> SELECT T_PIVOT.*
        FROM t1
                PIVOT(                  -- new cross table
                       SUM( c2 ) 
                       FOR c1
                       IN (
                             1
                           , 2
                          )
                        ) AS T_PIVOT;

1 2
- -
1 3

1 row selected.

The relation described before <pivot clause> statement is the source relation of the cross table.

gSQL> SELECT *
        FROM t1                  -- source relation
                PIVOT(
                       SUM( c2 ) 
                       FOR c1
                       IN (
                             1
                           , 2
                          )
                        );


1 2
- -
1 3

1 row selected.

<pivot for clause>

<pivot for clause> statement defines the target column of pivot.

gSQL> SELECT c1 FROM t1;

C1
--
 1
 2
 2

3 rows selected.



gSQL> SELECT *
        FROM t1 
                PIVOT(
                       SUM( c2 ) 
                       FOR c1      -- t1.c1
                       IN (
                             1     -- t1.c1 = 1
                           , 2     -- t1.c1 = 2
                          )
                        );


1 2
- -
1 3

1 row selected.

Pivot Column

Configure the new pivot columns of the cross table as many as the number of combinations of the values listed in <pivot in clause> and <aggregation function>.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )    -- aggregation #1
                       FOR c1
                       IN (
                             1      -- row #1
                           , 2      -- row #2
                          )
                        );

1 2
- -
1 3

1 row selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )    -- aggregation #1
                     , COUNT(*)     -- aggregation #2
                       FOR c1
                       IN (
                             1      -- row #1
                           , 2      -- row #2
                          )
                        );
1 1 2 2
- - - -
1 1 3 2

1 row selected.

Configure the columns of the cross table with all columns which were not referred within <pivot clause> statement among the columns of the source relation.

gSQL> \DESC t1

COLUMN_NAME TYPE         IS_NULLABLE
----------- ------------ -----------
C1          NUMBER(10,0) TRUE       
C2          NUMBER(10,0) TRUE  


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( 1 ) 
                       FOR c1      -- Refer to t1.c1
                       IN (
                             1
                           , 2
                          )
                        );

C2    1 2
-- ---- -
 1    1 1
 2 null 1

2 rows selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )   -- Refer to t1.c2
                       FOR c1      -- Refer to t1.c1
                       IN (
                             1
                           , 2
                          )
                        );

1 2
- -
1 3

1 row selected.

Pivot Column Name

Configure the pivot column name by adding '_' between the pivot column name prefix and the pivot column name suffix .

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix
                       FOR c1
                       IN (
                             1   AS ONE       -- prefix #1
                           , 2   AS TWO       -- prefix #2
                          )
                        );

ONE_TOTAL TWO_TOTAL
--------- ---------
        1         3

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix #1
                     , COUNT(*)  AS CNT       -- suffix #2
                       FOR c1
                       IN (
                             1   AS ONE       -- prefix #1
                           , 2   AS TWO       -- prefix #2
                          )
                        );

ONE_TOTAL ONE_CNT TWO_TOTAL TWO_CNT
--------- ------- --------- -------
        1       1         3       2

1 row selected.
Pivot Column Name Prefix

The display name for expr listed within <pivot value list> is used as a prefix of the pivot column name.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix
                       FOR c1
                       IN (
                             1                -- prefix #1
                           , '2'              -- prefix #2
                          )
                        );


1_TOTAL '2'_TOTAL
------- ---------
      1         3

1 row selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL     -- suffix #1
                     , COUNT(*)  AS CNT       -- suffix #2
                       FOR c1
                       IN (
                             1                -- prefix #1
                           , '2'              -- prefix #2
                          )
                        );

1_TOTAL 1_CNT '2'_TOTAL '2'_CNT
------- ----- --------- -------
      1     1         3       2

1 row selected.

If two or more expr are used in <pivot value list>, then the display names of each expr are concatenated with '_'.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       COUNT(*)
                       FOR ( c1, c2 )
                       IN (
                             ( 1, 2 )      -- prefix #1
                           , ( '3', '4' )  -- prefix #2
                          )
                        );

1_2 '3'_'4'
--- -------
  0       0

1 row selected.



gSQL> SELECT *
        FROM t1
                PIVOT(
                       COUNT(*) AS CNT     -- suffix
                       FOR ( c1, c2 )
                       IN (
                             ( 1, 2 )      -- prefix #1
                           , ( '3', '4' )  -- prefix #2
                          )
                        );

1_2_CNT '3'_'4'_CNT
------- -----------
      0           0

1 row selected.

If an alias is specified in <pivot value list>, then the prefix of the pivot column name is replaced with the alias.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix #1
                           , '2'  AS TWO     -- prefix #2
                          )
                        );

ONE TWO
--- ---
  1   3

1 row selected.


gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL    -- suffix
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix #1
                           , '2'  AS TWO     -- prefix #2
                          )
                        );

ONE_TOTAL TWO_TOTAL
--------- ---------
        1         3

1 row selected.
Pivot Column Name Suffix

An alias for the aggregated value can be given in <aggregation function>, and this alias is operated as a suffix of the pivot column name.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 ) AS TOTAL    -- suffix #1
                     , COUNT(*)  AS CNT      -- suffix #2
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix
                          )
                        );

ONE_TOTAL ONE_CNT
--------- -------
        1       1

1 row selected.

If an alias is not given in <aggregation function>, then the suffix of the pivot column name does not exist.

gSQL> SELECT *
        FROM t1
                PIVOT(
                       SUM( c2 )             -- suffix #1 (empty)
                     , COUNT(*)  AS CNT      -- suffix #2
                       FOR c1
                       IN (
                             1    AS ONE     -- prefix
                          )
                        );

ONE ONE_CNT
--- -------
  1       1

1 row selected.

Examples

The following is an example of SELECT statement which used <pivot clause>.

gSQL> SELECT * FROM sales;

ITEM   REGION PRICE AMOUNT
------ ------ ----- ------
apple  seoul  30000     10
apple  seoul  30000     30
kiwi   seoul  20000     15
mango  seoul  40000     20
orange seoul  25000      5
apple  busan  25000      5
mango  busan  35000     20
mango  busan  45000     10
orange busan  30000     15
apple  daegu  25000     30
kiwi   daegu  25000     10
kiwi   daegu  15000     20
apple  jeju   25000     30
apple  jeju   35000      5
kiwi   jeju   15000     10
kiwi   jeju   15000     10
mango  jeju   45000     10

17 rows selected.


--# Retrieve the sales aggregation value for each fruit per region.
gSQL> SELECT *
        FROM sales PIVOT(
                          SUM( price * amount ) FOR item IN (  'apple'  PC_APPLE
                                                             , 'kiwi'   PC_KIWI
                                                             , 'mango'  PC_MANGO
                                                             , 'orange' PC_ORANGE )
                        );

REGION PC_APPLE PC_KIWI PC_MANGO PC_ORANGE
------ -------- ------- -------- ---------
seoul   1200000  300000   800000    125000
daegu    750000  550000     null      null
busan    125000    null  1150000    450000
jeju     925000  300000   450000      null

4 rows selected.

Compatibility

The SQL standard does not define the concept of pivot.

For More Information

Refer to from clause.

unpivot clause

Function

It describes a cross table which converts a column to a row (value).

Syntax

<unpivot clause> ::=
    UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ]
    <left paren>
       <unpivot value column list>
       <unpivot for clause>
       <unpivot in clause>
    <right paren>

<unpivot value column list> ::=
       name
     | <left paren> name [, name] ... <right paren>
       
<unpivot for clause> ::=
      FOR name
    | FOR <left paren> name [, name] ... <right paren>

<unpivot in clause> ::=
    IN
    <left paren>
        <columns of unpivot in clause> [, <columns of unpivot in clause>] ...
    <right paren>

<columns of unpivot in clause> ::=
    {
        column
      | <left paren> column [, column] ... <right paren>
    }
    [ AS
         {
             expr
         }
    ]

Syntax Rules and Parameters

<unpivot clause>

If [ INCLUDE NULLS | EXCLUDE NULLS ] is not described in <unpivot clause> statement, then it is operated the same as EXCLUDE NULLS.

gSQL> SELECT * FROM result;

STUDENT ENGLISH MATH SCIENCE HISTORY
------- ------- ---- ------- -------
David        70   70      80      90
Linda        90   60      80      70
Tom          90 null    null      70

3 rows selected.


gSQL> SELECT *
        FROM result
              UNPIVOT INCLUDE NULLS      -- INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

12 rows selected.


gSQL> SELECT *
        FROM result
              UNPIVOT EXCLUDE NULLS      -- EXCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.


gSQL> SELECT *
        FROM result
              UNPIVOT                     -- Omitting NULLS treatment
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.

<unpivot in clause>

Only column_name is allowed for the column within <unpivot in clause>.

gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             'aaa'
                          )
                     );

ERR-42000(40000): syntax error: 
                             'aaa'
                             ^   ^
Error at line 8


gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             xxx
                          )
                     );

ERR-42000(16036): 'XXX': invalid identifier : 
                             xxx
                             *
ERROR at line 8:

The number of names within <unpivot value column list> and the number of columns in <columns of unpivot in clause> should be same.

gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score                    -- <unpivot value column list>
                       FOR uc_subject
                       IN (
                             english               -- <columns of unpivot in clause> #1
                           , ( math, science )     -- <columns of unpivot in clause> #2
                          )
                     );

ERR-42000(16607): the number of elements in unpivot values mismatch the unpivot columns : 
                           , ( math, science )     -- <columns of unpivot in clause> #2
                                     *
ERROR at line 9:


gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       ( uc_score_1, uc_score_2 )  -- <unpivot value column list>
                       FOR uc_subject
                       IN (
                             english               -- <columns of unpivot in clause> #1
                           , ( math, science )     -- <columns of unpivot in clause> #2
                          )
                     );

ERR-42000(16607): the number of elements in unpivot values mismatch the unpivot columns : 
                             english               -- <columns of unpivot in clause> #1
                             *
ERROR at line 8:

expr which is described after AS keyword of <columns of unpivot in clause> can not refer to the column of the unpivot target relation.

gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english AS english    -- result.column
                          )
                     );

ERR-42000(16036): 'ENGLISH': invalid identifier : 
                             english AS english
                                        *
ERROR at line 8:


gSQL> SELECT *
        FROM result
              UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english AS student    -- result.column
                          )
                     );

ERR-42000(16036): 'STUDENT': invalid identifier : 
                             english AS student
                                        *
ERROR at line 8:


gSQL> SELECT *
        FROM dual
       WHERE EXISTS(
                     SELECT *
                       FROM result
                             UNPIVOT
                                    (
                                      uc_score
                                      FOR uc_subject
                                      IN (
                                            english AS dummy  -- dual.dummy (outer query's column)
                                         )
                                    )
                    );

DUMMY
-----
X    

1 row selected.

Description

<unpivot clause>

<unpivot clause> statement defines a new cross table by using the source relation.

gSQL> SELECT T_UNPIVOT.*
        FROM result
              UNPIVOT                       -- new cross table
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     ) T_UNPIVOT;

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.

The relation described before <unpivot clause> statement is the source relation of the cross table.

gSQL> SELECT * 
        FROM result                  -- source relation
              UNPIVOT 
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.

If INCLUDE NULLS is specified in <unpivot clause>, then all records created by unpivot are returned as results.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

If EXCLUDE NULLS is specified in <unpivot clause>, then it returns records excluding the records all of whose unpivot columns are null among records created by unpivot.

--# If all unpivot columns are null

gSQL> SELECT * 
        FROM result
              UNPIVOT EXCLUDE NULLS   -- Delete the row whose uc_score column value is null.
                     (
                       uc_score        
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
David   SCIENCE          80
Linda   SCIENCE          80
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

10 rows selected.
--# If some unpivot columns are null

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, math )
                           , ( science, history )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_MATH            70         70
Linda   ENGLISH_MATH            90         60
Tom     ENGLISH_MATH            90       null
David   SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY       null         70

6 rows selected.


gSQL> SELECT * 
        FROM result
              UNPIVOT EXCLUDE NULLS   -- Delete the row whose uc_score_1 and uc_score_2 values are null.
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, math )
                           , ( science, history )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_MATH            70         70
Linda   ENGLISH_MATH            90         60
Tom     ENGLISH_MATH            90       null
David   SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY       null         70

6 rows selected.
--# If all unpivot columns are null

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, history )
                           , ( math, science )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_HISTORY         70         90
Linda   ENGLISH_HISTORY         90         70
Tom     ENGLISH_HISTORY         90         70
David   MATH_SCIENCE            70         80
Linda   MATH_SCIENCE            60         80
Tom     MATH_SCIENCE          null       null

6 rows selected.


gSQL> SELECT * 
        FROM result
              UNPIVOT EXCLUDE NULLS   -- Delete the row whose uc_score_1 and uc_score_2 are null.

                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject
                       IN (
                             ( english, history )
                           , ( math, science )
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_HISTORY         70         90
Linda   ENGLISH_HISTORY         90         70
Tom     ENGLISH_HISTORY         90         70
David   MATH_SCIENCE            70         80
Linda   MATH_SCIENCE            60         80

5 rows selected.

Unpivot Column Which Consists of the Information about the Column of the Source Relation

<unpivot for clause> statement configures a new unpivot column consisting of the information about the unpivot target column.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject    -- value is column's name in <columns of unpivot in clause>
                       IN (
                             english     -- <columns of unpivot in clause> #1
                           , math        -- <columns of unpivot in clause> #2
                           , science     -- <columns of unpivot in clause> #3
                           , history     -- <columns of unpivot in clause> #4
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

The name within <unpivot for clause> is the column name of the new unpivot column.

gSQL> SELECT T_UNPIVOT.* 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR english    -- T_UNPIVOT's column
                       IN (
                             english
                          )
                     ) T_UNPIVOT;

STUDENT MATH SCIENCE HISTORY ENGLISH UC_SCORE
------- ---- ------- ------- ------- --------
David     70      80      90 ENGLISH       70
Linda     60      80      70 ENGLISH       90
Tom     null    null      70 ENGLISH       90

3 rows selected.

<unpivot for clause> 내에 기술된 name 개수만큼 새로운 unpivot column이 구성한다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR (
                              uc_subject_1  -- <unpivot for clause> unpivot column #1
                            , uc_subject_2  -- <unpivot for clause> unpivot column #2
                            )
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT_1 UC_SUBJECT_2 UC_SCORE
------- ------------ ------------ --------
David   ENGLISH      ENGLISH            70
Linda   ENGLISH      ENGLISH            90
Tom     ENGLISH      ENGLISH            90
David   MATH         MATH               70
Linda   MATH         MATH               60
Tom     MATH         MATH             null
David   SCIENCE      SCIENCE            80
Linda   SCIENCE      SCIENCE            80
Tom     SCIENCE      SCIENCE          null
David   HISTORY      HISTORY            90
Linda   HISTORY      HISTORY            70
Tom     HISTORY      HISTORY            70

12 rows selected.

<columns of unpivot in clause> 내에 기술된 column들의 display name를 '_'로 연결하여 새로운 string value를 구성한다.

구성된 string value는 <unpivot for clause>에 의해 생성된 unpivot column의 값이다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR uc_subject               -- value is column's name in <columns of unpivot in clause>
                       IN (
                             ( english, math )      -- <columns of unpivot in clause> #1
                           , ( science, history )   -- <columns of unpivot in clause> #2
                          )
                     );

STUDENT UC_SUBJECT      UC_SCORE_1 UC_SCORE_2
------- --------------- ---------- ----------
David   ENGLISH_MATH            70         70
Linda   ENGLISH_MATH            90         60
Tom     ENGLISH_MATH            90       null
David   SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY       null         70

6 rows selected.

Unpivot table의 각 레코드 내 <unpivot for clause>에 의해 구성된 unpivot column들은 모두 같은 값을 가진다.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       ( uc_score_1, uc_score_2 )
                       FOR (
                             uc_subject_1               -- value is column's name in <columns of unpivot in clause>
                           , uc_subject_2               -- value is column's name in <columns of unpivot in clause>
                           )
                       IN (
                             ( english, math )      -- <columns of unpivot in clause> #1
                           , ( science, history )   -- <columns of unpivot in clause> #2
                          )
                     );

STUDENT UC_SUBJECT_1    UC_SUBJECT_2    UC_SCORE_1 UC_SCORE_2
------- --------------- --------------- ---------- ----------
David   ENGLISH_MATH    ENGLISH_MATH            70         70
Linda   ENGLISH_MATH    ENGLISH_MATH            90         60
Tom     ENGLISH_MATH    ENGLISH_MATH            90       null
David   SCIENCE_HISTORY SCIENCE_HISTORY         80         90
Linda   SCIENCE_HISTORY SCIENCE_HISTORY         80         70
Tom     SCIENCE_HISTORY SCIENCE_HISTORY       null         70

6 rows selected.

Unpivot Column Which Consists of the Column Values of the Source Relation

<unpivot value column list> statement defines the new unpivot columns with the value of the unpivot target column.

The column values in <columns of unpivot in clause> are set to the unpivot column value created by <unpivot value column list>.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score        -- value is column's value in <columns of unpivot in clause>
                       FOR uc_subject
                       IN (
                             english   -- <columns of unpivot in clause> #1
                           , math      -- <columns of unpivot in clause> #2
                           , science   -- <columns of unpivot in clause> #3
                           , history   -- <columns of unpivot in clause> #4
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

<unpivot in clause> statement defines the target column of unpivot.

gSQL> SELECT * 
        FROM result
              UNPIVOT INCLUDE NULLS
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english   -- english is result.english
                           , math      -- math is result.math
                           , science   -- science is result.science
                           , history   -- history is result.history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
Linda   MATH             60
Tom     MATH           null
David   SCIENCE          80
Linda   SCIENCE          80
Tom     SCIENCE        null
David   HISTORY          90
Linda   HISTORY          70
Tom     HISTORY          70

12 rows selected.

Examples

The following is an example of SELECT statement using <unpivot clause>.

gSQL> SELECT * FROM result;

STUDENT ENGLISH MATH SCIENCE HISTORY
------- ------- ---- ------- -------
David        70   70      80      90
James        80   90      60      60
Mary         70   90      50      80
Linda        90   60      80      70
Tom          90 null    null      70
null       null null    null    null

6 rows selected.


--# Retrieve scores per each subject for all students.
gSQL> SELECT *
  FROM result
             UNPIVOT
                     (
                       uc_score
                       FOR uc_subject
                       IN (
                             english
                           , math
                           , science
                           , history
                          )
                     );

STUDENT UC_SUBJECT UC_SCORE
------- ---------- --------
David   ENGLISH          70
James   ENGLISH          80
Mary    ENGLISH          70
Linda   ENGLISH          90
Tom     ENGLISH          90
David   MATH             70
James   MATH             90
Mary    MATH             90
Linda   MATH             60
David   SCIENCE          80
James   SCIENCE          60
Mary    SCIENCE          50
Linda   SCIENCE          80
David   HISTORY          90
James   HISTORY          60
Mary    HISTORY          80
Linda   HISTORY          70
Tom     HISTORY          70

18 rows selected.

Compatibility

The SQL standard does not define the concept of unpivot.

For More Information

Refer to from clause.

sample clause

Function

It applies random sampling to the <table primary>.

Syntax

<sample clause> ::=
      TABLESAMPLE <left paren> percent_value [ PERCENT ROWS | PERCENT PAGES ] <right paren> [ <repeatable clause> ]

<repeatable clause> ::=
    REPEATABLE <left paren> seed_value <right paren>

Invocation and Access Rules

<sample clause>

Only real numbers greater than 0 and less than or equal to 100 are allowed for percent_value.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 0 PERCENT ROWS );

ERR-42000(16664): table sampling rate must be greater than 0 and less than or equal to 100 : 
SELECT COUNT(*) FROM t1 TABLESAMPLE( 0 PERCENT ROWS )
                                     *
ERROR at line 1:


gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 200 PERCENT ROWS );

ERR-42000(16664): table sampling rate must be greater than 0 and less than or equal to 100 : 
SELECT COUNT(*) FROM t1 TABLESAMPLE( 200 PERCENT ROWS )
                                     *
ERROR at line 1:


gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 100 PERCENT ROWS );

COUNT(*)
--------
 1000000

1 row selected.


gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
   99854

1 row selected.

If neither PERCENT ROWS nor PERCENT PAGES is specified, PERCENT ROWS is applied by default.

gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 );

COUNT(*)
--------
   99791

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

The percent_value uses up to two decimal places as the table sampling rate.

gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 12.345678 );

COUNT(*)
--------
  123804

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 12.34 % )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

<repeatable clause>

The seed_value is a native integer type.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 10000000000 );

ERR-22003(12075): data is outside the range of the data type to which the number is being converted


gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 100000000 );

COUNT(*)
--------
   99967

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % ) REPEATABLE( 100000000 )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan


gSQL> \EXPLAIN PLAN SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( -100000000 );

COUNT(*)
--------
  100109

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % ) REPEATABLE( -100000000 )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

Description

<sample clause> is a feature in SQL that allows queries to be performed by extracting only a sample subset of data from a base table or global temporary table.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100415

1 row selected.


gSQL> SELECT COUNT(*) FROM ( SELECT * FROM t1 ) TABLESAMPLE( 10 PERCENT ROWS );

ERR-42000(16663): table sampling can only be performed on a single base table or temporary table : 
SELECT COUNT(*) FROM ( SELECT * FROM t1 ) TABLESAMPLE( 10 PERCENT ROWS )
                       *
ERROR at line 1:


gSQL> SELECT COUNT(*) FROM v1 TABLESAMPLE( 10 PERCENT ROWS );

ERR-42000(16663): table sampling can only be performed on a single base table or temporary table : 
SELECT COUNT(*) FROM v1 TABLESAMPLE( 10 PERCENT ROWS )
                     *
ERROR at line 1:

If PERCENT ROWS is specified, row-level sampling is performed at the probability of percent_value; if PERCENT PAGES is specified, page-level sampling is performed at the probability of percent_value.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100415

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100173

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 20 PERCENT ROWS );

COUNT(*)
--------
  200285

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT PAGES );

COUNT(*)
--------
   96768

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 20 PERCENT PAGES );

COUNT(*)
--------
  202368

1 row selected.

The <sample clause> does not guarantee an exact number of result rows and evaluates by accessing the table directly, ignoring indexes.

gSQL> \EXPLAIN PLAN SELECT /*+ INDEX( t1 ) */ COUNT(*) FROM t1;

COUNT(*)
--------
 1000000

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      INDEX ACCESS ("T1", "IDX_T1")                           | (   1000000)          1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  READ INDEX COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan


gSQL> \EXPLAIN PLAN SELECT /*+ INDEX( t1 ) */ COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100081

1 row selected.

>>>  start print plan

< Execution Plan >
==================================================================================================
|  IDX  |  NODE DESCRIPTION                                            |                    ROWS |
--------------------------------------------------------------------------------------------------
|    0  |  SELECT STATEMENT                                            |                       1 |
|    1  |    QUERY BLOCK ("$QB_IDX_2")                                 |                       1 |
|    2  |      TABLE ACCESS ("T1")                                     |                       1 |
==================================================================================================

     1  -  TARGET : COUNT(*)
     2  -  ROW SAMPLING ( 10.00 % )
           READ COLUMN : NOTHING
           AGGREGATION : COUNT(*)

<<<  end print plan

The <sample clause> performs random sampling, so executing it without the REPEATABLE clause may produce different results each time.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
  100167

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
   99802

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS );

COUNT(*)
--------
   99732

1 row selected.

Different seed_values in the <repeatable clause> may produce different results.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 1 );

COUNT(*)
--------
   99756

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 1 );

COUNT(*)
--------
   99756

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) REPEATABLE ( 2 );

COUNT(*)
--------
  100361

1 row selected.

Examples

The following is an example of a SELECT statement using the <sample clause>.

gSQL> SELECT COUNT(*) FROM t1 TABLESAMPLE( 10 PERCENT ROWS ) WHERE c1 >= c2;

COUNT(*)
--------
  100290

1 row selected.

gSQL> SELECT COUNT(*) FROM t1 A TABLESAMPLE( 1 PERCENT ROWS ), t1 B TABLESAMPLE( 2 PERCENT ROWS ) WHERE A.c1 = B.c1;

COUNT(*)
--------
 2012379

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T613

Sampling

O

For More Information

Refer to query specification.

where clause

Function

It applies <search condition> to the result of <from clause>.

Syntax

<where clause> ::=
    WHERE <search condition>

Syntax Rules and Parameters

<where clause>

<search condition> which returns a boolean type is required after WHERE keyword.

Description

For more information about <where clause>, refer to Conditions.

Example

The following is an example of SELECT statement which uses <where clause>.

gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation = 'KOREA';

S_NAME                    S_NATION
------------------------- --------
Supplier#2                KOREA

1 row selected.

gSQL> SELECT s_name, ps_availqty, ps_supplycost FROM supplier, partsupp WHERE s_nation = 'KOREA' AND s_suppkey = ps_suppkey;

S_NAME                    PS_AVAILQTY PS_SUPPLYCOST
------------------------- ----------- -------------
Supplier#2                       8076        993.49
Supplier#2                       4069        357.84

2 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F441

Extended set function support

O

For More Information

Refer to query specification.

hierarchical query clause

Function

It specifies to retrieve the hierarchical model data in a hierarchy. 
It returns table records in a hierarchy of depth-first sequence by using the launch condition and sub-connectivity condition.

Syntax

<hierarchical query clause> ::= 
    <start with connect by clause> [ <order siblings by clause> ]

<start with connect by clause> ::= 
    <start with clause> <connect by clause>
    | <connect by clause> <start with clause>
    | <connect by clause>

<start with clause> ::=
    START WITH <start_with_condition>

<connect by clause> ::=
    CONNECT BY [NOCYCLE] <connect_by_condition>

<order siblings by clause> ::=
    ORDER SIBLINGS BY <ordering element> [ { <comma> <ordering element> }... ]

<ordering element> ::=
    <value expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]

<hierarchy expression> ::=
    LEVEL
    | CONNECT_BY_ISCYCLE
    | CONNECT_BY_ISLEAF
    | PRIOR <value expression>
    | CONNECT_BY_ROOT <value expression>
    | SYS_CONNECT_BY_PATH <left paren> <value expression> <comma> <character string literal> <right paren>

Invocation and Access Rules

It is supported in <query specification> statement, and the user should satisfy the access privilege of <query specification> to perform it.
For more information, refer to query specification.

Syntax Rules and Parameters

<hierarchical query clause>

<connect by clause> should be described.
<start with clause> or <order siblings by clause> is described when it is required.

<start with clause>

It specifies the condition of root record in data hierarchy.
When it is not specified, then all records of from clause become targets of root records.
It can be described only once within SELECT statement.

<connect by clause>

It describes the relation between the parent record and the child record.
It expresses the relation between the parent record and the child record by using PRIOR operator which representing the column value of the parent record.
If the condition of connecting the parent record and the child record is not specified by using PRIOR operator, then an infinite loop may occur.
It can be described only once within SELECT statement.

<order siblings by clause>

It specifies the order of fetching sibling records of the same parent records.

<hierarchy expression>

Result type of <hierarchy expression>

Expression

Result DataType

LEVEL

NATIVE_BIGINT

CONNECT_BY_ISCYCLE

NATIVE_BIGINT

CONNECT_BY_ISLEAF

NATIVE_BIGINT

PRIOR expr

expr의 DataType

CONNECT_BY_ROOT expr

expr의 DataType

SYS_CONNECT_BY_PATH( expr, literal )

VARCHAR(4000 characters)

<hierarchy expression> can be described in the following statements.

Expression\clause

FROM

START WITH

CONNECT BY

ORDER SIBLINGS BY

WHERE/

GROUP BY/

HAVING

ORDER BY/

SELECT TARGET

LEVEL

X

O

O

X

O

O

CONNECT_BY_ISCYCLE

X

X

X

X

O

O

CONNECT_BY_ISLEAF

X

X

X

X

O

O

PRIOR

X

X

O

X

O

O

CONNECT_BY_ROOT

X

X

X

X

O

O

SYS_CONNECT_BY_PATH

X

X

X

X

O

O

Whether <hierarchy expression> can be used as an argument of <hierarchy expression> is described in the following table.

Expression\Argument(expr)

LEVEL

CONNECT_BY_ISCYCLE

CONNECT_BY_ISLEAF

PRIOR

CONNECT_BY_ROOT

SYS_CONNECT_BY_PARTH

PRIOR expr

X

X

X

X

X

X

CONNECT_BY_ROOT expr

X

X

X

X

X

X

SYS_CONNECT_BY_PARTH(expr,literal)

O

O

O

O

O

O

Description

<hierarchical query clause> retrieves the hierarchical model data in a hierarchy. 
It returns table records in a hierarchy of depth-first sequence by using the launch condition and sub-connectivity condition.
When <hierarchical query clause> is described in SELECT, then it is processed in the following order.

  1. ON condition in FROM clause

  2. START WITH

  3. CONNECT BY

  4. WHERE

SELECT *
 FROM r_region
WHERE r_population > 10000000             3 Condition in WHERE clause
START WITH r_name = 'EARTH'               1 START WITH
CONNECT BY r_domain = PRIOR r_name        2 CONNECT BY
SELECT *
  FROM r_region INNER JOIN s_region 
       ON r_id = s_id                      1 Join condition in ON clause
 WHERE r_population > 10000000             4 Condition in WHERE clause
START WITH r_name = 'EARTH'                2 START WITH
CONNECT BY r_domain = PRIOR r_name         3 CONNECT BY
SELECT *
  FROM r_region, s_region
 WHERE r_population > 10000000             3 Condition in WHERE clause
   AND r_id = s_id                         3 Join condition in WHERE clause
START WITH r_name = 'EARTH'                1 START WITH
CONNECT BY r_domain = PRIOR r_name         2 CONNECT BY
SELECT *
  FROM r_region INNER JOIN s_region
       ON r_name = s_name                   1 Join condition in ON clause
 WHERE r_population > 10000000              4 Condition in WHERE clause
   AND r_id = s_id                          4 Join condition in WHERE clause
START WITH r_name = 'EARTH'                 2 START WITH
CONNECT BY r_domain = PRIOR r_name          3 CONNECT BY

<order siblings by clause>

It specifies the order of fetching sibling records of the same parent records within <hierarchical query clause>.
<order siblings by clause> is a statement distinct from <order by clause>.
gSQL> 
SELECT * FROM t1;

I1  I2
--- ----
A   null
AA  A   
AB  A   
fAA AA  
eAA AA  
bAA AA  
dAB AB  
cAB AB  
aAB AB  

9 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'A' 
CONNECT BY i2 = PRIOR i1
ORDER SIBLINGS BY i1;

LEVEL I1  I2
----- --- ----
    1 A   null
    2 AA  A   
    3 bAA AA  
    3 eAA AA  
    3 fAA AA  
    2 AB  A   
    3 aAB AB  
    3 cAB AB  
    3 dAB AB  

9 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'A'
CONNECT BY i2 = PRIOR i1
ORDER SIBLINGS BY i1
ORDER BY LEVEL;

LEVEL I1  I2  
----- --- ----
    1 A   null
    2 AA  A   
    2 AB  A   
    3 bAA AA  
    3 eAA AA  
    3 fAA AA  
    3 aAB AB  
    3 cAB AB  
    3 dAB AB  

9 rows selected.

<hierarchy expression>

The following are features of the hierarchy expression.

gSQL>
SELECT i1, i2
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = PRIOR i1;

I1    I2  
----- ----
X     null
XA    X   
XXA   XA  
XXXA  XXA 
XXXXA XXXA

5 rows selected.
gSQL> 
SELECT LEVEL, i1, i2
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

LEVEL I1    I2  
----- ----- ----
   1 X     null
   2 XA    X   
   3 XXA   XA  
   4 XXXA  XXA 
   5 XXXXA XXXA

5 rows selected.
gSQL> 
SELECT * FROM t1;

I1 I2  
-- ----
A  null
AA A   
AB A   
AC A   
AA AA  
AB AA  

6 rows selected.

gSQL> 
SELECT i1, i2, CONNECT_BY_ISCYCLE 
  FROM t1
START WITH i1 = 'A'
CONNECT BY NOCYCLE i2 = prior i1;

I1 I2   CONNECT_BY_ISCYCLE
-- ---- ------------------
A  null                  0
AA A                     1
AB AA                    0
AB A                     0
AC A                     0

5 rows selected.
gSQL> 
SELECT i1, i2, CONNECT_BY_ISLEAF
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1; 

I1    I2   CONNECT_BY_ISLEAF
----- ---- -----------------
X     null                 0
XA    X                    0
XXA   XA                   0
XXXA  XXA                  0
XXXXA XXXA                 1

5 rows selected.
gSQL> 
SELECT i1, i2, CONNECT_BY_ROOT i1
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

I1    I2   CONNECT_BY_ROOT I1
----- ---- ------------------
X     null X                 
XA    X    X                 
XXA   XA   X                 
XXXA  XXA  X                 
XXXXA XXXA X                 

5 rows selected.
gSQL> 
SELECT i1, i2, SYS_CONNECT_BY_PATH( i1, '/' )
  FROM t1
START WITH i1 = 'X'
CONNECT BY i2 = prior i1;

I1    I2   SYS_CONNECT_BY_PATH( I1, '/' )
----- ---- ------------------------------
X     null /X                            
XA    X    /X/XA                         
XXA   XA   /X/XA/XXA                     
XXXA  XXA  /X/XA/XXA/XXXA                
XXXXA XXXA /X/XA/XXA/XXXA/XXXXA          

5 rows selected.

Examples

The following is the result of retrieving record in emp table which will be used in the example of hierarchical query clause.

gSQL> 
SELECT * FROM emp;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Jackson Kelly  
Joe     Kelly  
Scott   Bill   
Larry   Bill   
Paul    Jackson
Bill    Bill   

8 rows selected.

The following is an example of when cycle occurs.

gSQL> 
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY mgr = PRIOR name
ORDER SIBLINGS BY name;

ERR-42000(16511): cycle detected while executing recursive WITH query

The following is an example of executing the query by using CONNECT BY NOCYCLE statement.

gSQL> 
SELECT *
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR    
------- -------
Kelly   null   
Bill    Kelly  
Larry   Bill   
Scott   Bill   
Jackson Kelly  
Paul    Jackson
Joe     Kelly  

7 rows selected.

The following is an example of retrieving the information about the hierarchical data by using the hierarchy expression.

gSQL> 
SELECT name, 
       mgr, 
       PRIOR name AS prior_mgr,
       LEVEL,
       CONNECT_BY_ISCYCLE AS iscycle,
       CONNECT_BY_ISLEAF AS isleaf,
       CONNECT_BY_ROOT mgr AS root_mgr,
       SYS_CONNECT_BY_PATH( mgr, '/' ) AS path
  FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE mgr = PRIOR name
ORDER SIBLINGS BY name;

NAME    MGR     PRIOR_MGR LEVEL ISCYCLE ISLEAF ROOT_MGR PATH           
------- ------- --------- ----- ------- ------ -------- ---------------
Kelly   null    null          1       0      0 null     /              
Bill    Kelly   Kelly         2       1      0 null     //Kelly        
Larry   Bill    Bill          3       0      1 null     //Kelly/Bill   
Scott   Bill    Bill          3       0      1 null     //Kelly/Bill   
Jackson Kelly   Kelly         2       0      0 null     //Kelly        
Paul    Jackson Jackson       3       0      1 null     //Kelly/Jackson
Joe     Kelly   Kelly         2       0      1 null     //Kelly        

7 rows selected.

group by clause

Function

It specifies the grouped table of which <group by clause> was applied to the result processed by the previous statements.

Syntax

<group by clause> ::=
    GROUP BY [<set quantifier>] <grouping element list>

<set quantifier> ::=
    ALL
    | DISTINCT

<grouping element list> ::=
    <grouping element> [ { , <grouping element> } ... ]

<grouping element> ::=
      <ordinary grouping set>
    | <rollup list>
    | <cube list>
    | <grouping sets specification>
    | <empty grouping set>

<ordinary grouping set> ::=
      <grouping column reference>
    | <left paren> <grouping column reference list> <right paren>

<grouping column reference> ::=
    <column reference>
    | <select list alias>
    | <value expression>

<grouping column reference list> ::=
    <grouping column reference> [ { , <grouping column reference> }... ]

<empty grouping set> ::=
    <left paren> <right paren>

<rollup list> ::=
    ROLLUP <left paren> <ordinary grouping set list> <right paren>

<ordinary grouping set list> ::=
    <ordinary grouping set> [ { , <ordinary grouping set> }... ]

<cube list> ::=
    CUBE <left paren> <ordinary grouping set list> <right paren>

<grouping sets specification> ::=
    GROUPING SETS <left paren> <grouping set list> <right paren>

<grouping set list> ::=
    <grouping set> [ { , <grouping set> }... ]

<grouping set> ::=
    <ordinary grouping set>
  | <rollup list>
  | <cube list>
  | <grouping sets specification>
  | <empty grouping set>

Invocation and Access Rules

Any separate access privilege is not required for a user to perform <group by clause>.

Syntax Rules and Parameters

<ordinary grouping set>

It consists of one or more <grouping column reference>.
It does not support LONG type (LONG VARCHAR, LONG VARBINARY).

• SELECT c1, sum(c2) FROM t1 GROUP BY c1;
• SELECT sum(c1) FROM t1 GROUP BY NULL;

<empty grouping set>

It can be specified by using only parentheses.

• SELECT sum(c1) FROM t1 GROUP BY ();

Description

<set quantifier>

It is ALL or DISTINCT. If <set quantifier> is not specified, then it is ALL. 
If DISTINCT is specified, then it drops the duplicately defined group.

<grouping element list>

It groups <grouping element list> specified in <group by clause> into a GROUPING SET. If all values of <grouping element> in GROUPING SET are matched, it is processed as the same group.

<ordinary grouping set>

<grouping column reference> or <left paren><grouping column reference list><right paren> can appear in <ordinary grouping set>.

<group column reference list> is a list of <grouping column reference>, and <column reference> or <value expression> can appear in <grouping column reference>.

<rollup list>

ROLLUP statement is used together with <ordinary grouping set list>. If n number of <ordinary grouping set> are listed in <ordinary grouping set list>, then it groups <ordinary grouping set> into n groups, then it groups <ordinary grouping set> into n-1 groups, and it groups the next grouping set into n-2 groups. It keeps grouping in this way, and finally it returns the result of grouping into <empty grouping sets> group. 
Therefore, ( n + 1 ) groups are created in total.
If it is used together with SUM, then ROLLUP can calculate the most specific small sum as well as the total sum.

<cube list>

CUBE statement is used together with <ordinary grouping set list>. It groups <ordinary grouping set> into all kinds of combinations. If the number of <ordinary grouping set> is n, then 2n groups are created in total.

<grouping sets specification>

GROUPING SETS can specify all required combinations of groups. ROLLUP and CUBE combines groups according to each statement. However, GROUPING SETS can select required combinations of groups only.

<empty grouping set>

All records in <empty grouping set> are configured into a single group.
• SELECT sum(c1), sum(c2) FROM t1 GROUP BY ();

Example

The following is an example of SELECT statement which uses GROUP BY clause.

gSQL> SELECT c_nation, COUNT(c_name) FROM customer GROUP BY c_nation;

C_NATION      COUNT(C_NAME)
------------- -------------
UNITED STATES             1
CANADA                    1
KOREA                     2
GERMANY                   1

4 rows selected.

gSQL> SELECT COUNT(c_name) FROM customer GROUP BY NULL;

COUNT(C_NAME)
-------------
            5

1 row selected.

gSQL> SELECT COUNT(c_name) FROM customer GROUP BY ();

COUNT(C_NAME)
-------------
            5

1 row selected.

The following is an example of using a <select list alias> as a grouping key.

gSQL> SELECT o_orderdate || ' : ' || o_custkey AS date_cust, COUNT(*) 
        FROM orders 
       GROUP BY date_cust 
      HAVING COUNT(*) > 2;


DATE_CUST           COUNT(*)
------------------- --------
1995-06-22 : 114637        3
1994-09-22 : 61855         3
1994-10-26 : 90070         3
1992-09-10 : 108091        3
1992-12-24 : 131530        3
1995-05-12 : 64672         3
1997-09-20 : 8098          3
1997-04-29 : 22942         3
1992-05-31 : 130456        3
1993-04-15 : 10405         3
1992-02-21 : 11939         3
1996-03-31 : 98120         3

12 rows selected.

The following is an example of SELECT statement which uses GROUP BY ROLLUP clause.

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

YEAR QUARTER MONTH        SUM
---- ------- ------- --------
2001 2001-01 2001-01  1631.26
2001 2001-01 2001-02   922.03
2001 2001-01 2001-03  1625.59
2001 2001-01 null     4178.88
2001 2001-02 2001-04  2087.83
2001 2001-02 2001-05  1168.99
2001 2001-02 2001-06  1778.76
2001 2001-02 null     5035.58
2001 2001-03 2001-07  1604.74
2001 2001-03 2001-08  1841.42
2001 2001-03 2001-09  1953.56
2001 2001-03 null     5399.72
2001 2001-04 2001-10  2117.61
2001 2001-04 2001-11  1862.95
2001 2001-04 2001-12  1880.53
2001 2001-04 null     5861.09
2001 null    null    20475.27
null null    null    20475.27

The following is an example of SELECT statement which uses GROUP BY CUBE clause.

\EXPLAIN PLAN
SELECT 
       channels.channel_desc as channel 
     , countries.country_iso_code as country
     , SUM(amount_sold) as sold_sum
  FROM sales, customers, times, channels, countries
 WHERE sales.time_id = times.time_id 
   AND sales.cust_id = customers.cust_id 
   AND sales.channel_id = channels.channel_id 
   AND customers.country_id = countries.country_id
   AND channels.channel_desc IN ('Direct Sales', 'Internet')
   AND times.calendar_month_desc ='2001-09'
   AND countries.country_iso_code IN ('US','FR')
   AND sales.cust_id < 1000 AND sales.prod_id > 142 AND sales.channel_id > 2
   AND customers.cust_id < 1000
 GROUP BY CUBE(channels.channel_desc, countries.country_iso_code)
 ORDER BY 1,2;

CHANNEL      COUNTRY SOLD_SUM
------------ ------- --------
Direct Sales FR         59.91
Direct Sales US        662.47
Direct Sales null      722.38
Internet     FR         29.62
Internet     US        382.56
Internet     null      412.18
null         FR         89.53
null         US       1045.03
null         null     1134.56

9 rows selected.

The following is an example of SELECT statement which uses GROUP BY GROUPING SETS clause.

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

YEAR QUARTER MONTH        SUM
---- ------- ------- --------
2001 2001-01 2001-01  1631.26
2001 2001-01 2001-02   922.03
2001 2001-01 2001-03  1625.59
2001 2001-02 2001-04  2087.83
2001 2001-02 2001-05  1168.99
2001 2001-02 2001-06  1778.76
2001 2001-03 2001-07  1604.74
2001 2001-03 2001-08  1841.42
2001 2001-03 2001-09  1953.56
2001 2001-04 2001-10  2117.61
2001 2001-04 2001-11  1862.95
2001 2001-04 2001-12  1880.53
2001 null    null    20475.27
null null    null    20475.27

14 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T431

Extended grouping capabilities

O

T432

Nested and concatenated GROUPING SETS

O

T434

GROUP BY DISTINCT

O

For More Information

Refer to the following.

having clause

Function

It specifies grouped tables having removed groups which do not satisfy <search condition>.

Syntax

<having clause> ::=
    HAVING <search condition>

Invocation and Access Rules

Any separate access privilege is not required for a user to perform <having clause>.

Syntax Rules and Parameters

<having clause>

Description

<having clause>

<having clause> specifies search conditions for the grouped data.

Generally, it is used together with <group by clause>. When <having clause> is used without <group by clause>, it is considered as if <empty grouping set> exists.

<column reference> specified in <group by clause> can be specified in <having clause>. 
The columns which are not specified in <group by clause> can be specified by using aggregate functions.

Example

The following is an example of SELECT statement which uses <having clause>.

gSQL> SELECT c_nation, COUNT(c_name) FROM customer GROUP BY c_nation HAVING COUNT(c_name) > 1;

C_NATION COUNT(C_NAME)
-------- -------------
KOREA                2

1 row selected.

gSQL> SELECT COUNT(c_name) FROM customer HAVING COUNT(c_name) > 1;

COUNT(C_NAME)
-------------
            5

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T301

Functional dependencies

O

For More Information

Refer to the following.

window clause

Function

It defines the execution range of the window function described in select list and order by clause.

Syntax

<window clause> ::=
     WINDOW <window definition list>

<window definition list> ::=
     <window definition> [ { <comma> <window definition> }... ]

<window definition> ::=
     <new window name> AS <window specification>

<new window name> ::=
     <window name>

<window specification> ::=
     <left paren> <window specification details> <right paren>

<window specification details> ::=
     [ <existing window name> ]
          [ <window partition clause> ]
          [ <window order clause> ]
          [ <window frame clause> ]

<existing window name> ::=
     <window name>

<window partition clause> ::=
     PARTITION BY <window partition column reference list>

<window partition column reference list> ::=
     <window partition column reference>
          [ { <comma> <window partition column reference> }... ]

<window partition column reference> ::=
     <column reference>

<window order clause> ::=
     ORDER BY <sort specification list>

<sort specification list> ::=
     <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
     <sort key> [ <ordering specification> ] [ <null ordering> ]

<sort key> ::=
     <value expression>

<ordering specification> ::=
       ASC
     | DESC

<null ordering> ::=
      NULLS FIRST
    | NULLS LAST

<window frame clause> ::=
     <window frame units> <window frame extent>
          [ <window frame exclusion> ]

<window frame units> ::=
       ROWS
     | RANGE
     | GROUPS

<window frame extent> ::=
       <window frame start>
     | <window frame between>

<window frame start> ::=
       UNBOUNDED PRECEDING
     | <window frame preceding>
     | CURRENT ROW

<window frame preceding> ::=
     <unsigned value specification> PRECEDING

<window frame between> ::=
     BETWEEN <window frame bound 1> AND <window frame bound 2>

<window frame bound 1> ::=
     <window frame bound>

<window frame bound 2> ::=
     <window frame bound>

<window frame bound> ::=
       <window frame start>
     | UNBOUNDED FOLLOWING
     | <window frame following>

<window frame following> ::=
     <unsigned value specification> FOLLOWING

<window frame exclusion> ::=
       EXCLUDE CURRENT ROW
     | EXCLUDE GROUP
     | EXCLUDE TIES
     | EXCLUDE NO OTHERS

Invocation and Access Rules

The access privilege for a column is required if the column exist in a window clause.

Syntax Rules and Parameters

<window clause>

The window function is not allowed in a window clause.

<window definition list>

It can define multiple <window definition>.

<window definition>

It describes the execution range of window function with <new window name>.
<new window name> should not be a duplicate in <window clause>. 
<new window name> can be referred in over clause in the window function.
SELECT SUM(i2) OVER w1
  FROM t1
WINDOW w1 AS ( PARTITION BY i1 ORDER BY i2 );

<window specification>

It defines the execution range of window function.

It can redefine <window specification> by referring to <existing window name> and adding it to the predefined information.
<existing window name> can refer to <new window name> which was predefined in <window definition list> only.
• Referred in window clause

SELECT SUM(i2) OVER w2
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 ),
       w2 AS ( w1 ROWS BETWEEN UNBOUNDED PRECEDING  <---
                           AND CURRENT ROW );

• Referred in over clause of window function

SELECT SUM(i2) OVER ( w1 ROWS BETWEEN UNBOUNDED PRECEDING  <---
                                  AND CURRENT ROW )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 );

When redefining <window specification>by referring to <existing window name>

• <window partition clause> is not allowed in the redefinition.

SELECT SUM(i2) OVER ( w1 PARTITION BY i1 )  <--- ( X )
  FROM t1
WINDOW w1 AS ( );

• If order by clause is described in <existing window name>, 
  order by clause is not allowed in the redefinition.

SELECT SUM(i2) OVER ( w1 ORDER BY i3 ) <--- ( X )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 );

• window frame clause is not allowed in <existing window name>.

SELECT SUM(i2) OVER ( w1 )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2
               ROWS BETWEEN UNBOUNDED PRECEDING  <--- ( X )
                        AND CURRENT ROW );

<window frame start>

If frame end is omitted, then <window frame start> is the same as <window frame start> AND CURRENT ROW.

<window frame between>

<window frame following> / <window frame preceding>

offset PRECEDING / offset FOLLOWING

Description

WINDOW clause describes the execution range of window function described in select list and  order by clause.
<window partition clause> divides groups.
<window order clause> sorts the records in the group.
<window frame clause> defines the range of window function's target record for the sorted records in the group.
WINDOW clause is executed for the result sets after FROM, WHERE, GROUP BY, HAVING clauses are executed.
When using an aggregate, GROUP BY, HAVING clauses in a query, then describe the group column instead of the column from the original table in WINDOW clause.

<window specification>

It describes the execution range of window function for each record.
It can redefine <window specification> by referring to <existing window name> and adding it to the predefined information.
SELECT SUM(i2) OVER ( w1 
                      ORDER BY i2
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1 ),
       w2 AS ( w1 ORDER BY i3 );

   → It is the same statement.

SELECT SUM(i2) OVER ( PARTITION BY i1 
                      ORDER BY i2
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
  FROM t1
WINDOW w1 AS ( PARTITION BY i1 ),
       w2 AS ( PARTITION BY i1
               ORDER BY i3 );
When referring to <window name> wname in window function OVER clause, then OVER wname and OVER ( wname ) is not same.
* Referring to <window specification> information defined with wname

ex) Referring to w1
SELECT SUM(i2) OVER w1 
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW );
* It redefines <window specification> by adding it to the predefined information,
  and it can not define <window frame clause> in the existing information.

ex) Referring to w1 
SELECT SUM(i2) OVER ( w1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2 );

ex) Referring to w1 ( Error : It can not define <window frame clause> in the existing information. )
SELECT SUM(i2) OVER ( w1 ) 
  FROM t1
WINDOW w1 AS ( PARTITION BY i1
               ORDER BY i2
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW );  <---

<window partition clause>

It divides the query result set into groups based on <window partition column reference list> by using PARTITION BY.
If this clause is omitted, then the function processes all rows in the result set as a single group.
gSQL> 
SELECT orderdate,
       orderkey,
       totalprice, 
       SUM( totalprice ) OVER( PARTITION BY orderdate ) AS SUM_OVER_RESULT 
FROM orders;

ORDERDATE  ORDERKEY TOTALPRICE SUM_OVER_RESULT
---------- -------- ---------- ---------------
1998-07-24     1730     204656          520630  
1998-07-24    17056     289620          520630  
1998-07-24    19937      26354          520630      partition 1
----------------------------------------------------------------------
1998-07-25     2400     150304          368523  
1998-07-25    11204      27165          368523  
1998-07-25    11938     191054          368523      partition 2 
----------------------------------------------------------------------
1998-07-26    35655      13698          362691  
1998-07-26    53377     185930          362691  
1998-07-26    55010     163063          362691      partition 3 
----------------------------------------------------------------------

9 rows selected.
gSQL> 
SELECT orderdate,
       orderkey,
       totalprice,
       SUM( totalprice ) OVER() AS SUM_OVER_RESULT 
  FROM orders;

ORDERDATE  ORDERKEY TOTALPRICE SUM_OVER_RESULT
---------- -------- ---------- ---------------
1998-07-24     1730     204656         1251844  
1998-07-24    17056     289620         1251844  
1998-07-24    19937      26354         1251844  
1998-07-25     2400     150304         1251844  
1998-07-25    11204      27165         1251844  
1998-07-25    11938     191054         1251844  
1998-07-26    35655      13698         1251844  
1998-07-26    53377     185930         1251844  
1998-07-26    55010     163063         1251844      partition 1 
----------------------------------------------------------------------

9 rows selected.

<window order clause>

It specifies the method of sorting the data in the partition based on <sort specification list> by using ORDER BY.

<window frame clause>

It specifies window frame which is the record range of window function's target.
window frame is the record range related to each row (current row) of the query.
The target of window frame is records sorted in the current partition.
window frame can specify the scope (ROWS/ RANGE/ GROUPS), starting point and ending point, and excluded records.
If it is omitted, then it applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          180000
2000-01-01   102 3088163      42000          269000   <- peer ( custkey values are same. )
2000-01-01   103 3088163      47000          269000      peer
2000-01-01   104 3088165     217000          486000
2000-01-01   105 3088167     108000          734000   <- peer ( custkey values are same. )
2000-01-01   106 3088167      60000          734000      peer
2000-01-01   107 3088167      80000          734000      peer
...
15 rows selected.
gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     269000 1+2+3 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     734000 1+2+3+4+5+6+7 
2000-01-01   106 3088167      60000 6     734000 1+2+3+4+5+6+7 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     816000 1+2+3+4+5+6+7+8+9+10 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     594000 1+2+3+4+5
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.

<window frame units>

ROWS/ RANGE/ GROUPS are units of window frame scope.

<window frame extent>

It defines window frame start (the starting point) and window frame end (ending point).

<window frame exclusion>

It defines records to exclude from window frame.

Example of Using <window frame extent>

# ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5
2000-01-01   106 3088167      60000 6     654000 1+2+3+4+5+6 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     486000 1+2+3+4
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.
# RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     269000 1+2+3 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     734000 1+2+3+4+5+6+7 
2000-01-01   106 3088167      60000 6     734000 1+2+3+4+5+6+7 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     816000 1+2+3+4+5+6+7+8+9+10 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     594000 1+2+3+4+5
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.
# GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     269000 1+2+3 
2000-01-01   103 3088163      47000 3     269000 1+2+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     734000 1+2+3+4+5+6+7 
2000-01-01   106 3088167      60000 6     734000 1+2+3+4+5+6+7 
2000-01-01   107 3088167      80000 7     734000 1+2+3+4+5+6+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     816000 1+2+3+4+5+6+7+8+9+10 
2000-01-01   110 3088170      20000 10     816000 1+2+3+4+5+6+7+8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2
2000-03-03   302 3088161      42000 2     222000 1+2
2000-03-03   303 3088165      47000 3     269000 1+2+3
2000-03-03   304 3088167     217000 4     594000 1+2+3+4+5
2000-03-03   305 3088167     108000 5     594000 1+2+3+4+5

15 rows selected.
# ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN 1 PRECEDING 
                                        AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     269000 1+2+3 
2000-01-01   102 3088163      42000 2     486000 1+2+3+4 
2000-01-01   103 3088163      47000 3     414000 2+3+4+5 
2000-01-01   104 3088165     217000 4     432000 3+4+5+6 
2000-01-01   105 3088167     108000 5     465000 4+5+6+7 
2000-01-01   106 3088167      60000 6     280000 5+6+7+8 
2000-01-01   107 3088167      80000 7     202000 6+7+8+9 
2000-01-01   108 3088169      32000 8     162000 7+8+9+10 
2000-01-01   109 3088170      30000 9      82000 8+9+10
2000-01-01   110 3088170      20000 10      50000 9+10
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     269000 1+2+3 
2000-03-03   302 3088161      42000 2     486000 1+2+3+4 
2000-03-03   303 3088165      47000 3     414000 2+3+4+5 
2000-03-03   304 3088167     217000 4     372000 3+4+5 
2000-03-03   305 3088167     108000 5     325000 4+5 

15 rows selected.
# RANGE BETWEEN 1 PRECEDING AND 2 FOLLOWING

#####################################################
# When sorting ORDER BY column in ASC order 
#####################################################

 • 1 PRECEDING 
   -->   The value above ( sortkey value of the current row - 1 )
       = The value above ( custkey - 1 )

 • 2 FOLLOWING
   -->   The value below ( sortkey value of the current row + 2 )
       = The value below ( custkey + 2 )

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN 1 PRECEDING 
                                         AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     269000 1+2+3 
2000-01-01   102 3088163      42000 2     306000 2+3+4 
2000-01-01   103 3088163      47000 3     306000 2+3+4 
2000-01-01   104 3088165     217000 4     465000 4+5+6+7 
2000-01-01   105 3088167     108000 5     280000 5+6+7+8 
2000-01-01   106 3088167      60000 6     280000 5+6+7+8 
2000-01-01   107 3088167      80000 7     280000 5+6+7+8 
2000-01-01   108 3088169      32000 8      82000 8+9+10 
2000-01-01   109 3088170      30000 9      82000 8+9+10 
2000-01-01   110 3088170      20000 10      82000 8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     222000 1+2 
2000-03-03   302 3088161      42000 2     222000 1+2 
2000-03-03   303 3088165      47000 3     372000 3+4+5 
2000-03-03   304 3088167     217000 4     325000 4+5 
2000-03-03   305 3088167     108000 5     325000 4+5 

15 rows selected.


#####################################################
# When sorting ORDER BY column in DESC order 
#####################################################

 • 1 PRECEDING 
   -->   The value below ( sortkey value of the current row + 1 )
       = The value below ( custkey + 1 )

 • 2 FOLLOWING
   -->   The value above ( sortkey value of the current row - 2 )
       = The value above ( custkey - 2 )

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey DESC
                               RANGE BETWEEN 1 PRECEDING 
                                         AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   109 3088170      30000 1      82000 1+2+3 
2000-01-01   110 3088170      20000 2      82000 1+2+3 
2000-01-01   108 3088169      32000 3     330000 1+2+3+4+5+6 
2000-01-01   105 3088167     108000 4     465000 4+5+6+7 
2000-01-01   106 3088167      60000 5     465000 4+5+6+7 
2000-01-01   107 3088167      80000 6     465000 4+5+6+7 
2000-01-01   104 3088165     217000 7     306000 7+8+9 
2000-01-01   102 3088163      42000 8     269000 8+9+10 
2000-01-01   103 3088163      47000 9     269000 8+9+10 
2000-01-01   101 3088161     180000 10     180000 10 
-------------------------------------------------------------------------
2000-03-03   304 3088167     217000 1     372000 1+2+3 
2000-03-03   305 3088167     108000 2     372000 1+2+3 
2000-03-03   303 3088165      47000 3      47000 3 
2000-03-03   301 3088161     180000 4     222000 4+5 
2000-03-03   302 3088161      42000 5     222000 4+5 

15 rows selected.
# GROUPS BETWEEN 1 PRECEDING AND 2 FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN 1 PRECEDING 
                                          AND 2 FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     486000 1+2+3+4 
2000-01-01   102 3088163      42000 2     734000 1+2+3+4+5+6+7 
2000-01-01   103 3088163      47000 3     734000 1+2+3+4+5+6+7 
2000-01-01   104 3088165     217000 4     586000 2+3+4+5+6+7+8 
2000-01-01   105 3088167     108000 5     547000 4+5+6+7+8+9+10 
2000-01-01   106 3088167      60000 6     547000 4+5+6+7+8+9+10 
2000-01-01   107 3088167      80000 7     547000 4+5+6+7+8+9+10 
2000-01-01   108 3088169      32000 8     330000 5+6+7+8+9+10 
2000-01-01   109 3088170      30000 9      82000 8+9+10 
2000-01-01   110 3088170      20000 10      82000 8+9+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     594000 1+2+3+4+5 
2000-03-03   302 3088161      42000 2     594000 1+2+3+4+5 
2000-03-03   303 3088165      47000 3     594000 1+2+3+4+5 
2000-03-03   304 3088167     217000 4     372000 3+4+5 
2000-03-03   305 3088167     108000 5     372000 3+4+5 

15 rows selected.
# ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN CURRENT ROW 
                                        AND UNBOUNDED FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          816000
2000-01-01   102 3088163      42000          636000
2000-01-01   103 3088163      47000          594000
2000-01-01   104 3088165     217000          547000
2000-01-01   105 3088167     108000          330000
2000-01-01   106 3088167      60000          222000
2000-01-01   107 3088167      80000          162000
2000-01-01   108 3088169      32000           82000
2000-01-01   109 3088170      30000           50000
2000-01-01   110 3088170      20000           20000
2000-03-03   301 3088161     180000          594000
2000-03-03   302 3088161      42000          414000
2000-03-03   303 3088165      47000          372000
2000-03-03   304 3088167     217000          325000
2000-03-03   305 3088167     108000          108000

15 rows selected.
# RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN CURRENT ROW 
                                         AND UNBOUNDED FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          816000
2000-01-01   102 3088163      42000          636000
2000-01-01   103 3088163      47000          636000
2000-01-01   104 3088165     217000          547000
2000-01-01   105 3088167     108000          330000
2000-01-01   106 3088167      60000          330000
2000-01-01   107 3088167      80000          330000
2000-01-01   108 3088169      32000           82000
2000-01-01   109 3088170      30000           50000
2000-01-01   110 3088170      20000           50000
2000-03-03   301 3088161     180000          594000
2000-03-03   302 3088161      42000          594000
2000-03-03   303 3088165      47000          372000
2000-03-03   304 3088167     217000          325000
2000-03-03   305 3088167     108000          325000

15 rows selected.
# GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN CURRENT ROW 
                                          AND UNBOUNDED FOLLOWING ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE SUM_OVER_RESULT
---------- ----- ------- ---------- ---------------
2000-01-01   101 3088161     180000          816000
2000-01-01   102 3088163      42000          636000
2000-01-01   103 3088163      47000          636000
2000-01-01   104 3088165     217000          547000
2000-01-01   105 3088167     108000          330000
2000-01-01   106 3088167      60000          330000
2000-01-01   107 3088167      80000          330000
2000-01-01   108 3088169      32000           82000
2000-01-01   109 3088170      30000           50000
2000-01-01   110 3088170      20000           50000
2000-03-03   301 3088161     180000          594000
2000-03-03   302 3088161      42000          594000
2000-03-03   303 3088165      47000          372000
2000-03-03   304 3088167     217000          325000
2000-03-03   305 3088167     108000          325000

15 rows selected.

Example of Using <window frame exclusion>

# ROWS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW
                               EXCLUDE CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null 
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     222000 1+2 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     594000 1+2+3+4+5 
2000-01-01   107 3088167      80000 7     654000 1+2+3+4+5+6 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     796000 1+2+3+4+5+6+7+8+9 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2     180000 1 
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     486000 1+2+3+4 

15 rows selected.
# RANGE

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW
                               EXCLUDE CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     227000 1+3 
2000-01-01   103 3088163      47000 3     222000 1+2 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     626000 1+2+3+4+6+7 
2000-01-01   106 3088167      60000 6     674000 1+2+3+4+5+7 
2000-01-01   107 3088167      80000 7     654000 1+2+3+4+5+6 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     786000 1+2+3+4+5+6+7+8+10 
2000-01-01   110 3088170      20000 10     796000 1+2+3+4+5+6+7+8+9 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1      42000 2 
2000-03-03   302 3088161      42000 2     180000 1 
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     377000 1+2+3+5 
2000-03-03   305 3088167     108000 5     486000 1+2+3+4 

15 rows selected.
# GROUPS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW
                               EXCLUDE CURRENT ROW ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     227000 1+3 
2000-01-01   103 3088163      47000 3     222000 1+2 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     626000 1+2+3+4+6+7 
2000-01-01   106 3088167      60000 6     674000 1+2+3+4+5+7 
2000-01-01   107 3088167      80000 7     654000 1+2+3+4+5+6 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     786000 1+2+3+4+5+6+7+8+10 
2000-01-01   110 3088170      20000 10     796000 1+2+3+4+5+6+7+8+9 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1      42000 2 
2000-03-03   302 3088161      42000 2     180000 1 
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     377000 1+2+3+5 
2000-03-03   305 3088167     108000 5     486000 1+2+3+4 

15 rows selected.
# ROWS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW
                               EXCLUDE GROUP ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     180000 1 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     486000 1+2+3+4 
2000-01-01   107 3088167      80000 7     486000 1+2+3+4 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     766000 1+2+3+4+5+6+7+8 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2       null
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     269000 1+2+3 

15 rows selected.
# RANGE

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW
                               EXCLUDE GROUP ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     180000 1 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     486000 1+2+3+4 
2000-01-01   107 3088167      80000 7     486000 1+2+3+4 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     766000 1+2+3+4+5+6+7+8 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2       null
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     269000 1+2+3 

15 rows selected.
# GROUPS

gSQL> SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW
                               EXCLUDE GROUP ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1       null
2000-01-01   102 3088163      42000 2     180000 1 
2000-01-01   103 3088163      47000 3     180000 1 
2000-01-01   104 3088165     217000 4     269000 1+2+3 
2000-01-01   105 3088167     108000 5     486000 1+2+3+4 
2000-01-01   106 3088167      60000 6     486000 1+2+3+4 
2000-01-01   107 3088167      80000 7     486000 1+2+3+4 
2000-01-01   108 3088169      32000 8     734000 1+2+3+4+5+6+7 
2000-01-01   109 3088170      30000 9     766000 1+2+3+4+5+6+7+8 
2000-01-01   110 3088170      20000 10     766000 1+2+3+4+5+6+7+8 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1       null
2000-03-03   302 3088161      42000 2       null
2000-03-03   303 3088165      47000 3     222000 1+2 
2000-03-03   304 3088167     217000 4     269000 1+2+3 
2000-03-03   305 3088167     108000 5     269000 1+2+3 

15 rows selected.
# ROWS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               ROWS BETWEEN UNBOUNDED PRECEDING 
                                        AND CURRENT ROW
                               EXCLUDE TIES ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     227000 1+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5 
2000-01-01   106 3088167      60000 6     546000 1+2+3+4+6 
2000-01-01   107 3088167      80000 7     566000 1+2+3+4+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     786000 1+2+3+4+5+6+7+8+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1 
2000-03-03   302 3088161      42000 2      42000 2 
2000-03-03   303 3088165      47000 3     269000 1+2+3 
2000-03-03   304 3088167     217000 4     486000 1+2+3+4 
2000-03-03   305 3088167     108000 5     377000 1+2+3+5 

15 rows selected.
# RANGE

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               RANGE BETWEEN UNBOUNDED PRECEDING 
                                         AND CURRENT ROW
                               EXCLUDE TIES ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     227000 1+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5 
2000-01-01   106 3088167      60000 6     546000 1+2+3+4+6 
2000-01-01   107 3088167      80000 7     566000 1+2+3+4+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     786000 1+2+3+4+5+6+7+8+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1 
2000-03-03   302 3088161      42000 2      42000 2 
2000-03-03   303 3088165      47000 3     269000 1+2+3 
2000-03-03   304 3088167     217000 4     486000 1+2+3+4 
2000-03-03   305 3088167     108000 5     377000 1+2+3+5 

15 rows selected.
# GROUPS

gSQL> 
SELECT orderdate AS O_DATE,
       orderkey AS O_KEY,
       custkey,
       totalprice,
       SUM( totalprice ) OVER( PARTITION BY orderdate 
                               ORDER BY custkey
                               GROUPS BETWEEN UNBOUNDED PRECEDING 
                                          AND CURRENT ROW
                               EXCLUDE TIES ) AS SUM_OVER_RESULT 
  FROM orders;

O_DATE     O_KEY CUSTKEY TOTALPRICE    SUM_OVER_RESULT
---------- ----- ------- ----------    ---------------
2000-01-01   101 3088161     180000 1     180000 1 
2000-01-01   102 3088163      42000 2     222000 1+2 
2000-01-01   103 3088163      47000 3     227000 1+3 
2000-01-01   104 3088165     217000 4     486000 1+2+3+4 
2000-01-01   105 3088167     108000 5     594000 1+2+3+4+5 
2000-01-01   106 3088167      60000 6     546000 1+2+3+4+6 
2000-01-01   107 3088167      80000 7     566000 1+2+3+4+7 
2000-01-01   108 3088169      32000 8     766000 1+2+3+4+5+6+7+8 
2000-01-01   109 3088170      30000 9     796000 1+2+3+4+5+6+7+8+9 
2000-01-01   110 3088170      20000 10     786000 1+2+3+4+5+6+7+8+10 
-------------------------------------------------------------------------
2000-03-03   301 3088161     180000 1     180000 1 
2000-03-03   302 3088161      42000 2      42000 2 
2000-03-03   303 3088165      47000 3     269000 1+2+3 
2000-03-03   304 3088167     217000 4     486000 1+2+3+4 
2000-03-03   305 3088167     108000 5     377000 1+2+3+5 

15 rows selected.

Example

gSQL> 
SELECT item_no,
       sales_date,
       sales,
       SUM( sales ) OVER W1 cumulative_sales, 
       AVG( sales ) OVER w1 avg_sales
  FROM store
WINDOW w1 AS ( PARTITION BY item_no
               ORDER BY sales_date
               ROWS BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW );

ITEM_NO SALES_DATE SALES CUMULATIVE_SALES AVG_SALES
------- ---------- ----- ---------------- ---------
    100 2001-01-01   150              150       150
    100 2001-01-02   100              250       125
    100 2001-01-03   170              420       140
    100 2001-01-04    90              510     127.5
    100 2001-01-05   200              710       142
    235 2001-01-01    70               70        70
    235 2001-01-02   130              200       100
    235 2001-01-03   190              390       130
    235 2001-01-04   150              540       135
    235 2001-01-05    50              590       118

10 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T611

Elementary OLAP operations

X

T612

Advanced OLAP operations

X

T301

Functional dependencies

X

T620

WINDOW clause: GROUPS option

O

For More Information

Refer to the following.

order by clause

Function

It specifies the sorting order of the query results.

Syntax

<order by clause> ::=
    ORDER BY <sort specification list>

<sort specification list> ::=
    <sort specification> [ { <comma> <sort specification> }... ]

<sort specification> ::=
    <sort key> [ <ordering specification> ] [ <null ordering> ]

<sort key> ::=
    <value expression>

<ordering specification> ::=
      ASC
    | DESC

<null ordering> ::=
      NULLS FIRST
    | NULLS LAST

Invocation and Access Rules

The access privilege for a column is required if the column exist in a sort key specified for sorting.

Syntax Rules and Parameters

<order by clause>

<sort specification list>

<sort key>

Description

<order by clause>

<order by clause> specifies a method to sort the query results.

<sort key> can be listed in <order by clause> by using a comma (,), and <sort key> of each records are compared and listed in order.

SELECT c1, c2 FROM t1 ORDER BY c1, c2;

<ordering specification> which specifies an ascending order or an descending order can be specified in <sort key>. If it is omitted, then they are sorted in an ascending order.

gSQL> SELECT c1 FROM t1;
C1
--
 2
 3
 1
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 ORDER BY c1 ASC;
C1
--
 1
 2
 3
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1 DESC;
C1
--
 3
 2
 1
3 rows selected.
<null ordering> specifies an order between the non-NULL values and NULL values in <sort key>. If it is omitted, then they are sorted as NULLS LAST.
gSQL> SELECT c1 FROM t1;
  C1
----
   2
null
   1
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1;    
  C1
----
   1
   2
null
3 rows selected.

gSQL> SELECT c1 FROM t1 ORDER BY c1 NULLS LAST;
  C1
----
   1
   2
null
3 rows selected.
gSQL> SELECT c1 FROM t1 ORDER BY c1 NULLS FIRST;
  C1
----
null
   1
   2
3 rows selected.

If a constant value is specified in <sort key>, the expression positioned in the location corresponding to the order of the corresponding value in <select list> is regarded as <sort key>. In this case, the constant is an integer bigger than 0, and it should be equal or smaller than the total number of expression in <select list>.

gSQL> SELECT c1 FROM t1 ORDER BY 1;
  C1
----
   1
   2
null
3 rows selected.

LONG (LONG VARCHAR, LONG VARBINARY) type can not be specified in <sort key>.

Comparison of Null Value

Sorting Rows Which Have Same Sort Key Value

Peers are rows which can not be distinguished by a sort key, and the peers are sorted according to the scan order.

<aggregation function> Which Is Used As <sort key>

If <aggregation function> is used in <query specification>, or <group by clause> is specified, <aggregation function> can be used as <sort key>.
However, the nested aggregation function can be used as <sort key> only when <group by clause> is specified.
gSQL> SELECT c1, c2 FROM t1;
C1 C2
-- --
 2  1
 3  5
 1  2
 2 10
 3 10
5 rows selected.

gSQL> SELECT sum(c1) FROM t1 ORDER BY sum(c1);
SUM(C1)
-------
     11
1 row selected.

gSQL> SELECT c1, sum(c2) FROM t1 GROUP BY c1 ORDER BY sum(c2);
C1 SUM(C2)
-- -------
 1       2
 2      11
 3      15
3 rows selected.

gSQL> SELECT sum(c1) FROM t1 GROUP BY c1 ORDER BY sum(sum(c1));
SUM(C1)
-------
      6
1 row selected.

Example

The following is an example of SELECT statement which uses ORDER BY clause.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY c_nation;

C_NAME     C_NATION
---------- -------------
Customer#2 CANADA
Customer#4 GERMANY
Customer#1 KOREA
Customer#3 KOREA
Customer#5 UNITED STATES

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY c_nation DESC;

C_NAME     C_NATION
---------- -------------
Customer#5 UNITED STATES
Customer#1 KOREA
Customer#3 KOREA
Customer#4 GERMANY
Customer#2 CANADA

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer ORDER BY 2 DESC;

C_NAME     C_NATION     
---------- -------------
Customer#5 UNITED STATES
Customer#1 KOREA        
Customer#3 KOREA        
Customer#4 GERMANY      
Customer#2 CANADA       

5 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F850

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

O

F851

<order by clause> in subqueries

O

F852

Top-level <order by clause> in views

O

F855

Nested <order by clause> in <query expression>

O

For More Information

Refer to query expression.

offset limit clause

Function

It specifies the number of rows to skip and the number of rows to fetch from the query results.

Syntax

<offset limit clause> ::=
      <result offset clause>
    | <fetch limit clause>
    | <result offset clause> <fetch limit clause>

<result offset clause> ::=
    OFFSET <offset row count> [ { ROW | ROWS } ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ <fetch row count> ] [ ROW ONLY | ROWS ONLY ]

<limit clause> ::=
    LIMIT { <fetch row count> | <offset row count> , <fetch row count> | ALL }

Invocation and Access Rules

The access privilege for <offset limit clause> is not required.

Syntax Rules and Parameters

<result offset clause>

<fetch limit clause>

<fetch first clause>

<limit clause>

Description

<result offset clause>

It fetches rows from the <offset row count>th of the query results. If the result which <offset row count> queried is greater than or equal to the number of rows, the number of fetch rows is 0.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 OFFSET 1;
C1
--
 2
 3
2 rows selected.

gSQL> SELECT c1 FROM t1 OFFSET 3;
no rows selected.

<fetch first clause>

It fetches the query results as many as the number of <fetch row count>.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

gSQL> SELECT c1 FROM t1 FETCH FIRST 2 ROWS ONLY;
C1
--
 1
 2
2 rows selected.

<limit clause>

When LIMIT <fetch_row_count> is used, it fetches the query results as many as the number of <fetch row count>.

When LIMIT <offset row count> is used, <fetch row count>, it fetches the query results as many as the number of <fetch row count> from the <offset row count>th row.

When LIMIT ALL is used, it returns the query results to a user without limit of the number.

gSQL> SELECT c1 FROM t1;
C1
--
 1
 2
 3
3 rows selected.

• LIMIT <fetch_row_count>
gSQL> SELECT c1 FROM t1 LIMIT 2;
C1
--
 1
 2
2 rows selected.

• LIMIT <offset row count>, <fetch_row_count>
gSQL> SELECT c1 FROM t1 LIMIT 1, 1;
C1
--
 2
1 row selected.

• LIMIT ALL
gSQL> SELECT c1 FROM t1 LIMIT ALL;
C1
--
 1
 2
 3
3 rows selected.

Examples

The following is an example of SELECT statement which uses <result offset clause>.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1;

C_NAME     C_NATION
---------- -------------
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

4 rows selected.

The following is an example of SELECT statement which uses <fetch first clause>.

gSQL> SELECT c_name, c_nation FROM customer FETCH FIRST ROW ONLY;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA

1 row selected.

gSQL> SELECT c_name, c_nation FROM customer FETCH FIRST 2 ROW ONLY;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA
Customer#2 CANADA

2 rows selected.

The following is an example of SELECT statement which uses <limit clause>.

gSQL> SELECT c_name, c_nation FROM customer LIMIT 1;

C_NAME     C_NATION
---------- --------
Customer#1 KOREA

1 row selected.

gSQL> SELECT c_name, c_nation FROM customer LIMIT 1, 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

gSQL> SELECT c_name, c_nation FROM customer LIMIT ALL;

C_NAME     C_NATION
---------- -------------
Customer#1 KOREA
Customer#2 CANADA
Customer#3 KOREA
Customer#4 GERMANY
Customer#5 UNITED STATES

5 rows selected.

The following is an example of SELECT statement which uses <result offset clause> and <fetch limit clause>.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1 FETCH 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

gSQL> SELECT c_name, c_nation FROM customer OFFSET 1 LIMIT 2;

C_NAME     C_NATION
---------- --------
Customer#2 CANADA
Customer#3 KOREA

2 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F861

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

O

F862

<result offset clause> in subqueries

O

F863

Nested <result offset clause> in <query expression>

O

F864

Top-level <result offset clause> in views

O

F865

dynamic <offset row count> in <result offset clause>

X

set operator

Function

It performs a set operation for results of the subquery.

Syntax

<set operator> ::=
      <set operator term>
    | <query expression body> UNION [ ALL | DISTINCT ] <set operator term>
    | <query expression body> EXCEPT [ ALL | DISTINCT ] <set operator term>
    | <query expression body> MINUS [ ALL | DISTINCT ] <set operator term>

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

Invocation and Access Rules

All access privileges for the <query expression> in each <set operator term> are required for using <set operator> statement.

Syntax Rules and Parameters

<set operator>

<query term>

It specifies the single subquery.
For more information, refer to query expression.

Description

Differences between ALL and DISTINCT in <set operator>

For example, if the data of the table R1 and R2 is given as follows, the result of each <set operator > is as follows.

SET operation results

SET operation results

Operator Precedence

The operator precedence of <set operator> is as follows.

Result Type of <set operator>

The i-th column of all subqueries in <set operator> should be a data type of the same family, and its result type is determined by  Result Type Combination Rule.
However, LONG VARCHAR and LONG VARBINARY types can only use UNION ALL.

ORDER BY Clause

When <set operator> is used together with ORDER BY, and the column names are different among subqueries, then it can be used as follows.

Examples

The following is an example of SELECT statement which uses UNION operator.

gSQL> SELECT s_nation nation FROM supplier UNION ALL SELECT c_nation FROM customer;

NATION
-------------
FRANCE
KOREA
GERMANY
UNITED STATES
CANADA
KOREA
CANADA
KOREA
GERMANY
UNITED STATES

10 rows selected.

gSQL> SELECT s_nation nation FROM supplier UNION DISTINCT SELECT c_nation FROM customer;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY
FRANCE

5 rows selected.

The following is an example of SELECT statement which uses EXCEPT operator.

gSQL> SELECT c_nation nation FROM customer EXCEPT ALL SELECT s_nation FROM supplier;

NATION
------
KOREA

1 row selected.

gSQL> SELECT c_nation nation FROM customer EXCEPT DISTINCT SELECT s_nation FROM supplier;

no rows selected.

The following is an example of SELECT statement which uses INTERSECT operator.

gSQL> SELECT c_nation nation FROM customer INTERSECT ALL SELECT s_nation FROM supplier;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY

4 rows selected.

gSQL> SELECT c_nation nation FROM customer INTERSECT DISTINCT SELECT s_nation FROM supplier;

NATION
-------------
UNITED STATES
CANADA
KOREA
GERMANY

4 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F302

INTERSECT table operator

O

F301

CORRESPONDING

X

T551

Optional key words for default syntax

O

F304

EXCEPT ALL table operator

O

For More Information

Refer to query expression.

subquery

Function

It specifies the scalar value, row, table which are derived from <query expression>.

Syntax

<scalar subquery> ::=
    <subquery>

<row subquery> ::=
    <subquery>

<table subquery> ::=
    <subquery>

<subquery> ::=
    ( <query expression> )

Invocation and Access Rules

The access privilege for <query expression> in <subquery> is required.

Syntax Rules and Parameters

<scalar subquery>

<row subquery>

<table subquery>

Description

<scalar subquery>

<scalar subquery> returns one row which has one column as a result. The target of <scalar subquery> should be only one, and the result data type depends on the data type of the target.

<scalar subquery> can be used alone in the target of <select list>, and it can be used in the operator which has a single column.

<row subquery>

<row subquery> returns one row which has two or more columns as a result. The targets of <row subquery> should be two or more, and the result data type depends on the data type of each target.

<row subquery> can not be used alone in the target of <select list>, and it can only be used in the row operator which has two or more columns.

<table subquery>

<table subquery> returns one or more rows which have one or more columns as a result. The targets of <table subquery> should be one or more, and the result data type depends on the data type of each target.

<table subquery> can not be used alone in the target of <select list>, but it can be used in the operators such as IN, NOT IN, EXISTS, NOT EXISTS, quantify operator.

Examples

The following is an example of SELECT statement which uses <scalar subquery>.

gSQL> SELECT (SELECT c_name FROM dual)  FROM customer;

(SELECT C_NAME FROM DUAL)
-------------------------
Customer#1
Customer#2
Customer#3
Customer#4
Customer#5

5 rows selected.

gSQL> SELECT c_name, c_nation FROM customer WHERE c_nation = (SELECT 'CANADA' FROM dual);

C_NAME     C_NATION
---------- --------
Customer#2 CANADA

1 row selected.

The following is an example of SELECT statement which uses <row subquery>.

gSQL> SELECT p_name, p_brand, p_type FROM part WHERE (p_brand, p_type) = (SELECT 'Brand#1', 'NICKEL' FROM dual);

P_NAME P_BRAND    P_TYPE
------ ---------- ------
Part#2 Brand#1    NICKEL

1 row selected.

The following is an example of SELECT statement which uses <table subquery>.

gSQL> SELECT s_name, s_nation FROM supplier WHERE s_nation IN (SELECT c_nation FROM customer);

S_NAME                    S_NATION
------------------------- -------------
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

4 rows selected.

gSQL> SELECT * FROM (SELECT s_name, s_nation FROM supplier);

S_NAME                    S_NATION
------------------------- -------------
Supplier#1                FRANCE
Supplier#2                KOREA
Supplier#3                GERMANY
Supplier#4                UNITED STATES
Supplier#5                CANADA

5 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F471

Scalar subquery values

O

F641

Row and table constructors

X

T501

Enhanced EXISTS predicate

O

E061-11

Subqueries in IN predicate

O

E061-12

Subqueries in quantified comparison predicate

O

E061-12

Correlated subqueries

O

For More Information

Refer to the following.

hint clause

It specifies a hint to be used for a query execution.
For more information, refer to SQL Hint.

SELECT .. FOR UPDATE

Function

It sets whether or not to update the result set of SELECT statement.

Syntax

<select for update statement> ::=
    <query expression>  <updatability clause>
    ;

<updatability clause> ::=
      FOR READ ONLY 
    | FOR UPDATE [ OF <column name list> ] [ <lock wait mode> ]

<lock wait mode> ::=
    | WAIT
    | WAIT second
    | NOWAIT

Invocation and Access Rules

The user should satisfy the following conditions to perform <select for update statement>.

Syntax Rules and Parameters

<query expression>

INTO clause should not exist in SELECT statement.

To use FOR UPDATE, the query should identify the row updates of the base table, or it should be an updatable query which can acquire the lock into the row.

The updatable query should satisfy all of following conditions.

For more information about SELECT statement, refer to query expression.

<updatability clause>

It specifies whether or not to update the row for the result set.

FOR UPDATE OF …

It lists the columns relating to acquiring lock when executing the query.

<lock wait mode>

It is used together with FOR UPDATE statement, and it specifies the lock acquisition method.

Description

SELECT statement keep fetching the rows regardless of whether the transaction ends. However, SELECT .. FOR UPDATE statement can not fetch the rows when the transaction ends because the statement acquires the lock for the rows.

Cursor holdability



Examples

The following is an example of acquiring a lock for the row by using FOR UPDATE statement.

gSQL> SELECT id, data FROM t1 WHERE id = 3 FOR UPDATE;

ID DATA  
-- ------
 3 data_3

1 row selected.

The following uses join and ORDER BY clause but it is an updatable query, so FOR UPDATE statement can be used.

gSQL> SELECT t1.id, t1.name, t2.addr 
        FROM t1, t2
       WHERE t1.id = t2.id
       ORDER BY 1
         FOR UPDATE;

ID NAME    ADDR         
-- ------- -------------
 1 someone somewhere    
 2 anyone  anywhere     
 3 unknown N/A          
 4 leekmo  leekmo's home
 5 mkkim   seoul        

5 rows selected.

The following is a non-updatable query, so FOR UPDATE statement can not be used.

gSQL> SELECT id, COUNT(*)
        FROM t1
       GROUP BY id
         FOR UPDATE;

ERR-42000(16112): query expression is not updatable

Compatibility

In the SQL standard, <select for update statement> is not defined, but it can be defined by using DECLARE cursor_name statement.

SELECT .. INTO

Function

It retrieves a single row by using a query, then obtains the value of retrieved row into the host variable.

Syntax

<select statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>
    ;

<select target list> ::=
    variable_name [, ...]

Invocation and Access Rules

One of the following privileges for all tables used in the statement is required for a user to perform <select statement: single row>.

Syntax Rules and Parameters

<hint clause>

It specifies hints for query execution.
For more information, refer to hint clause of SELECT statement.

<set quantifier>

It specifies whether to remove duplicates from the query result.
For more information, refer to query specification clause.

<select list>

It specifies the columns to be retrieved from the query result.
For more information, refer to select list clause.

INTO <select target list>

The number of the variable specified in INTO clause should be equal to the number of the expression specified in <select list>.

<table expression>

It specifies the query information such as a search condition.
For more information, refer to query specification clause.

Description

The rows to be retrieved should be one or less.
If two or more rows are retrieved, an error occurs.

Differences Between SELECT Statements

Example

The following is an example of obtaining the value into the host variable by using interactive SQL (gsql).

gSQL> \var v_id   INTEGER
gSLQ> \var v_data VARCHAR(128)

gSQL> SELECT id, data INTO :v_id, :v_data FROM t1 WHERE id = 3;

V_ID V_DATA
---- ------
   3 data_3

1 row selected.

SELECT .. INTO .. FOR UPDATE

Function

It sets whether to update the row by retrieving a single row through the query, then obtains the value of retrieved row into the host variable.

Syntax

<select for update statement: single row> ::=
    SELECT [ <hint clause> ] [ <set quantifier> ] <select list>
        INTO <select target list>
        <table expression>  <updatability clause>
    ;

<select target list> ::=
    variable_name [, ...]

<updatability clause> ::=
      FOR READ ONLY 
    | FOR UPDATE [ OF <column name list> ] [ <lock wait mode> ]

<lock wait mode> ::=
    | WAIT
    | WAIT second
    | NOWAIT

Invocation and Access Rules

One of the following privileges for all tables used in the statement is required for a user to perform <select statement: single row>.

If FOR UPDATE clause is used, one of the following privileges for the tables to be locked is required.

Syntax Rules and Parameters

<select for update statement: single row>

To use FOR UPDATE, the query should identify the row updates of the base table, or it should be an updatable query which can acquire the lock into the row.

The updatable query should satisfy all of following conditions.

<updatability clause>

It specifies whether or not to update the row for the result set.

FOR UPDATE OF …

It lists the columns relating to acquiring lock when executing the query.

<lock wait mode>

It is used together with FOR UPDATE statement, and it specifies the lock acquisition method.

<hint clause>

It specifies hints for query execution.
For more information, refer to hint clause of SELECT statement.

<set quantifier>

It specifies whether to remove duplicates from the query result.
For more information, refer to query specification clause.

<select list>

It specifies the columns to be retrieved from the query result.
For more information, refer to select list clause.

INTO <select target list>

The number of the variable specified in INTO clause should be equal to the number of the expression specified in <select list>.

<table expression>

It specifies the query information such as a search condition.
For more information, refer to query specification clause.

Description

The rows to be retrieved should be one or less.
If two or more rows are retrieved, an error occurs.

SELECT statement keep fetching the rows regardless of whether the transaction ends. However, SELECT .. FOR UPDATE statement can not fetch the rows when the transaction ends because the statement acquires the lock for the rows.

Cursor holdability



Differences Between SELECT Statements

Examples

The following is an example of acquiring a lock for the row by using FOR UPDATE statement, and obtaining the value into the host variable by using interactive SQL (gsql).

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> SELECT id, data INTO :v_id, :v_data FROM t1 WHERE id = 3 FOR UPDATE;

V_ID V_DATA
---- ------
   3 data_3

1 row selected.

The following uses join and ORDER BY clause but it is an updatable query, so FOR UPDATE statement can be used.

gSQL> \var v_id   INTEGER
gSQL> \var v_name VARCHAR(128)
gSQL> \var v_addr VARCHAR(128)


gSQL> SELECT t1.id, t1.name, t2.addr 
        INTO :v_id, :v_name, :v_addr
        FROM t1, t2
       WHERE t1.id = t2.id
       ORDER BY 1
       LIMIT 1
         FOR UPDATE;

ID NAME    ADDR         
-- ------- -------------
 1 someone somewhere    

1 row selected.

The following is a non-updatable query, so FOR UPDATE statement can not be used.

gSQL> \var v_id    INTEGER
gSQL> \var v_count INTEGER

gSQL> SELECT id, COUNT(*)
        INTO :v_id, :v_count
        FROM t1
       GROUP BY id
         FOR UPDATE;

ERR-42000(16112): query expression is not updatable

For More Information

Refer to the following.

SET CONSTRAINTS

Function

It sets the check point of deferrable constraint in a transaction to IMMEDIATE or DEFERRED.

Syntax

<set constraints mode statement> ::=
    SET { CONSTRAINT | CONSTRAINTS } <constraint name list> { DEFERRED | IMMEDIATE }
    ;

<constraint name list> ::=
      ALL
    | <constraint name> [, ...]

Invocation and Access Rules

Any separate access privilege is not required for a user to perform SET CONSTRAINTS.

It is not supported in the cluster system.

Syntax Rules and Parameters

CONSTRAINT | CONSTRAINTS

CONSTRAINT and CONSTRAINTS are the keywords of the same meaning, and the SQL standard uses CONSTRAINTS.

<constraint name list>

It specifies the list of constraint names, or specifies ALL to set all deferrable constraints.
When specifying <constraint name>, it should be the name of the deferrable constraint.
ALL means all deferrable constraints.

DEFERRED | IMMEDIATE

It sets the check point of specified deferrable constraints.

If the transaction is in progress, the check point of the constraint is set in the current transaction. If the transaction is not in progress, it is set in the next transaction.
After the transaction ends, it does not affect the next transaction.

Description

Deferrable Constraint

DEFERRABLE constraint can change its check point.
The following is an example of creating a table with a deferrable constraint, and inserting data to the table.
gSQL> CREATE TABLE t1 
( 
    id   INTEGER, 
    name VARCHAR(128) CONSTRAINT t1_uk UNIQUE 
                      DEFERRABLE INITIALLY IMMEDIATE
);

Table created.

gSQL> COMMIT;

Commit complete.

gSQL> INSERT INTO t1 VALUES ( 1, 'leekmo' );

1 row created.

gSQL> INSERT INTO t1 VALUES ( 2, 'mkkim' );

1 row created.

gSQL> COMMIT;

Commit complete.

In the example above, UNIQUE constraint which is deferrable is created on a name column, and the initial check point is set as INITIALLY IMMEDIATE. Therefore, the constraint is checked whenever DML statement is executed.

In this case, if the user tries to exchange the name value of two rows as follows, it violates the constraint because the check point is IMMEDIATE.

gSQL> UPDATE t1 SET name = 'mkkim' WHERE id = 1;

ERR-23000(16057): unique constraint (PUBLIC.T1_UK) violated

gSQL> UPDATE t1 SET name = 'leekmo' WHERE id = 2;

ERR-23000(16057): unique constraint (PUBLIC.T1_UK) violated

If the check point is changed to DEFERRED as follows, the operation the same as above succeeds because the constraint is checked when the transaction is committed.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> UPDATE t1 SET name = 'mkkim' WHERE id = 1;

1 row updated.

gSQL> UPDATE t1 SET name = 'leekmo' WHERE id = 2;

1 row updated.

gSQL> COMMIT;

Commit complete.

If the check point is set to DEFERRED, then the constraint is checked when the transaction is committed. Therefore, if the transaction is committed when the constraint is violated, then the transaction fails and it is rolled back as follows.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> INSERT INTO t1 VALUES ( 3, 'leekmo' );

1 row created.

gSQL> COMMIT;

ERR-40002(16291): transaction rollback: integrity constraint violation : PUBLIC.T1_UK(1)

Violation of a Deferred Constraint

Executing the following statements when the transaction violates the constraints set to DEFFFERED, then an error occurs as follows.

An unexpected ROLLBACK can occur when COMMIT, it is necessary to ensure whether the transaction violates the constraint by using SET CONSTRAINTS ALL IMMEDIATE statement.

gSQL> SET CONSTRAINTS t1_uk DEFERRED;

Constraints set.

gSQL> INSERT INTO t1 VALUES ( 3, 'leekmo' );

1 row created.

gSQL> SET CONSTRAINTS ALL IMMEDIATE;

ERR-23000(16038): integrity constraint violation : PUBLIC.T1_UK(1)

gSQL> SELECT * FROM t1 ORDER BY id;

ID NAME  
-- ------
 1 mkkim 
 2 leekmo
 3 leekmo

3 rows selected.

gSQL> UPDATE t1 SET name = 'xcom73' WHERE id = 3;

1 row updated.

gSQL> SET CONSTRAINTS ALL IMMEDIATE;

Constraints set.

gSQL> COMMIT;

Commit complete.

Transaction Control Language

SET CONSTRAINTS statement is a transaction control language which is used when the transaction is in progress such as SAVEPOINT savepoint_specifier.
The transaction control such as COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT statement is applied to SET CONSTRAINTS statement.
The following is an example of a table with multiple deferrable constraints.
CREATE TABLE t1
(
   id1 INTEGER CONSTRAINT t1_uk1 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
   id2 INTEGER CONSTRAINT t1_uk2 UNIQUE DEFERRABLE INITIALLY IMMEDIATE,
   id3 INTEGER CONSTRAINT t1_uk3 UNIQUE DEFERRABLE INITIALLY IMMEDIATE
);

If <set constraints mode statement> statement is performed when the transaction is in progress, the check point of deferrable constraints is changed depending on each point as follows.

INSERT INTO t1 VALUES ( 1, 1, 1 );

1 row created.

COMMIT;

Commit complete.
SAVEPOINT sp1;

Savepoint created.
SET CONSTRAINTS t1_uk1 DEFERRED;

Constraints set.
SAVEPOINT sp2;

Savepoint created.
SET CONSTRAINTS t1_uk2 DEFERRED;

Constraints set.
SAVEPOINT sp3;

Savepoint created.
SET CONSTRAINTS ALL DEFERRED;

Constraints set.
SAVEPOINT sp4;

Savepoint created.
SET CONSTRAINTS ALL IMMEDIATE;

Constraints set.

When the transaction is partially rolled back by using ROLLBACK TO SAVEPOINT statement as follows, SET CONSTRAINTS statement is also partially rolled back and the check point is changed.

INSERT INTO t1 VALUES ( 1, 2, 2 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK1) violated
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp4;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

1 row created.
INSERT INTO t1 VALUES ( 4, 4, 1 );

1 row created.
ROLLBACK TO SAVEPOINT sp3;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

1 row created.
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp2;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

1 row created.
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
ROLLBACK TO SAVEPOINT sp1;

Rollback complete.
INSERT INTO t1 VALUES ( 1, 2, 2 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK1) violated
INSERT INTO t1 VALUES ( 3, 1, 3 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK2) violated
INSERT INTO t1 VALUES ( 4, 4, 1 );

ERR-23000(16057): unique constraint (PUBLIC.T1_UK3) violated
SELECT * FROM t1;

ID1 ID2 ID3
--- --- ---
  1   1   1

1 row selected.

When the transaction is committed or rolled back, the effects of SET CONSTRAINTS statement is also terminated, and all deferrable constraints follows the constraints property which is INITIALLY IMMEDIATE or INITIALLY DEFERRED value.

Examples

The following is an example of changing the check point by specifying the constraint name.

gSQL> SET CONSTRAINTS t1_uk1 DEFERRED;

Constraints set.

The following is an example of changing the check point of all deferrable constraints.

gSQL> SET CONSTRAINTS ALL DEFERRED;

Constraints set.

Compatibility

The SQL standard does not define CONSTRAINT keyword clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F721

Deferrable constraints

O

For More Information

Refer to the following.

SET ROLE role_name

Function

It sets the session role and the current role.

Syntax

<set role statement> ::=
    SET ROLE <role specification> ;

<role specification> ::=
     <role_name>
   | NONE

Invocation and Access Rules

One of the following conditions should be satisfied to perform <set role statement>.

Syntax Rules and Parameters

<role specification>

Description

If the transaction is activated, then it can not alter <set role statement>.

The current role is NULL when it is connected to the session for the first time.

Set the current role by performing <set role statement>.
Or, do not set the current role session as when it is connected to the session for the first time.

It performs all statements according to the current role after performing <set role statement>.

Example

The following is an example of setting and releasing the current role by the user whose role is granted.

gSQL> \connect u1 u1

gSQL> SELECT CURRENT_USER , CURRENT_ROLE FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
U1           null        

1 row selected.

gSQL> SET ROLE role1;

Session set.

gSQL> SELECT CURRENT_USER , CURRENT_ROLE FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
U1           ROLE1       

1 row selected.

gSQL> SET ROLE NONE;

Session set.

gSQL> SELECT CURRENT_USER , CURRENT_ROLE FROM dual;

CURRENT_USER CURRENT_ROLE
------------ ------------
U1           null        

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T331

Basic roles

O

T332

Extended Roles

X

SET SCHEMA schema_name

Function

It sets the default schema name to be used in the current session.

Syntax

<set schema statement> ::=
    SET SCHEMA schema_name
    ;

Invocation and Access Rules

N/A

Syntax Rules and Parameters

schema_name

It is the default schema name to be set in the current session.

Description

It sets the default schema name to be used in the current session.
If the schema name of the object is not specified, then it becomes the default schema name to be used in the current session.
% gsql u1 u1
gsql> SELECT * FROM r;
gsql> SET SCHEMA new_schema;

gsql> SELECT * FROM r;

Examples

The following is an example of when user u1 has schema s1 and s2.

CREATE USER u1 IDENTIFIED BY u1 WITHOUT SCHEMA;
CREATE SCHEMA s1 AUTHORIZATION u1;
CREATE SCHEMA s2 AUTHORIZATION u1;
COMMIT;

ALTER USER u1 SCHEMA PATH ( s1, s2 );
GRANT ALL PRIVILEGES TO u1;
COMMIT;

CREATE TABLE s1.t1 ( c1 VARCHAR(32) );
INSERT INTO s1.t1 VALUES ( 'S1.T1' );
COMMIT;

CREATE TABLE s2.t1 ( c1 VARCHAR(32) );
INSERT INTO s2.t1 VALUES ( 'S2.T1' );
COMMIT;

It retrieves table S1.T1 by interpreting table t1 using schema path of user u1 when accessing for the first time.

% gsql u1 u1

gSQL> SELECT current_schema FROM dual;

CURRENT_SCHEMA
--------------
S1            

1 row selected.


gSQL> SELECT * FROM t1;

C1   
-----
S1.T1

1 row selected.

It retrieves table S2.T1 by interpreting table t1 using schema name of the session after SET SCHEMA statement is used.

gSQL> SET SCHEMA s2;

Session set.


gSQL> SELECT current_schema FROM dual;

CURRENT_SCHEMA
--------------
S2            

1 row selected.


gSQL> SELECT * FROM t1;

C1   
-----
S2.T1

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F761

Session management

O

SET SESSION AUTHORIZATION user_identifier

Function

It sets the session user and current user.

Syntax

<set session user identifier statement> ::=
    SET SESSION AUTHORIZATION user_identifier
    ;

Invocation and Access Rules

ACCESS CONTROL ON DATABASE privilege is required for a logon user to perform <set session user identifier statement>.

The user information is managed in three types as follows.

Syntax Rules and Parameters

user_identifier

It is the username to be altered.

Description

After performing SET SESSION AUTHORIZATION statement, all statements is performed based on the session user. Therefore, the privilege for the session user is checked and the owner of when creating objects also is the session user.

Example

The following is an example that the user test with ACCESS CONTROL ON DATABASE privilege sets the user u1 to the session user.

gSQL> SET SESSION AUTHORIZATION u1;

Session set.

gSQL> SELECT LOGON_USER(), SESSION_USER(), CURRENT_USER FROM dual;

LOGON_USER() SESSION_USER() CURRENT_USER
------------ -------------- ------------
TEST         U1             U1          

1 row selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F321

User authorization

O

SET SESSION CHARACTERISTICS AS transaction_mode

Function

It sets the transaction property of a session.

Syntax

<set session characteristics statement> ::=
    SET SESSION CHARACTERISTICS AS TRANSACTION <transaction_mode>
    ;

<transaction_mode> ::=
    { <transaction_access_mode> | ISOLATION LEVEL < isolation_level > }

<transaction_access_mode> ::=
    READ { ONLY | WRITE }

< isolation_level > ::=
    { READ COMMITTED | SERIALIZABLE }

Syntax Rules and Parameters

<transaction_access_mode>

It is ACCESS MODE of the following transactions.

<isolation_level>

It is ISOLATION LEVEL of the following transactions.

SERIALIZABLE is not supported in the cluster environment.

Description

SET SESSION CHARACTERISTICS sets the transaction property of a session. In other words, properties of all transactions created within the session follows these properties.
However, SET TRANSACTION transaction_mode statement sets only the property of a single transaction which is performed next.

Examples

The following is an example that all transactions to be created within the session are set to READ ONLY.

gSQL> SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;

Session set.

The following is an example that the isolation level of all transactions to be created within the session is set to READ COMMITTED.

gSQL> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

Session set.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F761

Session management

O

For More Information

Refer to SET TRANSACTION transaction_mode.

SET TIME ZONE

Function

It sets the TIMEZONE of a session.

Syntax

<set local time zone statement> ::=
    SET TIME ZONE <set time zone value>
    ;

<set time zone value> ::= 
    { '[+|-]hh:mm' | LOCAL }

Syntax Rules and Parameters

<set time zone value>

It is the TIMEZONE value to be set.

Description

Altering the time zone of the session affects the result value of function such as CURRENT_TIME, CURRENT_TIMESTAMP.

Example

The following is an example of altering the session time zone to '+09: 00'.

gSQL> SET TIME ZONE '+09:00';

Session set.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F411

Time zone specifications

O

SET TRANSACTION transaction_mode

Function

It sets the transaction property.

Syntax

<set transaction statement> ::=
    SET TRANSACTION <transaction_mode>
    ;

<transaction_mode> ::=
    { <transaction_access_mode> | ISOLATION LEVEL < isolation_level > }

<transaction_access_mode> ::=
    READ { ONLY | WRITE }

< isolation_level > ::=
    { READ COMMITTED | SERIALIZABLE }

Syntax Rules and Parameters

<transaction_access_mode>

It is ACCESS MODE of the following transactions.

<isolation_level>

It is ISOLATION LEVEL of the following transactions.

SERIALIZABLE is not supported in the cluster environment.

Description

SET TRANSACTION sets property of the next transaction, and the property is reset to the default value after the next transaction ends.

Example

The following is an example of setting the next transaction to READ ONLY.

gSQL> SET TRANSACTION READ ONLY;

Transaction set.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T251

SET TRANSACTION statement: LOCAL option

X

For More Information

Refer to SET SESSION CHARACTERISTICS AS transaction_mode.

TRUNCATE TABLE

Function

It truncates all rows from a table.

Syntax

<truncate table statement> ::= 
    TRUNCATE TABLE table_name 
        [ RESTART IDENTITY | CONTINUE IDENTITY ] 
        [ DROP STORAGE | DROP ALL STORAGE ] 
    ;

Invocation and Access Rules

One of the following privileges is required for a user to perform <truncate table statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be truncated.
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

[ RESTART IDENTITY | CONTINUE IDENTITY ]

[ DROP STORAGE | DROP ALL STORAGE ]

Description

Data Definition Language (DDL) statement such as TRUNCATE TABLE can be rolled back if it is before when the transaction is committed.

TRUNCATE TABLE does not fire DELETE TRIGGER.

A parent table referenced by a foreign key cannot be TRUNCATEd.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER CONSTRAINT child_fk REFERENCES parent(pk) );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> TRUNCATE TABLE parent;
ERR-42000(16042): unique/primary keys in table referenced by foreign keys

Either TRUNCATE the child table first, drop the foreign key, or change it to NOT ENFORCED.

gSQL> TRUNCATE TABLE child;
Table truncated.

gSQL> TRUNCATE TABLE parent;
Table truncated.
gSQL> ALTER TABLE child ALTER CONSTRAINT child_fk NOT ENFORCED;
Table altered.

gSQL> TRUNCATE TABLE parent;
Table truncated.

Examples

The following is an example of performing TRUNCATE TABLE statement.

gSQL> TRUNCATE TABLE t1;

Table truncated.

The following is an example of restarting the value of the identity column when performing TRUNCATE TABLE.

TRUNCATE TABLE t1 RESTART IDENTITY;

Table truncated.

Compatibility

The SQL standard does not define [ DROP STORAGE | DROP ALL STORAGE ] clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F200

TRUNCATE TABLE statement

O

F202

TRUNCATE TABLE: identity column restart option

O

UPDATE

Function

It updates rows in a table.

Syntax

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
    ;

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

Invocation and Access Rules

One of the following privileges is required for a user to perform <update statement: searched>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be updated.
It can define schema to which the table belongs such as schema_name.table_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

[ AS alias_name ]

It is an alias for the table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be the same as the number of values.
It can be defined as follows.
UPDATE table_name 
   SET column1 = value1, column2 = value2, column3 = value3
UPDATE table_name 
   SET ( column1, column2, column3 ) = ( value1, value2, value3 )
UPDATE table_name 
   SET column1 = ( SELECT max(value1) FROM other_table_name )
<query expression> should be a query which creates a single row.
If DEFAULT is defined as a column value, the default values (refer to <default clause>) defined when executing CREATE TABLE is used. If it is not defined, NULL value is assigned.

WHERE <search condition>

It updates the rows which satisfy WHERE condition.
If WHERE condition is not specified, all rows are updated.
For more information about WHERE condition, refer to where clause of SELECT.

<result offset clause>

It specifies the number of rows to skip from the query result.
For more information, refer to <result offset clause> of SELECT.

<fetch limit clause>

It specifies the number of rows to fetch in two ways, which are <fetch first clause> and <limit clause>.

Description

Differences Between UPDATE Statements

Examples

The following is an example of updating multiple rows which satisfy the condition.

gSQL> UPDATE lineitem
         SET l_shipdate = CURRENT_DATE
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating the value of multiple columns.

gSQL> UPDATE lineitem
         SET l_shipdate   = CURRENT_DATE
           , l_returnflag = 'A'
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating multiple columns by enclosing them with parentheses.

gSQL> UPDATE lineitem
         SET ( l_shipdate  , l_returnflag )
           = ( CURRENT_DATE, 'A' )
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating the column value by using the subquery.

gSQL> UPDATE lineitem
         SET l_discount = ( SELECT MAX(l_discount) + 0.01 FROM lineitem )
       WHERE l_returnflag = 'R';

5 rows updated.

The following is an example of updating part of the rows which satisfy the condition by using OFFSET and FETCH clauses.

gSQL> UPDATE lineitem
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
      OFFSET 3
      FETCH 2;

2 rows updated.

Compatibility

The SQL standard does not define the following clauses in UPDATE statement.

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

T111

Updatable joins, unions, and columns

X

UPDATE name RETURNING

Function

It updates rows in a table, and retrieves the rows of before or after the update.

Syntax

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning clause>

<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }


<returning clause> ::=
    { RETURN | RETURNING } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] }

Invocation and Access Rules

The user should satisfy the following conditions to perform <update returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be updated.

[ AS alias_name ]

It is an alias for the table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be the same as the number of values.
For more information, refer to UPDATE.

WHERE <search condition>

It updates the rows which satisfy WHERE condition.
If WHERE condition is not specified, all rows are updated.
For more information about WHERE condition, refer to where clause of SELECT.

<result offset clause>

It specifies the number of rows to skip from the query result.
For more information, refer to <result offset clause> of SELECT.

<fetch limit clause>

It specifies the number of rows to fetch in two ways, which are <fetch first clause> and <limit clause>.

<returning clause>

It defines the updated rows as a result set, and specifies columns to be retrieved from the result set.

Description

For more information, refer to Differences Between UPDATE Statements.

Examples

The following is an example of obtaining values of the updated rows by using RETURNING clause.

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
   RETURNING l_orderkey, l_linenumber, l_discount;

L_ORDERKEY L_LINENUMBER L_DISCOUNT
---------- ------------ ----------
         8            1        .07
         9            2        .11
        12            5        .05
        15            1        .03
        16            2        .08

5 rows updated.

The following is an example of obtaining values before the update for the updated rows by using RETURNING OLD clause.

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_returnflag = 'R'
   RETURNING OLD l_orderkey, l_linenumber, l_discount;

L_ORDERKEY L_LINENUMBER L_DISCOUNT
---------- ------------ ----------
         8            1        .06
         9            2         .1
        12            5        .04
        15            1        .02
        16            2        .07

5 rows updated.

Compatibility

The SQL standard does not define <update returning query statement>.

UPDATE name RETURNING .. INTO

Function

It updates a single row of a table, and the updated value is obtained into the host variable.

Syntax

<update statement: searched> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning into clause>
    ;


<set clause> ::=
      column_name = { <value expression> | DEFAULT }
    | ( column_name [, ...] ) = ( { <value expression> | DEFAULT } [, ...] )
    | ( column_name [, ...] ) = ( <query expression> )


<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }


<returning into clause> ::=
    { RETURN | RETURNING } [ NEW | OLD ] { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO variable_name [, ...]

Invocation and Access Rules

The user should satisfy the following conditions to perform <update returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of a target table whose rows are to be updated.

[ AS alias_name ]

It is an alias for the table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be the same as the number of values.
For more information, refer to UPDATE.

WHERE <search condition>

It updates the rows which satisfy WHERE condition.
If WHERE condition is not specified, all rows are updated.
For more information about WHERE condition, refer to where clause of SELECT.

<result offset clause>

It specifies the number of rows to skip in the query result.
For more information, refer to <result offset clause> of SELECT.

<fetch limit clause>

It specifies the number of rows to fetch in two ways, which are <fetch first clause> and <limit clause>.

RETURNING .. AS ..

It defines the updated rows as a result set, and specifies columns to be retrieved from the result set.
For more information, refer to <returning clause> of UPDATE name RETURNING.

INTO variable_name [, ...]

The number of variables specified in INTO clause should be equal to the number of the expressions specified in RETURNING clause.
The row to be updated should be one or less. If two or more rows are updated, an error occurs.

Description

For more information, refer to Differences Between UPDATE Statements.

Example

The following is an example of obtaining column values of the updated rows into the host variables.
• Declare the host variable.
gSQL> \VAR v_discount NUMBER

gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01
       WHERE l_orderkey = 12 AND l_linenumber = 5
   RETURNING l_discount INTO :v_discount;

V_DISCOUNT
----------
       .05

1 row updated.

Compatibility

In the SQL standard, <update returning into statement> statement does not exist.

UPDATE name WHERE CURRENT OF cursor_name

Function

It updates a single row which the current cursor indicates.

Syntax

<update statement: positioned> ::=
    UPDATE table_name [ [ AS ] alias_name ]
        SET <set clause> [, ...]
        WHERE CURRENT OF cursor_name
    ;

Invocation and Access Rules

The user should satisfy the following conditions to perform <update statement: positioned>.

Syntax Rules and Parameters

table_name

It is the name of a table whose rows are to be updated.

[ AS alias_name ]

It is an alias for the table_name.

<set clause>

It defines the columns to be updated and its values to be assigned. The number of columns in <set clause> should be the same as the number of values.
For more information, refer to UPDATE.

cursor_name

The cursor corresponding to cursor_name should satisfy the following conditions.

Description

For more information, refer to Differences Between UPDATE Statements.

Examples

The following is an example that <update statement: positioned> is performed in interactive SQL (gsql) using the cursor.

gSQL> \VAR v_discount NUMBER
gSQL> DECLARE update_cursor CURSOR FOR 
        SELECT l_discount
          FROM lineitem
         WHERE l_orderkey = 8 AND l_linenumber = 1
           FOR UPDATE;

Cursor declared.
gSQL> OPEN update_cursor;

Cursor is open.
gSQL> FETCH update_cursor INTO :v_discount;

V_DISCOUNT
----------
       .06

1 row fetched.
gSQL> UPDATE lineitem 
         SET l_discount = l_discount + 0.01 
       WHERE CURRENT OF update_cursor;

1 row updated.
gSQL> CLOSE update_cursor;

Cursor closed.

gSQL> COMMIT;

Commit complete.

The following is an example of performing <update statement: positioned> by using the cursor in embedded SQL program.

{
    ...
    EXEC SQL BEGIN DECLARE SECTION;
        ...    
        double v_discount;  
        ...   
    EXEC SQL END DECLARE SECTION;
    ...
    EXEC SQL DECLARE update_cursor CURSOR FOR
              SELECT l_discount
                FROM lineitem
               WHERE l_orderkey = 8 AND l_linenumber = 1
                 FOR UPDATE;
    ...
    EXEC SQL OPEN update_cursor;
    ...
    EXEC SQL FETCH NEXT update_cursor INTO :v_discount;
    ...
    EXEC SQL UPDATE lineitem 
                SET l_discount = l_discount + 0.01 
              WHERE CURRENT OF update_cursor;
    ...
    EXEC SQL CLOSE update_cursor;
    ...
    EXEC SQL COMMIT WORK;
    ...
}

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F831

Full cursor update

O

B031

Basic dynamic SQL

O

For More Information

Refer to CLOSE cursor_name.