Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Not able to set the variable as column alias in create view in snowflake stored procedure

I am trying to create a view in snowflake stored procedure. I need one of the column from the variable. I tried following but getting following error,

Error
SQL compilation error:
syntax error line 4 at position 36 unexpected ‘(‘.
At Statement.execute, line 14 position 33 (line 26)

Error step,
‘${column1}’ AS concat(ID, ‘${column1}’)

 create or replace procedure sp_test_js()
returns float not null
language javascript
as     
$$  
var my_sql_command = "select iff(month(getdate()) <= 3,year(getdate())-1,year(getdate()))";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
var result_set1 = statement1.execute();
while (result_set1.next())  {
   var column1 = result_set1.getColumnValue(1);
   }
var my_sql_command2 = `create or replace view v_02_test as 
                  (select  
                   ID 
                   ,'${column1}' as col1
                   ,'${column1}' AS concat(ID, '${column1}')
                   from COV_TEST);`
var statement2 = snowflake.createStatement( {sqlText: my_sql_command2} );
var result_set2 = statement2.execute();                       
   return column1;
$$
;

Any suggestions?

Tried in Snowflake SQL worksheet

Expected Output

ID, COL1, ID_2024

1,2024,2024

Tags:

2

Answers


  1. 0

    I think the issue you are facing is happening because of the way you concat the column1 with _ID, because Snowflake SQL does not interpret the ${column1} inside a SQL command as part of its standard SQL syntax for defining column names dynamically.

    const dynamicColName = 'ID_' + column1;

const my_sql_command2 = `create or replace view v_02_test as 
                      (select  
                       ID, 
                       '${column1}' as col1, 
                       '${column1}' AS "${dynamicColName}"
                       from COV_TEST);`;
    Login or Signup to reply.
  2. 0

    The CONCAT function can only be used to return data, not to generate a column alias.

    When generating var my_sql_command2, instead, use the following syntax to create a dynamic column alias:

    ,‘${column1}’ as ID_${column1}

    You’re probably aware, but note that by using ‘…’ around the expression, the year value will be returned as a string, and not a number or date.

    Let me know how you get on!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search