Here is part of my query that is getting the error
WHEN D.ROLE_NAME LIKE '%' + B.Project_Phase + '%'
AND D.ROLE_NAME LIKE '%Clinical Consultant%'
THEN D.RESOURCE_NAME END AS "Clinical Consultant"
Am I missing some parenthesis? It works in SQL fine but can't get it to work in snowflake. Thanks for the help.
To concatenate string in Snowflake you need to use || operator instead of +:
CASE WHEN D.ROLE_NAME LIKE '%' || B.Project_Phase || '%'
AND D.ROLE_NAME LIKE '%Clinical Consultant%'
THEN D.RESOURCE_NAME END AS "Clinical Consultant"
When + is used then implicit conversion occurs and that is the reason of the error: Numeric value '%' is not recognized