How to manipulate multi-value string parameters for a SQL Command in Crystal Reports

Question

I have a Crystal Report based on a SQL Command that, in part, consists of procedure names. What I'm trying to do is add a multi-value string parameter to the SQL Command such that the end users can enter partial procedure names and the report will return only those relevant procedures that string match.

For example, a user should be able to enter "%KNEE%" and "%HIP%" into the parameter and return all procedures that contain the words "KNEE" and "HIP". The problem is that I can't figure out how to manipulate the parameter value in the SQL to accomplish this. I've done this before with a report parameter (as opposed to a SQL Command parameter) by simply adding the line {table.procedure_name} like {?name match parameter} to the record selection formula, but taking the same approach in the SQL Command gets me an "ORA-00907: Missing right parenthesis" error.

Any suggestions on how I can manipulate the multi-value string parameter to accomplish this?

Answer 1

I dont like to post this as an answer because I don't care for the solution however it is the only way I have found to work around this. I have had to instruct users to enter '%KNEE%','%HIP%','%ETC%' at the parameter prompt. Then the {table.procedure_name} like {?name match parameter} should work in your SQL. Not optimal, especially for your scenario with the %. I would love to hear someone provide a better solution because I have wrestled with this many times.

Answer 2

Here's an approach:

SELECT column0
FROM   table0
INNER JOIN (
    SELECT trim('%' || x.column_value.extract('e/text()') || '%') SEARCH
    FROM  ( SELECT 'arm,knee' options FROM dual ) t,
    TABLE (xmlsequence(xmltype('<e><e>' || replace(t.options,',','</e><e>')|| '</e></e>').extract('e/e'))) x

) v ON column0 LIKE v.search

Use Oracle's XML functionality to convert a comma-delimited string to an equivalent number of rows, wrapping each clause with %%. Then join those rows to the desired table.

To use with CR, create a single-value, string parameter and add it to the code:

...
FROM  ( SELECT '{?search_param}' options FROM dual ) t,
...