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

Add syntax to generate a range of values

    XMLWordPrintable

    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

          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: