Aquí está el proceso almacenado que creé. Pero cuando trato de llamarlo, aparece un error de línea de análisis.
Create or replace procedure insert_into_staged1() RETURNS STRING NOT NULL LANGUAGE JAVASCRIPT AS $$ var sql_cmd = `Insert into staging_table(select xyz as xyz, abc as old_abc, case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1)) when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) else coalesce(replace(abc,'\"','\''),'') end as abc_new from table2)` var sql = snowflake.createStatement({sqlText: sql_cmd}); var result = sql.execute(); return 'success'; $$;Prueba esto:
Create or replace procedure insert_into_staged1() RETURNS STRING NOT NULL LANGUAGE JAVASCRIPT AS $$ var sql_cmd = ` Insert into staging_table select xyz as xyz, abc as old_abc, case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1)) when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then coalesce(replace(abc,'\"','\'''),'') end as abc_new from table2`; var sql = snowflake.createStatement({sqlText: sql_cmd}); var result = sql.execute(); return 'success'; $$;Intenté ejecutar su instrucción SQL y descubrí que faltaba THEN en uno de los casos.
The corrected function added. --ORiginal select xyz as xyz, abc as old_abc, case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1)) when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) else coalesce(replace(abc,'\"','\''),'') end as abc_new from table2 --Corrected select xyz as xyz, abc as old_abc, case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1)) when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 'Correction added' else coalesce(replace(abc,'\"','\''),'') end as abc_new from table2; create table staging_table AS select xyz as xyz, abc as old_abc ,case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1)) when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 'missing then' else coalesce(replace(abc,'\"','\''),'') end as abc_new from table2; create or replace table table2 (rx_code varchar2(100), split_part varchar2(100), xyz varchar2(120), abc varchar2(100)); select column1, case when column1=1 then 'one' when column1=2 then 'two' else 'other' end as result from (values(1),(2),(3)) v; Create or replace procedure insert_into_staged1() RETURNS STRING NOT NULL LANGUAGE JAVASCRIPT AS $$ var sql_cmd = `select xyz as xyz, abc as old_abc ,case when rx_code in ('1234','5432') and trim(split_part(upper(abc),'*',1)) in ('GOOGLE','APPLE','SAMSUNG') then trim(split_part(upper(abc),'*',1)) when rx_code in ('6012') and trim(split_part(upper(abc),'*',1)) then 'missing then' else coalesce(replace(abc,'\\"','\\''),'') end as abc_new from table2` var sql = snowflake.createStatement({sqlText: sql_cmd}); var result = sql.execute(); return 'success'; $$; call insert_into_staged1();