I create a while loop to generate values and insert them into the existing table "PLAYGROUND"."BF_DEV".A5_DIMDATE_HUIQIONGWU

But there always is syntax error:

SQL compilation error: syntax error line 23 at position 10 unexpected '('. syntax error line 23 at position 20 unexpected '<'.

execute immediate $$
declare
    crrday date default '2005-01-01';
    datekey integer default 0;
    daynumberofweek integer default 0;
    endaynumberofweek varchar;
    daynumberofmonth integer default 0;
    daynumberofyear integer default 0;
    weeknumberofyear integer default 0;
    enmonth varchar;
    monthnumberofyear integer default 0;
    calquarter integer default 0;
    calyear integer default 0;
    calses integer default 0;
    fisyear integer default 0;
    fisquarter integer default 0;
    fisses integer default 0;
begin 
    let crrday date := '2005-01-01';
    let lastday date := '2031-01-01';
    let crryear integer := year(crrday);
    let lastyear integer := year(lastday);
    while (:crryear < lastyear) then
        datekey := convert(integer, crrday, 112);
        daynumberofweek := DAYOFWEEK(crrday);
        endaynumberofweek := decode(extract ('dayofweek_iso',crrday);
        daynumberofmonth := DAYOFMONTH(crrday);
        daynumberofyear := DAYOFYEAR(crrday);
        weeknumberofyear := WEEKOFYEAR(crrday);
        enmonth := decode(monthname(crrday));
        monthnumberofyear := MONTH(crrday);
        calquarter := QUARTER(crrday);
        calyear := year(crrday);
        calses := case when month(crrday) between 1 and 6 then 1 else 2 end;
        fisyear := case when month(crrday) between 1 and 6 then year(crrday) else dateadd(year, 1, crrday) end;
        fisquarter := case when month(crrday) between 7 and 9 then 1
                 case when month(crrday) between 10 and 12 then 2
                 case when month(crrday) between 1 and 3 then 3
                 else 4 end;
        fisses := case when month(crrday) between 7 and 12 then 1 else 2 end;
        insert into "PLAYGROUND"."BF_DEV".A5_DIMDATE_HUIQIONGWU values 
        (datekey, crrday, daynumberofweek, endaynumberofweek, daynumberofmonth, endaynumberofweek, daynumberofmonth, 
        daynumberofyear, weeknumberofyear, enmonth, monthnumberofyear, calquarter, calyear, calses, fisquarter, fisyear, fisses);
        crrday := dateadd(day, 1, crrday);
        crryear := year(crryear);
    end while;
end;
$$
;

CodePudding user response：

Have you considered using the GENERATOR function instead of a while loop? The code below is an example of that. (It is not in sproc format but should work as an example.)

• Step 1: CTE with Generator function creates a range of dates.
• Step 2: Select expressions to create the desired date transforms

/*********************************************************************************************
A WEEK_START session variable of 0 is the default Snowflake behavior and has weeks start on
Monday and end of Sunday (ISO standard).
Wherever possible, this script uses the ISO standard for WEEKS and DAY_OF_WEEK.
The DATEDIFF function does not support WEEKISO and therefore the WEEK_START parameter is set 
for the session in case this session parameter was set differently on the account or user. 
https://docs.snowflake.com/en/sql-reference/parameters.html#label-week-start
*********************************************************************************************/
alter session set week_start = 0;

/*********************************************************************************************
The parameters below define the temporal boundaries of the calendar table. The values must be 
DATE type and can be hardcoded, the result of a query, or a combination of both.
For example, you could set date_start and date_end based on the MIN and MAX date of the table
with the finest date granularity in your data.
*********************************************************************************************/

SET date_start = TO_DATE('2018-12-18');
SET date_end = current_date(); --TIP: for the current date use current_date();

--This sets the num_days parameter to the number of days between start and end
--this value is used for the generator
set num_days = (select datediff(day, $date_start, $date_end 1));

--CTE to hold generated date range
create or replace transient table calendar as 
with d as (
select
  dateadd(day,'-' || row_number() over (order by null), 
          dateadd(day, ' 1', $date_end)
         ) as date_key
from table (generator(rowcount => ($num_days)))
order by 1)
-- calendar table expressions 
select
    date_key,
    year(date_key) - (year(date_key) % 10) as decade,
    year(date_key) as year_,
    (year(date_key)::string || '-Q' || quarter(date_key)::string)::varchar(7) as year_qtr,
    (year(date_key)::string || '-' || lpad(month(date_key)::string, 2 ,'0'))::varchar(7) as year_month,
    (yearofweekiso(date_key)::string || '-' || lpad(weekiso(date_key)::string, 2 ,'0'))::varchar(7) as year_week_iso, --*see comments
    quarter(date_key) as qtr_of_year,
    month(date_key) as month_num,
    monthname(date_key) as month_name,
    time_slice(date_key, 1, 'month', 'start') as month_start_date,
    last_day(date_key, 'month')  as month_end_date, 
    weekiso(date_key) as week_iso_num, --*see comments
    yearofweekiso(date_key) as week_iso_year, --*see comments
    'W'||lpad(weekiso(date_key)::string,2,0) as week_iso_string, 
    iff(dayofweekiso(date_key) = 1, date_key, previous_day(date_key, 'mo')) as week_start_mon, --week starts on mon, ends on sun
    iff(dayofweekiso(date_key) = 7, date_key, next_day(date_key, 'su')) as week_end_mon, --week starts on mon, ends on sun
    iff(dayofweekiso(date_key) = 7, date_key, previous_day(date_key, 'su')) as week_start_sun, --week starts on sun, ends on sat
    iff(dayofweekiso(date_key) = 6, date_key, next_day(date_key, 'sa')) as week_end_sun, --week starts on sun, ends on sat
    dayofyear(date_key) as day_of_year,
    dayofmonth(date_key) as day_of_month,
    dayofweekiso(date_key) as day_of_week_iso,
    dayname(date_key) as day_name, 
    ceil(dayofmonth(date_key) / 7) as day_instance_in_month, --used to identify 'floating' events such as "fourth thursday of november" 
    iff(dayofweekiso(date_key) between 6 and 7, 1, 0) flag_day_is_weekend,
    iff(dayofweekiso(date_key) between 6 and 7, 0, 1) flag_day_is_weekday,
    iff(year(current_date())= year(date_key) and dayofyear(date_key) <= dayofyear(current_date()), 1, 0) as flag_cytd, --current year to date, date falls within current year to day
    iff(year(current_date()) - 1 = year(date_key) and dayofyear(date_key) <= dayofyear(current_date()), 1, 0) as flag_lytd, --last year to date, date falls within current year to day
    iff(dayofyear(date_key) <= dayofyear(current_date()), 1, 0) as flag_ytd, --year to date, date falls within the same days in the year, no matter which year
    iff(add_months(time_slice(date_key, 1, 'month', 'start'),1) = time_slice(current_date(), 1, 'month', 'start'),1 , 0) as flag_last_month,
    datediff(year, date_key, current_date()) as age_years_ago,
    datediff(month,date_key, current_date()) as age_months_ago,
    datediff(week,date_key, current_date()) as age_weeks_ago,
    datediff(day,date_key, current_date()) as age_days_ago
FROM D;

Example query:

select * from calendar where flag_lytd = 1
order by date_key desc
limit 10;

CodePudding user response：

I would not use a stored procedure to insert rows one at a time. Inserting single rows to a micropartition based table can take a few hundred milliseconds each. Multiplied by thousands of rows this can be slow, and there are some other issues with it. Here is your stored procedure logic expressed as a SELECT statement. You can then just insert into the table from the select:

select       row_number() over (order by null) - 1              DATEKEY
            ,dateadd('DAY', DATEKEY, '2005-01-01'::date)::date  CRRDAY
            ,dayofweek(CRRDAY)                                  DAYNUMBEROFWEEK
            ,extract('dayofweek_iso',CRRDAY)                    ENDDAYNUMBEROFWEEK -- What do you want here?
            ,dayofmonth(CRRDAY)                                 DAYNUMBEROFMONTH
            ,dayofmonth(last_day(CRRDAY, 'WEEK'))               ENDDAYNUMBEROFWEEK
            ,dayofyear(CRRDAY)                                  DAYNUMBEROFYEAR
            ,weekofyear(CRRDAY)                                 WEEKNUMBEROFYEAR
            ,monthname(CRRDAY)                                  ENMONTH
            ,month(CRRDAY)                                      MONTHNUMBEROFYEAR
            ,quarter(CRRDAY)                                    CALQUARTER
            ,year(CRRDAY)                                       CALYEAR
            ,iff(month(crrday) <= 6, 1, 2)                      CALSES
            ,case 
                when month(crrday) between 7 and 9 then 1
                when month(crrday) between 10 and 12 then 2
                when month(crrday) between 1 and 3 then 3
                else 4
             end                                                FISQUARTER
            ,iff(month(crrday) > 6, 1, 0)   year(crrday)        FISYEAR
            ,iff(month(CRRDAY) > 6, 1, 2)                       FISSES
            
from table(generator(rowcount => 10000))
qualify CRRDAY < '2031-01-01'::date 
order by CRRDAY;

First ten rows: