SQL WHERE IN Clause Returning no Rows when comparing with nested functions (Crystal Reports Command with SQL-92 DB)

Question

I am looking to delimit a Crystal Reports variable in my SQL query on behalf of the user so that they can provide input in the format of "customer1, customer2, customer 3" vs. "'customer1', 'customer2', customer3'". I can format the string on behalf of the user by passing the Crystal Reports parameter to the REPLACE() and CONCAT() functions in my query. When I place the code in the select list, the string is formatted appropriately for use in an IN expression. However, the query returns no rows once I migrate the code to the IN clause. I have copy and pasted the output from the select list into the IN statement and the rows are returned as expected. I have attempted to execute the same code in my SQL client against a string literal instead of the param, with same results, which means that this is not specific to Crystal Reports.

select
  customer.custid,
  customer.name
from pub.customer
where customer.custid IN (
  CONCAT('''', CONCAT(RTRIM(LTRIM(
    REPLACE('{?customer_param}', ',', ''','''))), '''')))

The code simply replaces commas with ',' and appends a single quote at the start and end of the parameter value. In my select list the code returns: 'customer1','customer2'.

I understand this method is not ideal performance-wise.

SQL Driver standard (SQL-92, Level 1 compliance via ODBC) DB: Progress 32-bit db (ABL Native with SQL Abstraction layer) Application: Epicor 9.05

Answer 1

The result of your CONCAT(...) is a string. For what you are trying to do to work, it would need to be interpreted as SQL code instead. That's not going to happen.

You could perhaps go about it differently. If your database has the LOCATE() function or something similar for searching for a substring within a string then you could perhaps write your filter condition like so:

WHERE LOCATE(
  CONCAT(',', customer.custid,     ','),
  CONCAT(',', '{?customer_param}', ',')) != 0

Slapping commas around everything ensures that you match only complete strings, and ensures that you can match the first and last strings in the list.