GENERATE_RANGE
Description
GENERATE_RANGE creates values inside a range based on a given step size. It can be used to generate values for dropdown components.
GENERATE_RANGE can be used with INT and DATE columns.
The function generates a temporary table with the generated range as single column. The result table is not joined to any other table. The range is specified by passing a step size, a start and an end value to GENERATE_RANGE. The start and end values must have the same data-type, the generated range is on the interval [start, end].
The maximum number of generated elements is limited to 10,000. If this limit is exceeded, an error is returned. The step size must be positive and range_end must be greater or equal than range_start
Syntax
GENERATE_RANGE ( step_size , range_start , range_end )
step_size: The size of each interval. The type of the step_size depends on the data type of the range_start:
range_start: The value where the generated value range should start from. It must be of type INT or DATE.
range_end: The value where the generated value range should end. It must be of the same data type as the range_start.
Use Cases
The result of GENERATE_RANGE can be used to generate values for a dropdown component.
To create a unique index column for a certain table, UNIQUE_ID or INDEX_ORDER can be used instead.
Examples
[1] Simple example where GENERATE_RANGE is used to create an INT column with values between 1 and 4 and step size 1 | |||||||||
| |||||||||
|
[2] Calculated expressions are also allowed for the step, start, and end values, as long as the calculated value does not depend on the filter state. In this example, the range is set to go from the minimum to the maximum value of IntColumn | |||||||||||||||
| |||||||||||||||
|
[3] This example shows the usage of GENERATE_RANGE with negative start and end operators. Recall that the step size still must be positive | ||||||||||
| ||||||||||
|
[4] Simple example of how to use GENERATE_RANGE to generate DATE range, with a step size of 1 month. | |||||||||||
| |||||||||||
|
[5] Example where the step size is set to one hour. The minutes are also considered for the range boundaries | ||||||||||
| ||||||||||
|