Add syntax to generate a range of values

Detail name	Value
Changelog Number	10983
Type	New Feature
Status	Resolved
Fix Versions	Exasol 7.1.0
Resolution Date	2021-08-03

New Feature 

We have added a syntax for a table that generates integer values between a minimum and maximum value, with an optional step:

VALUES BETWEEN <minimum> AND <maximum> [WITH STEP <step>]

The table has a single column called RANGE_VALUE with an integer type.

It will generate each value between minimum and maximum inclusive if no step is given, otherwise, it will skip values, producing the arithmetic series minimum + step * n with the last value less than or equal to the maximum value.

The generated rows are not ordered.

The user does not need to have any privileges to be able to select from this "table".

Examples

SELECT * FROM VALUES BETWEEN 1 AND 4;

RANGE_VALUE 
------------
           1
           2
           3
           4

SELECT * FROM VALUES BETWEEN 10 AND 230 WITH STEP 60;

RANGE_VALUE 
------------
          10
          70
         130
         190

Previously you could create a time dimension using the CONNECT BY LEVEL syntax, which was not as performant. This example shows how to easily create a time dimension with this new syntax.

CREATE OR REPLACE TABLE test.dim_date AS 
SELECT 
        to_date('2020-01-01', 'YYYY-MM-DD') -1 + RANGE_VALUE     AS the_date,
        extract(DAY FROM LOCAL.the_date)                         AS the_day,
        to_char(LOCAL.the_date, 'day')                           AS the_day_name,
        cast(to_char(LOCAL.the_date, 'ww') as decimal(2,0))      AS the_week,
        cast(to_char(LOCAL.the_date, 'iw') as decimal(2,0))      AS the_iso_week,
        cast(to_char(LOCAL.the_date, 'd')  as decimal(1,0))      AS the_day_of_week, 
        cast(to_char(LOCAL.the_date, 'id') as decimal(1,0))      AS the_iso_day_of_week,
        cast(extract(MONTH FROM LOCAL.the_date) as decimal(2,0)) AS the_month,
        to_char(LOCAL.the_date, 'month')                         AS the_month_name,
        cast(to_char(LOCAL.the_date, 'q') as decimal(1,0))       AS the_quarter,
        extract(YEAR FROM LOCAL.the_date)                        AS the_year,
        trunc(LOCAL.the_date, 'MM')                              AS the_first_of_month,
        add_months(trunc(LOCAL.the_date, 'YYYY'), 12) - 1        AS the_last_of_year,
        cast(to_char(LOCAL.the_date, 'ddd') as decimal(3,0))     AS the_day_of_year
from
values between 1 and 1000;

 

Changed behavior

Added VALUES BETWEEN syntax