multi-value numeric parameter causes syntax error

[former_member594221]

I'm using Crystal Reports 2016 which directly connects to Sybase DB.

I have 3 sub-reports within 1 main report. Each of the sub-report's SQL require the same parameter

...WHERE myColumn in ({?whichValues})

Sub-report links are updated and work well prompting the user for the parameter value only once. That's perfect and it works well!

However, when I provide more than one parameter value I get an error: Failed to retrieve from the database: ...Syntax error near ',' on line 13. Line 13 contains the Where clause (noted in the code block above.)

What can I do to make the multiple values for the numeric parameter work with SQL?

Should there be differing approaches when the multi-parameter is a string versus a numeric?

Crystal likes to format commas into numeric values, could that be happening? How can I see the resulting SQL?

I realize multiple values in parameter forces the parameter object to become an array but I don't seem to be getting an array oriented error like, "out of bounds".

I've struggled with this for a week. I really hope you can help.

Thank you

[abhilash_kumar · Accepted solution]

Hi Kerrie,

Could you check if the prompt in the Command SQL is set to "allow multiple values"?

Also, try removing the round braces around {?WhichValues}, like so:

...WHERE myColumn in {?whichValues}

This works fine with SQL Server, I'm not sure if it would with Sybase DB

-Abhilash

[former_member594221 · Accepted solution]

Yes, "allow multiple values" is checked AND your solution works perfectly. Thank you so much!

[Former Member]

Hi Kerrie

Not used SYBASE but I have found Oracle prefers Crystal String parameters wrapped in single quote marks.

WHERE myColumn in '{?whichValues}'

might be worth a try.

Ian