IF condition in While loop in Snowflake procedure

Question

my procedure looks like below, but here, the values are not coming into if condition which is written in while loop. please let me know if someone fixes this. i am using v_CODE in insert command which is coming from if condition , since no values are coming into this V_CODE, procedure is getting failed and showing following error 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;
}
$$
;
`

Thanks

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-03-31T12:52:18

You have if/else structure based on the values coming for the SELECT query. So it is normal that the if/else part about v_CODE is not executed everything. For example if v_F_TIME_CUR equals to v_FILTER_DESC, then the following code will not be executed:

...
else if (v_CODE=='v_'+v_FILTER_DESC.trim())
{
v_CODE=v_FILTER_ATTRIBUTE+" "+v_FILTER_CONDITION+" "+v_FILTER_VALUE;
}

I think you need to use separate "if" conditions?

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;
}

IF condition in While loop in Snowflake procedure

1 Answers