Long Select Statement

[piepmeier]

I am trying to create a report that selects information for our "tier 1" products. The list of tier 1 product is about 300 items long. If I put these into the formula editor in the following format (listing all 300 numbers)

{cqi-shp-rma.item} in ["80295", "80296", "80297", ...]

I get an error about too many characters in a single statement. Are there other ways to tackle this?

PS: Although the three numbers shown are in sequence that doesn't hold true for long.

 

[Steve978]

This is off the top of my head, so there may be more elegant solutions.

If you know the full list at runtime (you don't mention whether you're doing this programmatically i.e. from Visual Basic), pump them into a table (lootkuptable ). Then you can say:
join lootkuptable on {cqi-shp-rma.item} = {lookuptable.item}

along with whatever other conditions.

 

[synapsevampire]

That's a good approach, Steve. Perhaps even add to that using a View which already has these 2 tables joined.

Piep: Isn't there an indicator for Tier 1 other than the list???

You might also pass it by compressing the list as in:

{cqi-shp-rma.item} in ["80295" to "80350"]
or
{cqi-shp-rma.item} in ["80357" to "80499"]

This will convert to SQL and pass to the database.

The problem with this approach is that I believe it will return 802951, 8029510, 80295100, etc., so if those longer "strings" exist...

I would convert these to values and use them.

If this field contains values stored as strings, abruptly place your DBA on the other side of the back door, and create a SQL Expression in the report to convert {cqi-shp-rma.item} to a value.

Now reference this SQL Expression value in your record selection formula, as in:

{%item} in 80357 to 80360
or
{%item} in [80499,80567,80788...]

You may have to use more than one report to handle so many numbers.

This will prove faster and accurate.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

BTW, I meant subreports when I said more than one report.

And the additional View/table idea is the best.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[piepmeier]

You have given me several things to work on and it make take me a few days due to travel to try it out. I will let you know. Thanks for the input.