select criteria IN statement not in SQL 1

[beltmanjr]

Hi all,

I have a string type parameter called myParam and I manually give it the value '1','2','3','4'

In the select expert I tell it to find the matching records:
{SOME FIELD} in {?myParam}

I know that Crystal will eventually get me the correct results, but when looking at the SQL-query of Crystal it leaves the statement out completely.

When the table is a bit larger this is bad news.

Just in case you wondered why I want to do this:
In my main report I'm putting a string together, comma seperated, with data read from the database.

The subreport needs this data as a select criteria.

I tried to use a local Array stringVar and split the result of the string so I'm passing a real array to the IN statement, but it didn't help.

Hope someone knows how to fix this.
Many thanks
JR

 

[IanWaterman]

Which version of Crystal are you using.

Just as a test if you change select to

{SOME FIELD} in ['1','2','3','4']

Does that get parsed to SQL

Ian

 

[beltmanjr]

Ah, CR v XI R2 and 2008.
The {SOME FIELD} in ['1','2','3','4'] does get passed

 

[IanWaterman]

TRy replacing your parameter with this

@list
join({?myParam}, ",")

Ian

 

[IanWaterman]

what I meant was create thjat formula and in select

{SOME FIELD} in {@list}

or

{SOME FIELD} in [{@list}]

Ian

 

[beltmanjr]

Great stuff!
I am so HAPPY!

You gave me an idea which doesnt make sense but does work. This must be to do with using variables and the crystal passes.

So, I inserted the string parameter in a formula:

@list

{?myParam)

And the select criteria looks like:

{SOME FIELD} in split(@list,',')

The parameter has a simple string in it like: '1,2,3,4',
Hence we dont need to join, but do need to split to give the select statement IN an array.

 

[IanWaterman]

YOur solution does make sense. I did not appreciate that your param already had the "," separating string numbers.


Ian

 

[beltmanjr]

I dont get why

{SOME FIELD} in split(?myParam,',')

doesnt work though