Forum Discussion

azvineth's avatar
azvineth
New Contributor
3 years ago

Parameterizing Snowflake query in Snaplogic

I am trying to execute a Snowflake query to fetch some columns data in a table using Snowflake Execute snap.
Select “id”, “user_name”, “user_location”, “user_email” from t_user where “user_location” = ‘Chennai’ order by “id”;
Now in the above query I need to parameterize the filter used and so I introduced a pipeline paramter “u_loc” in the pipeline properties and changed the query as below. But it shows query parsing failed error on validation.
“Select “id”, “user_name”, “user_location”, “user_email” from t_user where “user_location” = '”+_u_loc+“’ order by “id”;”

5 Replies

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    3 years ago

    Hi @azvineth,

    Try the following:

    "Select 'id', 'user_name', 'user_location', 'user_email' from t_user where 'user_location' = '"+_u_loc+"' order by 'id';"

    If you place a mapper in front of the Snowflake Execute snap, you’ll be able to validate the query and see how it looks before sending it to Snowflake. Just for debugging purposes.

    Regards
    Bojan

  • Supratim's avatar
    Supratim
    Contributor III
    3 years ago

    @azvineth Can also try the below option -

    "Select id, user_name, user_location, user_email from t_user where user_location = ‘%s’ order by id ".sprintf(_u_loc)

    • azvineth's avatar
      azvineth
      New Contributor
      3 years ago

      @Supratim Nope. Throws the below error.
      Failed to execute query: Select id, user_name, user_location, user_email from t_user where user_location = ‘Chennai’ order by id
      Reason:
      SQL compilation error: error line 1 at position 7 invalid identifier ‘ID’

  • azvineth's avatar
    azvineth
    New Contributor
    3 years ago

    @bojanvelevski That did not work. Got the below error.

    Failed to execute query: Select ‘id’, ‘user_name’, ‘user_location’, ‘user_email’ from t_user where ‘user_location’ = ‘Chennai’ order by ‘id’
    SQL compilation error:
    syntax error line 1 at position 8 unexpected ‘‘id’’

  • ljupcho_machko1's avatar
    ljupcho_machko1
    New Contributor III
    3 years ago

    Hi @azvineth ,

    Can you please try with the below expression:

    “Select id, user_name, user_location, user_email from t_user where user_location = '” + _u_loc + “’ order by id;”

    Maybe quoted column names will not be needed ?