雪花中的 while 循环

Question

我正在 Snowflake 工作表上编写下面的 SQL 脚本，并在下面收到此错误。表格已定义。如有任何反馈，我们将不胜感激。

错误：

第 9 行位置 4 处未捕获类型“STATMENT_ERROR”异常：SQL 编译错误：位置 34 处错误第 162 行无效标识符“CURRMONTH”

execute immediate $$ 
declare
    firstmonth :=(select dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date()))));
    lastmonth  :=(select dateadd(month,1,dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date())))));
    currmonth date default firstmonth;

begin        
 while (currmonth <=lastmonth) do 
    insert into temptable  
    select colA, colB, colC, colD
    from tableA 
    where date between currmonth and dateadd(day,-1,dateadd(month,1,currmonth))  and date2 = currmonth

 currmonth :=dateadd(month,1,currmonth)
end while;
end;
$$

Answer 1

试试这个，将列名从 date 修改为 date1，

create or replace  table temptable  (date1 date,date2 date, colA varchar2, colB varchar2, colC varchar2, colD varchar2);
create or replace  table tableA  (date1 date,date2 date, colA varchar2, colB varchar2, colC varchar2, colD varchar2);


execute immediate $ 
declare
    firstmonth :=(select dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date()))));
    lastmonth  :=(select dateadd(month,1,dateadd(month,case when day(current_timestamp())<=10 then -1 else 0 end,dateadd(month,0,date_trunc('month',current_date())))));
    --currmonth date default firstmonth;
    currmonth date ;
begin
 currmonth :=firstmonth;
 while (currmonth <=lastmonth) do 
    insert into temptable (colA, colB, colC, colD)
    select colA, colB, colC, colD
    from tableA 
    where date1 between :currmonth and dateadd(day,-1,dateadd(month,1,:currmonth))  and date2 = :currmonth;

 currmonth :=dateadd(month,1,currmonth);
end while;
end;
$
;

Answer 2

您在语句末尾缺少分号。

 currmonth :=dateadd(month,1,currmonth);