snowflake-cloud-data-platform - Snowflake 过程中 While 循环中的 IF 条件
问题描述
我的程序如下所示,但在这里,值没有进入在 while 循环中写入的 if 条件。请让我知道是否有人解决了这个问题。我在来自 if 条件的插入命令中使用 v_CODE,因为没有值进入此 V_CODE,程序失败并显示以下错误Undefined 'FCT.V_CODE'
`CREATE OR REPLACE PROCEDURE CDW_PROC.SAMPLE_PROCEDURE(col1 FLOAT, COL2 VARCHAR, COL3 VARCHAR, COL4 VARCHAR, COL5 VARCHAR, COL6 VARCHAR)
RETURNS VARCHAR(10000)
LANGUAGE JAVASCRIPT
STRICT
EXECUTE AS OWNER
AS
$$
try
{
var v_FILTER_ID=0;
var v_A_TYPE=COL2
var v_TYPE=COL3
var v_FILTER_ATTRIBUTE
var v_ORG=COL4;
var v_FILTER_CONDITION,v_FILTER_VALUE,v_FILTER_DESC;
var v_BRAND ='v_BRAND';
var v_F_TIME_CUR = 'v_F_TIME_CUR';
var v_F_TIME_PREV='v_F_TIME_PREV';
var v_F_RANK='v_F_RANK';
var v_F_TIME_BUCKET='v_F_TIME_BUCKET';
var v_CODE='v_CODE';
var v_ID=col1;
var v_TIME_FRAME=COL5;
var v_WK_MTH_FLG=COL6;
var SEL_SQL=snowflake.execute({sqlText: "SELECT ID,TYPE,ORG,SUB_TYPE,FILTER_ID,FILTER_DESC,FILTER_ATTRIBUTE,FILTER_CONDITION,FILTER_VALUE,TIME_FRAME,WK_MTH_FLG FROM CDW_DB.FCT_TABLE WHERE ID=? AND TYPE =? AND SUB_TYPE =? AND ORG=? AND TIME_FRAME=? AND WK_MTH_FLG =?",binds:[v_ID,v_A_TYPE, v_TYPE, v_ORG, v_TIME_FRAME, v_WK_MTH_FLG]});
while(SEL_SQL.next())
{
var v_ID=SEL_SQL.getColumnValue(1);
var v_A_TYPE=SEL_SQL.getColumnValue(2);
var v_ORG=SEL_SQL.getColumnValue(3);
var v_TYPE=SEL_SQL.getColumnValue(4);
var v_FILTER_ID=SEL_SQL.getColumnValue(5);
var v_FILTER_DESC=SEL_SQL.getColumnValue(6);
var v_FILTER_ATTRIBUTE=SEL_SQL.getColumnValue(7);
var v_FILTER_CONDITION=SEL_SQL.getColumnValue(8);
var v_FILTER_VALUE=SEL_SQL.getColumnValue(9);
var v_TIME_FRAME=SEL_SQL.getColumnValue(10);
var v_WK_MTH_FLG=SEL_SQL.getColumnValue(11);
if (v_BRAND == 'v_'+v_FILTER_DESC.trim())
{
v_BRAND=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
else if ( v_F_TIME_CUR =='v_'+v_FILTER_DESC.trim())
{
v_F_TIME_CUR=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
else if (v_F_TIME_PREV=='v_'+v_FILTER_DESC.trim())
{
v_F_TIME_PREV=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
else if (v_F_RANK=='v_'+v_FILTER_DESC.trim())
{
v_F_RANK=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
else if (v_F_TIME_BUCKET=='v_'+v_FILTER_DESC.trim())
{
v_F_TIME_BUCKET=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
else if (v_CODE=='v_'+v_FILTER_DESC.trim())
{
v_CODE=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
v_WORK_SQL_ALRT_VOL1 = "create or replace table CDW_DB.TEMP_TABLE1(CUST_ID INTEGER,CALL_DATE VARCHAR(255),RNK INTEGER)";
var v_WORK_SQL_ALRT_VOL1_SQL=snowflake.createStatement({sqlText: v_WORK_SQL_ALRT_VOL1});
v_WORK_SQL_ALRT_VOL2 = `insert into CDW_DB.TEMP_TABLE1
select DISTINCT FCT.CUST_ID,
CAST(TO_CHAR(FCT.CALL_DATE_VOD ,'MM/DD/YYYY') AS VARCHAR(10)) AS CALL_DATE,
RANK() OVER (PARTITION BY FCT.CUST_ID ORDER BY FCT.DT_SK ASC,FCT.CREATEDDATE ASC) AS RNK
from CDW_VW.FCT_TABLE FCT WHERE CUST_ID <> -1
and FCT.${v_CODE}
and CALL_DATE_VOD > CURRENT_TIMESTAMP(0) QUALIFY RNK=1;`;
var v_WORK_SQL_ALRT_VOL2_SQL=snowflake.createStatement({sqlText: v_WORK_SQL_ALRT_VOL2});
var VOL1_RESULT=v_WORK_SQL_ALRT_VOL1_SQL.execute();
var VOL2_RESULT=v_WORK_SQL_ALRT_VOL2_SQL.execute();
var v_DROP_VOL_TBL1 =snowflake.execute({sqlText:"DROP TABLE CDW_DB.TEMP_TABLE1"});
var RESULT='Success';
return RESULT;
}
catch(err)
{
RESULT="Failed: Code: "+err.code+"\\n State: "+ err.state;
RESULT+="\\n Message: "+err.message;
RESULT+="\\n Stack Trace:\\n"+err.StackTraceTxt;
return RESULT;
}
$$
;
`
谢谢
解决方案
您拥有基于 SELECT 查询的值的 if/else 结构。所以关于 v_CODE 的 if/else 部分没有全部执行是正常的。例如,如果 v_F_TIME_CUR 等于 v_FILTER_DESC,则不会执行以下代码:
...
else if (v_CODE=='v_'+v_FILTER_DESC.trim())
{
v_CODE=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
我认为您需要使用单独的“如果”条件?
if (v_BRAND == 'v_'+v_FILTER_DESC.trim())
{
v_BRAND=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
};
if ( v_F_TIME_CUR =='v_'+v_FILTER_DESC.trim())
{
v_F_TIME_CUR=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
};
if (v_F_TIME_PREV=='v_'+v_FILTER_DESC.trim())
{
v_F_TIME_PREV=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
};
...
if (v_CODE=='v_'+v_FILTER_DESC.trim())
{
v_CODE=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}
推荐阅读
- visual-studio-code - 在 Mac 上的 Visual Studio Code 中打开终端时自动运行命令
- javascript - 如何使用数据库构建计数器以将值保存在 NextJS 中?
- python - 熊猫数据框迭代并与前一行计算的状态进行比较
- masstransit - 发布时的 MassTransit 子类未使用
- apache-spark - 从 PySpark 中的 AWS S3 读取时 InvalidAccessKeyId
- javascript - node.js 中的交叉导入
- r - 有没有办法在一张图上绘制具有不同衰减常数的指数衰减曲线?
- javascript - 忽略/覆盖Sinon错误替换`TypeError:尝试替换已经替换的foo`
- node.js - 在 Express、NodeJS 中流式传输 MP4(HTTP 206 部分内容)
- javascript - HTML 元素未固定在相同位置