Snowflake IF ELSE存储过程逻辑

Question

我有一个tsql代码，我正在尝试移植到Snowflake上的存储proc中。我有一个版本1的proc构建并投入生产。但现在必须基于IF-ELSE逻辑进行查找更改。我可以按照T-sql上的建议轻松完成，但是如何在Snowflake上完成相同的操作呢？下面是我想要转换成Snowflake的TSQL端的代码。

if object_id ('tempdb..#CTE_Y_Date') is not null drop table #CTE_Y_Date;
if object_id ('tempdb..#CTE_PY_Date') is not null drop table #CTE_PY_Date;

declare @week53_check int 
,@Cal_Date int = 20210101
select @week53_check = Calendar_Week FROM calendar where  cast([Calendar_Date] as varchar) = @Cal_Date
--print @week53_check


SELECT [Calendar_Date]
      ,[Fiscal_Year]
      ,[Fiscal_Period]
      ,[Calendar_Week]
      ,[Fiscal_Period_Week]
      ,[Fiscal_Period_Day]
      ,[Begnning_Date_PV]
      ,Fiscal_Quarter = case when  Fiscal_Period between 1 and 3 then 1
                            when  Fiscal_Period between 4 and 6 then 2
                            when  Fiscal_Period between 7 and 9 then 3
                            when  Fiscal_Period between 10 and 12 then 4 else 0 end
into #CTE_Y_Date
  FROM calendar
  where  cast([Calendar_Date] as varchar) = @Cal_Date

  -- select * from #CTE_Y_Date

if @week53_check = 53 
(
SELECT a.[Calendar_Date]
      ,a.[Fiscal_Year]
      ,a.[Fiscal_Period]
      ,a.[Calendar_Week]
      ,a.[Fiscal_Period_Week]
      ,a.[Fiscal_Period_Day]
      --,[Fiscal_Date]
     -- ,[Begnning_Date]
      ,a.[Begnning_Date_PV]
       ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                            when  a.Fiscal_Period between 4 and 6 then 2
                            when  a.Fiscal_Period between 7 and 9 then 3
                            when  a.Fiscal_Period between 10 and 12 then 4 else 0 end
into #CTE_PY_Date
  FROM calendar a
  left join #CTE_Y_Date b on   a.[Fiscal_Year] = b.[Fiscal_Year] and a.[Fiscal_Period_Day] = b.[Fiscal_Period_Day]
  where  a.[Fiscal_Year] = b.[Fiscal_Year] and a.Calendar_Week = 1
)

else  insert into #CTE_PY_Date
SELECT a.[Calendar_Date]
      ,a.[Fiscal_Year]
      ,a.[Fiscal_Period]
      ,a.[Calendar_Week]
      ,a.[Fiscal_Period_Week]
      ,a.[Fiscal_Period_Day]
      --,[Fiscal_Date]
     -- ,[Begnning_Date]
      ,a.[Begnning_Date_PV]
       ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                            when  a.Fiscal_Period between 4 and 6 then 2
                            when  a.Fiscal_Period between 7 and 9 then 3
                            when  a.Fiscal_Period between 10 and 12 then 4 else 0 end

  FROM calendar a
  left join #CTE_Y_Date b on  a.Fiscal_Period = b.Fiscal_Period and a.Fiscal_Period_Week = b.Fiscal_Period_Week and a.[Fiscal_Period_Day] = b.[Fiscal_Period_Day]
  where  a.[Fiscal_Year] = b.[Fiscal_Year] - 1

select * from #CTE_PY_Date

雪花代码版本1在这里。我需要从上面的Tsql完成IF块。

CREATE or replace procedure  "SP_53WeeksTest"()
returns FLOAT
language javascript
as
$$
//drop temp tables

var  dtt_CTE_Y_Date = 'drop table if exists CTE_Y_Date';
var dtt_CTE_Y_Date_stmt =  snowflake.createStatement({sqlText: dtt_CTE_Y_Date});  
var dtt_CTE_Y_Date_res = dtt_CTE_Y_Date_stmt.execute();

var  dtt_CTE_PY_Date = 'drop table if exists CTE_PY_Date';
var dtt_CTE_PY_Date_stmt =  snowflake.createStatement({sqlText: dtt_CTE_PY_Date});  
var dtt_CTE_PY_Date_res = dtt_CTE_PY_Date_stmt.execute();

var cttCTE_Y_Date = `create temporary table CTE_Y_Date (CALENDAR_DATE NUMBER(38,0)
                                   ,FISCAL_YEAR NUMBER(38,0)
                                   ,FISCAL_PERIOD NUMBER(38,0)
                                   ,CALENDAR_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_DAY NUMBER(38,0)
                                   ,BEGNNING_DATE_PV DATE
                                  ,Fiscal_Quarter NUMBER(38,0)
                                  )`;
var cttCTE_Y_Date_stmt = snowflake.createStatement({sqlText: cttCTE_Y_Date});
var cttCTE_Y_Date_res= cttCTE_Y_Date_stmt.execute();

