Creating a View with Variables in Snowflake

Question

I have the following view:

create or replace view my_database_name.my_schema_name.d_dates as (

  /*
      Declare variables
  */
    set (start_date) = ('2017-07-01');
    set (end_date) = ('2022-06-30');

  /*
  */
  with part_1 as (

    select top 20000
        -1 + row_number() over(order by 0) as i_value
        , start_date + i_value as generated_date 
    from 
        (
            select 
                cast($start_date as date) as start_date
               ,cast($end_date as date) as end_date
        )
    inner join table
        (generator(rowcount => 100000 )) x
    /*
      Qualify filters the results of window functions
    */
    qualify 
        i_value < 1 + end_date - start_date

  )

  select
      (a.i_value + 1) as "date_id"
      ,a.generated_date as "date"
  from
      part_1 as a
  order by
      "date";
    

)

I get the following error message:

SQL compilation error: syntax error line 6 at position 4 unexpected 'set'. syntax error line 6 at position 21 unexpected '='.

Any suggestions on how to fix this?

demircioglu demircioglu · Accepted Answer · 2021-03-03T17:56:28

You can achieve what you described in the question using table functions (UDTF) Start and end dates will be parameters to functions.

CREATE OR REPLACE FUNCTION my_database_name.my_schema_name.d_dates (start_date date, end_date date)
RETURNS TABLE (date_id number, generated_date date)
LANGUAGE SQL
AS
'
with part_1 as (

    select top 20000
        -1 + row_number() over(order by 0) as i_value
        , start_date + i_value as generated_date 
    from 
        (
            select 
                cast(start_date as date) as start_date
               ,cast(end_date as date) as end_date
        )
    inner join table
        (generator(rowcount => 100000 )) x
    /*
      Qualify filters the results of window functions
    */
    qualify 
        i_value < 1 + end_date - start_date

  )

  select
      (a.i_value + 1) as date_id
      ,a.generated_date as generated_date
  from
      part_1 as a
  order by
      2;
'
;

Your SQL needs more tweaking I think like not using date as a column name but this should work

Creating a View with Variables in Snowflake

2 Answers