7.15.4 ADB_GENERATE_SERIES function

Generates a series of values within the range specified in the arguments of the ADB_GENERATE_SERIES function, and returns the data in a format that can be retrieved as a single-column table.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Rules
(4) Examples

(1) Specification format

ADB_GENERATE_SERIES-function::=
              [MASTER.]{ADB_GENERATE_SERIES
                       |GENERATE_SERIES} (start-value,end-value[,interval-value])
 
  start-value::=value-expression
  end-value::=value-expression
  interval-value::={integer-literal|time-interval-literal}
    time-interval-literal::=INTERVAL'labeled-duration'

To Page Top

(2) Explanation of specification format

start-value:

Specifies the start value of the series of values to be generated as a value expression. For details about value expressions, see 7.21 Value expression.

The following rules apply:

Specify integer data or datetime data in the value expression. A character string literal (predefined input representation for dates, predefined input representation for times, or predefined input representation for time stamps) can also be specified as datetime data. For details about predefined input representations, see 6.3.3 Predefined character-string representations.
A dynamic parameter cannot be specified alone as the start value.

end-value:

Specifies the end value of the series of values to be generated as a value expression. For details about value expressions, see 7.21 Value expression.

The following rules apply:

Specify integer data or datetime data in the value expression. A character string literal (predefined input representation for dates, predefined input representation for times, or predefined input representation for time stamps) can also be specified as datetime data. For details about predefined input representations, see 6.3.3 Predefined character-string representations.
If a dynamic parameter is specified alone as the end value, the data type of the start value is assumed for the data type of the dynamic parameter.

interval-value:

interval-value::= {integer-literal|time-interval-literal}
  time-interval-literal::= INTERVAL'labeled-duration'

Specifies the interval between each value in the series of values to be generated, as an integer literal or a time interval literal. For details about labeled durations, see 7.29 Labeled duration.

The following rules apply:

If the start value is integer data, the specification of the interval value can be omitted. If omitted, 1 is assumed for the interval value.
If the start value is integer data, a time interval literal cannot be specified for the interval value.
If the start value is integer data, 0 cannot be specified for the interval value.
If the start value is datetime data or a character string literal, the interval value cannot be omitted.
If the start value is datetime data or a character string literal, an integer literal cannot be specified for the interval value.
If the interval value is a time interval literal, only a literal can be specified for the value-expression-primary of the labeled duration.
If the interval value is a time interval literal, 0 cannot be specified for the value-expression-primary of the labeled duration.
If the start value is DATE type or a character string literal (predefined input representation for dates), HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, or PICOSECOND cannot be specified for the labeled duration qualifier of the time interval literal specified for the interval value.
If the start value is TIME type or a character string literal (predefined input representation for times), YEAR, MONTH, or DAY cannot be specified for the labeled duration qualifier of the time interval literal specified for the interval value.

To Page Top

(3) Rules

Specify comparable data types for the start value and end value. For information on comparable data types, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.
Starting from the start value, the interval value is repeatedly added until the end value is reached. The resulting series of values is returned as the result of the ADB_GENERATE_SERIES function in a format that can be retrieved as a single-column table.
Examples:
```
ADB_GENERATE_SERIES(1,3,1)
```
In the above case, three rows of data with column values of 1, 2, and 3 are returned.
```
ADB_GENERATE_SERIES(3,1,-1)
```
In the above case, three rows of data with column values of 3, 2, and 1 are returned.
```
ADB_GENERATE_SERIES(1,-1,1)
```
In the above case, an empty row is returned.
The data type of the series of values becomes the data type of the column derived by the table function derived table that specifies the ADB_GENERATE_SERIES function.

The data type of the column that stores the series of values is determined according to the following rules:

If the start value is integer data

The data type of the column that stores the series of values is determined according to the rules described in 7.21.2 Data types of the results of value expressions with the start value, end value, and interval value as the candidate group for the result data type.

If the start value is datetime data or a character string literal

The data type of the column that stores the series of values is determined by the combination of the data type determined according to the rules described in 7.21.2 Data types of the results of value expressions with the start value and end value as the candidate group, and the interval value.

Table 7‒3: Data type of the column that stores the series of values (when the start value is datetime data or a character string literal)
Data type determined with the start value and end value as candidates	Labeled duration qualifier specified for the interval value	Data type of the column that stores the series of values
DATE	YEAR MONTH DAY	DATE
TIME(`p`)	HOUR MINUTE	TIME(`p`)
	SECOND	TIME(MAX(`p`,0))
	MILLISECOND	TIME(MAX(`p`,3))
	MICROSECOND	TIME(MAX(`p`,6))
	NANOSECOND	TIME(MAX(`p`,9))
	PICOSECOND	TIME(MAX(`p`,12))