var CTE_Y_Date_insert = `insert into CTE_Y_Date
select CALENDAR_DATE,FISCAL_YEAR,FISCAL_PERIOD,CALENDAR_WEEK,FISCAL_PERIOD_WEEK,FISCAL_PERIOD_DAY,BEGNNING_DATE_PV
 ,case when  Fiscal_Period between 1 and 3 then 1
when  Fiscal_Period between 4 and 6 then 2
when  Fiscal_Period between 7 and 9 then 3
when  Fiscal_Period between 10 and 12 then 4 else 0 end as Fiscal_Quarter
from FISCAL_CALENDAR
where BEGNNING_DATE_PV = (select max(BUSINESS_DATE) from RAW_TABLE_DB.POS_TABLES.PS_TRANS_HEADER)`;
var CTE_Y_Date_insert_stmt = snowflake.createStatement({sqlText: CTE_Y_Date_insert});
var CTE_Y_Date_res= CTE_Y_Date_insert_stmt.execute();

var cttCTE_PY_Date = `create temporary table CTE_PY_Date (CALENDAR_DATE NUMBER(38,0)
                                   ,FISCAL_YEAR NUMBER(38,0)
                                   ,FISCAL_PERIOD NUMBER(38,0)
                                   ,CALENDAR_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_WEEK NUMBER(38,0)
                                   ,FISCAL_PERIOD_DAY NUMBER(38,0)
                                   ,BEGNNING_DATE_PV DATE
                                  ,Fiscal_Quarter NUMBER(38,0)
                                  )`;
var cttCTE_PY_Date_stmt = snowflake.createStatement({sqlText: cttCTE_PY_Date});
var cttCTE_PY_Date_res= cttCTE_PY_Date_stmt.execute();

var CTE_PY_Date_insert = `insert into CTE_PY_Date
select A.CALENDAR_DATE,A.FISCAL_YEAR,A.FISCAL_PERIOD,A.CALENDAR_WEEK,A.FISCAL_PERIOD_WEEK,A.FISCAL_PERIOD_DAY,A.BEGNNING_DATE_PV
 ,case when  A.Fiscal_Period between 1 and 3 then 1
when  A.Fiscal_Period between 4 and 6 then 2
when  A.Fiscal_Period between 7 and 9 then 3
when  A.Fiscal_Period between 10 and 12 then 4 else 0 end as Fiscal_Quarter
from FISCAL_CALENDAR AS A
LEFT JOIN CTE_Y_Date AS B ON A.FISCAL_PERIOD=B.FISCAL_PERIOD AND A.FISCAL_PERIOD_WEEK=B.FISCAL_PERIOD_WEEK AND A.FISCAL_PERIOD_DAY=B.FISCAL_PERIOD_DAY
WHERE A.FISCAL_YEAR = (B.FISCAL_YEAR - 1)`;
var CTE_PY_Date_insert_stmt = snowflake.createStatement({sqlText: CTE_PY_Date_insert});
var CTE_PY_Date_res= CTE_PY_Date_insert_stmt.execute();

return 0;
$$

Answer 1

T-SQL存储过程是一个自上而下的流。Snowflake存储过程(目前)是JavaScript，应该模块化。

要查找单个值并将其存储在变量中，您需要使用一个辅助函数，如下所示：

function getSingleValueQuery(columnName, queryString) {
    var out;
    cmd1 = {sqlText: queryString};
    stmt = snowflake.createStatement(cmd1);
    var rs;

    rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(columnName);
    return out;
}

这使得执行重复性任务变得更容易和更具可读性，比如查看、运行查询和返回结果，而您只希望返回单行。

然后，您可以调用该函数并将结果存储在一个变量中，根据您的数据结构，如下所示：

var calDate = 20210101;
var week53_check = getSingleValueQuery('CALENDAR_WEEK', 
          `select CALENDAR_WEEK from CALENDAR where CALENDAR_DATE = ${calDate}`);

然后，只需在week53_check条件下设置一个if-then块，如下所示：

if (week53_check == 53) {
  // Do this
} else 
  // Do something else
}

为了获得最好的可读性，我建议使用helper函数模块化您的代码：

if (week53_check == 53) {
  doWeek53Insert();
} else 
  doNonWeek53Insert();
}

这样你就可以有两个这样的函数了：

function doWeek53Insert() {
    var out;
    cmd1 = {sqlText: `insert into CTE_Y_Date... (etc)... `};
    stmt = snowflake.createStatement(cmd1);
    var rs;
    rs = stmt.execute();
}

Answer 2

我认为使用一些帮助程序可以更容易地将代码转移到snowflake javascript。这是我使用的一个常见的代码片段：

// -------------- EXEC SNIPPET BEGIN --------------
var _RS, ROW_COUNT, _ROWS, MESSAGE_TEXT, SQLCODE, SQLSTATE, ERROR_HANDLERS, ACTIVITY_COUNT, INTO;
var fixBind = (arg) => arg instanceof Date && arg.toISOString() || arg || null;
var fetch = (count,rows,stmt) =>
    (count && rows.next() && Array.apply(null,Array(stmt.getColumnCount())).map((_,i) => rows.getColumnValue(i + 1))) || [];
