Uploaded image for project: 'EXASOL Roadmap'
  1. EXASOL Roadmap
  2. EXASOL-2841

Add syntax to generate a range of values

    Details

    • Type: New Feature
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Component/s: None
    • Labels:
      None

      Description

      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;
      

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                CaptainEXA Captain EXASOL
                Reporter:
                CaptainEXA Captain EXASOL
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: