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

score 0 · Accepted Answer · answered Sep 04 '15 at 20:33

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.

Thank you for the clever solution. I ended up deploying this rendition of your suggestion as the Progress SQL-92 standard driver supports only two arguments in its CONCAT() function. `LOCATE( concat(',' , concat(customer.custid , ',')) , concat(',' , concat('{?customer_list}' , ','))) != 0` — Robert Hartshorn, Sep 08 '15 at 16:51

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

1 Answers1