using variable while inserting data into a table - snowflake procedure

Question

i am inserting a query from a variable into log table , but it is throwing error as below.

Failed: Code: 100183\n State:  P0000\n  Message:  SQL compilation error:
syntax error line 3 at position 33 unexpected 'MM'.
syntax error line 7 at position 58 unexpected 'Current_Timestamp'

please refer INSERT_VOL2 where we are iserting values into a log table by using parameters. where v_WORK_SQL_ALRT_VOL2 is having the query , same query we are trying to insert into a log table. but it is throwing error.

below is the procedure 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);
       
v_WORK_SQL_ALRT_VOL2 = `insert into CDW_US_DIMS_DB.PLANNED_CALL1
select DISTINCT  FCT.PFZ_CUST_ID,
CAST(TO_CHAR(FCT.CALL_DATE_VOD ,'MM/DD/YYYY') AS VARCHAR(10)) AS PLANNED_CALL_DATE,
RANK() OVER (PARTITION BY FCT.PFZ_CUST_ID ORDER BY FCT.DT_SK ASC,FCT.CREATEDDATE ASC) AS RNK
from CDW_US_PROCESSING_VW.VVA_REP_PLANNED_CALLS FCT WHERE PFZ_CUST_ID <> -1
and FCT.${v_F_SALES_ORG_CODE}
and CALL_DATE_VOD > CURRENT_TIMESTAMP(0) QUALIFY  RNK=1;`;

var v_WORK_SQL_EXEC=snowflake.createStatement({sqlText: v_WORK_SQL_ALRT_VOL2});

var VOL2_RESULT=v_WORK_SQL_EXEC.execute();

var INSERT_VOL2=snowflake.execute({sqlText: "INSERT INTO CDW_DB.log_tbl VALUES ("+v_ID+",'"+v_A_TYPE+"','"+v_WORK_SQL_ALRT_VOL2+"',Current_Timestamp)"});

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

If you are going to wrap a string (i.e. your INSERT statement) with single quotes then you can't have single quotes within the string — NickW, Apr 06 '21 at 17:36
Thank you Nick, if we want to insert with quotes how would we do that? — BalajiAWS, Apr 07 '21 at 07:29

score 0 · Answer 1 · answered Apr 07 '21 at 06:45

The pasted code has multiple errors that would prevent it from working, or even giving that specific error message:

A block needs to be closed before catch with a }.
The variable col1 should be COL1.
v_F_SALES_ORG_CODE is never defined, but used.

Once all that is fixed, everything works well until these lines:

var v_WORK_SQL_EXEC=snowflake.createStatement({sqlText: v_WORK_SQL_ALRT_VOL2});

var VOL2_RESULT=v_WORK_SQL_EXEC.execute();

But then we find this:

var INSERT_VOL2=snowflake.execute({sqlText: "INSERT INTO CDW_DB.log_tbl VALUES ("+v_ID+",'"+v_A_TYPE+"','"+v_WORK_SQL_ALRT_VOL2+"',Current_Timestamp)"});

The problem is that v_WORK_SQL_ALRT_VOL2 is a full sql query - and inserting a full SQL query string in the middle of an insert statement will simply not work.

This code needs a lot of cleaning and work, but at least we found where the two errors in the question are coming from.

Thanks Fellipe, the above code is not actual code that i am using, due to restrictions on the code, i have given sample code, but actual procedure has `}` in catch block. i am trying in different way to insert that values. like below `var INSERT_VOL2=snowflake.execute({sqlText: "INSERT INTO CDW_DB.LOG_TBL VALUES (:1,:2,:3,:4,:5,:6,:7)", binds:[v_ID,v_ORG,v_TYPE,v_SUB_TYPE,'VOL2',v_WORK_SQL_ALRT_VOL2,Current_Timestamp]});` here current_timestamp is giving error, is this the right way to bind current_timestamp ? — BalajiAWS, Apr 07 '21 at 08:27

score -1 · Accepted Answer · answered Apr 07 '21 at 08:41

-1

i am able to insert that values now, after assigning current_timestamp values to a variable and that variable is using in Insert query. Thank you for your suggestions. grately appriciated.

answered Apr 07 '21 at 08:41

BalajiAWS

137
1
12

using variable while inserting data into a table - snowflake procedure

2 Answers2