SSRS (Snowflake): Select/Filter column by string with a comma in it

Polyglot Projects Other Tools
1

A column in a Table I am querying has values ‘183160,WELD’, ‘87491,WELD’, ‘874910,WELD’, ‘87491N,WELD’.

In Snowflake, I started with this Case Statement:

When ProcedureCode In('183160,WELD', '87491,WELD', '874910,WELD', '87491N,WELD') Then 'CHL'

While it worked fine in Snowflake, it errored when I used it in SSRS for Snowflake. [See SSRS error message further down this post.

Believing that there was an issue with the fact that there were commas in the ProcedureCode values, I switched my SQL to:

When (Left(cspc.procedureCode, 6) In ('183160', '874910', '87491N') And Right (cspc.procedureCode, 4) = 'WELD') Then 'CHL'
When (Left(cspc.procedureCode, 5) = '87491' And Right (cspc.procedureCode, 4) = 'WELD')) Then 'CHL'

This also worked fine in Snowflake but gave me a similar error in SSRS:

ERROR [22023] SQL compilation error: Can not convert parameter '((SUBSTR(SYS_VW.PROCEDURECODE_7, 1, 6)) IN (‘183160’, ‘874910’, ‘87491N’)) AND ((RIGHT(SYS_VW.PROCEDURECODE_7, 4)) = ‘WELD’) of type [BOOLEAN] into expected type [VARCHAR (16777216)]

I initially wanted to use an escape character for the commas in the 4 values but I was not able to figure out how to do that.

I noticed that the SSRS message had ‘interpreted’ my Left() function to a SUBSTR() function but don’t know if that means I need to change my LEFT() to SUBSTR()?

I am also not certain what the “type [BOOLEAN]” in the Error Message refers to.

I’d appreciate any help/pointers on how to resolve this.

2

Hi @MOKSHAGNYA_A,
SSRS is a Microsoft SQL product. That isn’t a database which is open-source like MySQL/MariaDB/PostgreSQL. You might have better luck by posting on a forum that is more targeted at MSSQL-related queries.