How to filter a number field against a multiple string parameter or string array?

Question

We have a Crystal Report that lets the user pick which of several fields to filter against. In the record selection formula, we have code like the following:

if {?First Selection Type} = "CUSTOMER CODE" then {foo.CustCode} in {?First Selection Value} 
 else if {?First Selection Type} = "ORDER" then {bar.OrderNum} in {?First Selection Value}
 ...

The {?First Selection Value} parameter is defined as a multiple string parameter, since some of the choices need to use strings.

Unfortunately, the bar.OrderNum field is numeric.

If I try to put in the code as above, Crystal Reports gives the error "A number range is required here".

If I put CStr({bar.OrderNum}) it saves without errors, but does not include that criteria when generating the SQL statement, resulting in atrocious performance as it pulls every order down from the database and searches them client side.

If I put ToNumber({?First Selection Value}) or CDbl({?First Selection Value}), it gives the error "This array must be subscripted. For example: Array [i]."

I need some way to make this criteria go to the database server, without making the parameters more confusing for the users.

In case it matters, we are using Crystal Reports 11.0.0.895 against a Progress OpenEdge 10.1B03 database.

Answer 1

The solution I found that seems to work is to create a SQL Expression named OrderNumStr with the following expression:

CAST("bar1"."SOrderNum" AS VARCHAR(10))

and then change the record selection to:

if {?First Selection Type} = "CUSTOMER CODE" then {foo.CustCode} in {?First Selection Value} 
 else if {?First Selection Type} = "ORDER" then {%OrderNumStr} in {?First Selection Value}
 ...

This at least sent the criteria to the server side, though it is preventing the database server from using indexes, so it is not ideal. It is still a massive performance improvement over what it was doing before.

P.S. (I needed to rename the table from bar to bar1 in the database expert to get the SQL expression to work. This may be Progress specific though.)