Snowflake use variable to set Database and Schema-CodePudding

I'm trying to create a variable in Snowflake UI worksheet and use the same in my queries to execute.

Below is a sample query and the error I'm getting. Any workaround to achieve this ?

  SET Database_pre= 'Sample_Pre';  
  SET Schema_pre='ACCOUNT';  
  SET Database_post= 'Sample_Post';   
  SET Schema_post='ACCOUNT';   
  SELECT * FROM   $Database_pre.$Schema_pre."PRODUCT" 
  EXCEPT 
  SELECT * FROM   $Database_post.$Schema_post."PRODUCT"

This is the error I'm getting while running the above query Error - SQL compilation error: syntax error line 1 at position 16 unexpected '$Database_pre'. syntax error line 3 at position 16 unexpected '$Database_post'.

CodePudding user response：

Here's an option ... not exactly ideal but it works :-)

  SET Database_pre= 'SF_TUTS';  
  SET Schema_pre='PUBLIC';  
  SET Database_post= 'SF_TUTS';  
  SET Schema_post='PUBLIC'; 

select 'SELECT * FROM '||   concat_ws('.', $Database_pre,$Schema_pre,'PRODUCT')||
' EXCEPT SELECT * FROM  '||   concat_ws('.', $Database_post,$Schema_post,'PRODUCT') run_this

CodePudding user response：

This is covered in the documentation: https://docs.snowflake.com/en/sql-reference/session-variables.html#using-variables-in-sql

You need to use IDENTIFIER() or TABLE() round your variables