var INTO = () => (ROW_COUNT && fetch(ROW_COUNT,_ROWS,_RS)) || [];
var EXEC = function (stmt,binds,noCatch) {
    try {
        binds = (binds && binds.map(fixBind)) || binds;
        _RS = snowflake.createStatement({sqlText : stmt,binds : binds});
        _ROWS = _RS.execute();
        ROW_COUNT = _RS.getRowCount();
        ACTIVITY_COUNT = _RS.getNumRowsAffected();
        return { INTO: () => INTO() };
    }
    catch(error) {
        MESSAGE_TEXT = error.message;
        SQLCODE = error.code;
        SQLSTATE = error.state;
        if (!noCatch && ERROR_HANDLERS) ERROR_HANDLERS(error);
        var newError = Error(`SQLCODE: ${SQLCODE} MESSAGE: ${MESSAGE_TEXT}`)
        throw newError;
    }
};
// -------------- EXEC SNIPPET END --------------

我经常使用它，所以我创建了一个简单的工具：https://github.com/orellabac/SnowJS-Helpers/，它允许我只扩展用于部署的代码：

例如，取自上面的代码：

    CREATE or replace procedure  SP_53WeeksTest()
    returns FLOAT
    language javascript
    as
    $$
    "@USING_EXEC";
    EXEC(`DROP TABLE IF EXISTS tempdb."#CTE_Y_Date";`)
    EXEC(`DROP TABLE IF EXISTS tempdb."#CTE_PY_Date";`)
    
    var week53_check = 0;
    var Cal_Date = 20210101;
    
    EXEC(`select Calendar_Week FROM calendar where  cast(Calendar_Date as varchar) = ?`,[Cal_Date])
    [week53_check] = INTO();
    //print @week53_check
    
    EXEC(`
         INSERT INTO "CTE_Y_Date"
         SELECT Calendar_Date
          ,Fiscal_Year
          ,Fiscal_Period
          ,Calendar_Week
          ,Fiscal_Period_Week
          ,Fiscal_Period_Day
          ,Begnning_Date_PV
          ,Fiscal_Quarter = case when  Fiscal_Period between 1 and 3 then 1
                                when  Fiscal_Period between 4 and 6 then 2
                                when  Fiscal_Period between 7 and 9 then 3
                                when  Fiscal_Period between 10 and 12 then 4 else 0 end
        FROM calendar
        where  cast(Calendar_Date as varchar) = ?]`,[Cal_Date]);
    
      // select * from #CTE_Y_Date
    
    if (week53_check == 53 )
    {
    EXEC(`
    insert into "#CTE_PY_DATE"
    SELECT a.Calendar_Date
          ,a.Fiscal_Year
          ,a.Fiscal_Period
          ,a.Calendar_Week
          ,a.Fiscal_Period_Week
          ,a.Fiscal_Period_Day
          --,Fiscal_Date
         -- ,Begnning_Date
          ,a.Begnning_Date_PV
           ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                                when  a.Fiscal_Period between 4 and 6 then 2
                                when  a.Fiscal_Period between 7 and 9 then 3
                                when  a.Fiscal_Period between 10 and 12 then 4 else 0 end
      FROM calendar a
      left join "#CTE_Y_Date" b on   a.Fiscal_Year = b.Fiscal_Year and a.Fiscal_Period_Day = b.Fiscal_Period_Day
      where  a.Fiscal_Year = b.Fiscal_Year and a.Calendar_Week = 1
    )`,[]);
    }
    else {
    EXEC(`insert into "#CTE_PY_Date"
    SELECT a.Calendar_Date
          ,a.Fiscal_Year
          ,a.Fiscal_Period
          ,a.Calendar_Week
          ,a.Fiscal_Period_Week
          ,a.Fiscal_Period_Day
          --,Fiscal_Date
         -- ,Begnning_Date
          ,a.Begnning_Date_PV
           ,Fiscal_Quarter = case when  a.Fiscal_Period between 1 and 3 then 1
                                when  a.Fiscal_Period between 4 and 6 then 2
                                when  a.Fiscal_Period between 7 and 9 then 3
                                when  a.Fiscal_Period between 10 and 12 then 4 else 0 end
    
      FROM calendar a
      left join "#CTE_Y_Date" b on  a.Fiscal_Period = b.Fiscal_Period and a.Fiscal_Period_Week = b.Fiscal_Period_Week and a.Fiscal_Period_Day = b.Fiscal_Period_Day
      where  a.Fiscal_Year = b.Fiscal_Year - 1`);
      // If you want to return this last statement
      // This is want i do, I use a temporary table which I drop before use to make sure it was empty and the from the caller
      // SP just select from RESULTS_SP_53WeeksTest
      EXEC("DROP TABLE IF EXISTS RESULTS_SP_53WeeksTest",[]);
      EXEC(`CREATE TEMPORARY TABLE RESULTS_SP_53WeeksTest AS select * from "#CTE_PY_Date"`,[]);
}
      $$;

我只是在部署之前使用我的工具展开EXEC代码片段，但是如果您想要复制粘贴EXEC代码片段而不是"@USING_EXEC“注释