TIMESTAMP(`p`)^#	YEAR MONTH DAY HOUR MINUTE	TIMESTAMP(`p`)
	SECOND	TIMESTAMP(MAX(`p`,0))
	MILLISECOND	TIMESTAMP(MAX(`p`,3))
	MICROSECOND	TIMESTAMP(MAX(`p`,6))
	NANOSECOND	TIMESTAMP(MAX(`p`,9))
	PICOSECOND	TIMESTAMP(MAX(`p`,12))

#: If the data type determined with the start value and end value as candidates is TIMESTAMP WITH TIME ZONE type, the data type of the column that stores the series of values is also TIMESTAMP WITH TIME ZONE type. If it is TIMESTAMP WITHOUT TIME ZONE type, the data type of the column that stores the series of values is also TIMESTAMP WITHOUT TIME ZONE type.

When generating the series of values, the values specified in the arguments are converted to the result data type.
When adding the interval value during generation of the series of values, the following arithmetic operation or datetime operation is performed:
- If the start value is integer data, the following arithmetic operation is performed
  
  Interval literal of start-value+interval-value
- If the start value is datetime data or a character string literal, the following datetime operation is performed
  
  Labeled duration of start-value+interval-value
If the start value or end value is a null value, an empty row is returned.
If the interval value is a positive value and start-value > end-value, an empty row is returned.
If the interval value is a negative value and start-value < end-value, an empty row is returned.
For column specifications in the value expressions of the start value or end value, only columns that satisfy all of the following conditions can be specified:
- A column of a table specified in the same FROM clause as the table function derived table that specifies the ADB_GENERATE_SERIES function
- A column of a table that is comma-joined with the table function derived table that specifies the ADB_GENERATE_SERIES function
- A column of a table specified to the left of the table function derived table that specifies the ADB_GENERATE_SERIES function
- Not a column from a table reference in a joined table
- Column from the same table
However, if a subquery is included in the value expression of the start value or end value, the above restrictions do not apply to column specifications within that subquery.
External reference columns cannot be specified in the value expressions of the start value or end value.
Only one table function derived table that specifies the ADB_GENERATE_SERIES function can be specified in a query specification.
The result table of the following two tables is a set of rows created by combining and concatenating each row of the result table derived from the table function derived table with each row of the table specified to the left of the table function derived table:
- The table specified to the left of the table function derived table that specifies the ADB_GENERATE_SERIES function
- The result table derived from the table function derived table
A specific example is shown below.

To Page Top

(4) Examples

The following shows examples of specifying the ADB_GENERATE_SERIES function.

Example without column specifications in the start value and end value
Example 1
```
SELECT "DC1" FROM ADB_GENERATE_SERIES(1,3,1) AS "DT"("DC1")
```
The underlined portion is the specification of the ADB_GENERATE_SERIES function. In this case, the result of the FROM clause (DC1) is as follows:
Example 2
```
SELECT "DC1" FROM ADB_GENERATE_SERIES(1,1,1) AS "DT"("DC1")
```
The underlined portion is the specification of the ADB_GENERATE_SERIES function. In this case, the result of the FROM clause (DC1) is as follows:
Example 3
```
SELECT "DC1" FROM ADB_GENERATE_SERIES(1,0,1) AS "DT"("DC1")
```
The underlined portion is the specification of the ADB_GENERATE_SERIES function. In this case, the result of the FROM clause (DC1) is an empty row.
Example 4
```
SELECT "DC1" FROM ADB_GENERATE_SERIES(DATE'2025-03-01',
                                      DATE'2025-03-03',
                                      INTERVAL'1 DAY') AS "DT"("DC1")
```
The underlined portion is the specification of the ADB_GENERATE_SERIES function. In this case, the result of the FROM clause (DC1) is as follows:
Example 5
```
SELECT "DC1" FROM ADB_GENERATE_SERIES(TIMESTAMP'2025-03-01 10:00:00',
                                      TIMESTAMP'2025-03-01 11:00:00',
                                      INTERVAL'30 MINUTE') AS "DT"("DC1")
```
The underlined portion is the specification of the ADB_GENERATE_SERIES function. In this case, the result of the FROM clause (DC1) is as follows:
Example with column specifications in the start value and end value
Example
```
SELECT "T1"."C1","T1"."C2","T2"."DC1"
    FROM "T1",ADB_GENERATE_SERIES("T1"."C1","T1"."C2",1) AS "T2" ("DC1")
```
The underlined portion is the specification of the ADB_GENERATE_SERIES function.

To Page Top