CODEWITHSUNDEEP
sqlstringwhere-clausesnowflake-cloud-data-platform
Shrini
Shrini

Reputation: 105

SQL compilation error: error line 2 at position 56 invalid identifier

I need small help in resolving error. I have logged in to Snowflake with ACCCOUNTADMIN role (for worksheet as well). I can run query successfully when I don't add WHERE clause. When I add WHERE clause it throws error. Here are both queries.

Success Query:

SELECT NAME, CREATED_ON, EMAIL, MUST_CHANGE_PASSWORD, EXPIRES_AT, PASSWORD_LAST_SET_TIME 
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."USERS";

Error Query:

SELECT NAME, CREATED_ON, EMAIL, MUST_CHANGE_PASSWORD, EXPIRES_AT, PASSWORD_LAST_SET_TIME 
FROM "SNOWFLAKE"."ACCOUNT_USAGE"."USERS" 
WHERE "NAME" = "MYTESTUSER_1";

Error:

SQL compilation error: error line 2 at position 56 invalid identifier 'MYTESTUSER_1'

There is no issue with WHERE clause. Corresponding user is existing. I have check it for a few other users as well. Same error.

Please help.

Upvotes: 3

Views: 25948

Answers (1)

GMB
GMB

Reputation: 222672

In standard SQL, which Snowflake follows in that regard, double quotes stand for identifiers (column names, table names, and so on). In the context of the where clause, the database understands "MYTESTUSER_1" as the name of a column (which obviously, does not exist), hence the error that you are getting.

You want a literal string, so you need single quotes instead. That is, change:

WHERE "NAME" = "MYTESTUSER_1"

To:

WHERE "NAME" = 'MYTESTUSER_1'

Upvotes: 8

Related Questions