Snowflake Snowsql conditional breaking IF, SWITCH

Question

Question is around Snowflake snowsql. And need to do a conditional check to see if an ETL_Date is already insert into a table and if it has; exit the stored proc. ELSE if date is not inserted go ahead and insert data and then return success(zero) and exit. Any help will be greatly appreciated. Thanks.

Starting Point: What I have done so far my code.

IF ((SELECT IFNULL(MAX(ETL_DATE),'1900-01-01') FROM DB.TABLES.test_20200909) > CURRENT_DATE() )
{RETURN 0; break;}
else insert into DB.TABLES.test_20200909 (ETL_DATE,INSERT_VALUE)
values(CURRENT_DATE(),1)

And this gives me error: SQL compilation error: syntax error line 1 at position 0 unexpected 'IF'.

Goal/End Game : Have to place this into a stored proc. Which what I have put so far is:

CREATE or replace procedure   TABLE_DB.TABLES.test_20200909_1 ()
returns FLOAT
language javascript
as
$$

var test_1 =`IF ((SELECT MAX(ETL_DATE) FROM DB.TABLES.test_20200909) > CURRENT_DATE() )
{RETURN 0; break;}
else insert into DB.TABLES.test_20200909 (ETL_DATE,INSERT_VALUE)
values(CURRENT_DATE(),1)`;
var test_1_stmt =  snowflake.createStatement({sqlText: test_1});  
var test_1_res = test_1_stmt.execute();
return 0
$$

score 1 · Answer 1 · answered Sep 09 '20 at 18:20

If your goal is to create a Stored Procedure to check/manage your load, I would create one like the following :

create or replace procedure SP_CHECK_LOAD()
    RETURNS VARCHAR(256) NOT NULL
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
as $$    

function get_check_load()
{
    var v_out_check_load;
    rs = snowflake.execute ( { sqlText: `SELECT CASE WHEN MAX(ETL_DATE)>=CURRENT_DATE() THEN 0 ELSE 1 END AS CHECK_RESULT FROM DB.TABLES.test_20200909;` }  );
    if( rs.next())
    {
       v_out_check_load = rs.getColumnValue(1); // get check result
    }

    return v_out_check_load; 
}   
  
  var result = new String('Successfully Executed');
  var v_check = get_check_load();
  
  if ( v_check==1 )
  {
    //data not yet loaded -> load them
    try {
       
    var sql_command = `INSERT INTO DB.TABLES.test_20200909 (ETL_DATE,INSERT_VALUE) VALUES(CURRENT_DATE(),1)`;
 
    var stmt = snowflake.createStatement({sqlText: sql_command});
    var res = stmt.execute();  
    
    }
    catch (err) {
    result =  "Failed: Code: " + err.code + " | State: " + err.state;
    result += "\n  Message: " + err.message;
    result += "\nStack Trace:\n" + err.stackTraceTxt; 
    } 
 } // end if
 
 return result;

  $$;

Please note that I've used >= inside the check condition MAX(ETL_DATE)>=CURRENT_DATE() and not only > as in your example because in that case you would exclude current date load. Change it if this doesn't suit your needs.

Thank you @Saul I will try this out. – junketsu Sep 09 '20 at 21:32 — junketsu, Sep 09 '20 at 21:32

score 1 · Answer 2 · answered Sep 09 '20 at 19:45

Based on what you have mentioned, it looks like you don't even need a stored procedure to do this. You can use merge match which probably is more easier to setup

And need to do a conditional check to see if an ETL_Date is already insert into a table and if it has; exit the stored proc. ELSE if date is not inserted go ahead and insert data and then return success(zero)

Please see if below is what you are trying to achieve

CREATE OR REPLACE TABLE test_20200909(INSERT_VALUE NUMBER, ETL_DATE TIMESTAMP);
 
INSERT INTO TEST_20200909 VALUES(1, '2020-09-01 00:00:00.000');
INSERT INTO TEST_20200909 VALUES(1, '2020-09-02 00:00:00.000');
INSERT INTO TEST_20200909 VALUES(1, '2020-09-03 00:00:00.000');
INSERT INTO TEST_20200909 VALUES(1, '2020-09-04 00:00:00.000');
 
SELECT * FROM test_20200909;
 
MERGE INTO test_20200909 T
 USING(SELECT MAX(ETL_DATE) maxdate FROM test_20200909) S 
 ON S.maxdate=CURRENT_DATE()
 when NOT matched then 
  insert (INSERT_VALUE,ETL_DATE)  values (1, CURRENT_DATE());
  
SELECT * FROM test_20200909;

Thank you @Rajib ; I will also try this out. I sadly do need a stored proc. As the solution i presented above is the most basic; there are other select logic statements after this IF clause that do an actual insert into a table. — junketsu, Sep 09 '20 at 21:33
Sure you can embed this merge query also in your procedure, will help you reduce code. Let us know how you finally solve this. — Rajib Deb, Sep 09 '20 at 22:16
Hi Rajib MERGE works just as fine; and was it MS-SQL ; would be the only way I would merge data. Sadly(and I do mean that), am migrating code to Snowflake. And business needs a stored proc. — junketsu, Sep 10 '20 at 16:56

Snowflake Snowsql conditional breaking IF, SWITCH

2 Answers2