Using IN and LIKE together? 2

[cully651]

I have a situation where I need to use a multi-select parameter with the IN and LIKE operators together, something like this;

{TABLE.FIELD} in (like ("*" + {?ARRAY_PARAMETER} + "*"))

I know I can do this
{TABLE.FIELD} like ("*" + {?SINGLE_VALUE_PARAMETER} + "*")

or this
{TABLE.FIELD} in {?ARRAY_PARAMETER}

but can't figure out how to make them work together.

Any suggestions?

 

[lbass]

I think you have to use a variable to do this. Use the following for your record selection formula:

whilereadingrecords;
numbervar i;
numbervar j := ubound({?parm});
stringvar x;

for i := 1 to j do(
if {table.field} like "*"+{?parm}+"*" then
x := x + {table.field} + ", "
);
{table.field} in x

-LB

 

[synapsevampire]

Try:

(
if ubound({?My Parameter}) > 0 then
{Customer.Contact Title} like "*" & {?My Parameter}[1] & "*"
)
0r
(
if ubound({?My Parameter}) > 1 then
{Customer.Contact Title} like "*" & {?My Parameter}[2] & "*"
)
0r
(
if ubound({?My Parameter}) > 2 then
{Customer.Contact Title} like "*" & {?My Parameter}[3] & "*"
)

This will also pass the SQL to the database.

-k

 

[synapsevampire]

LB: Try that example, yours won't pass the SQL to the database.

-k

 

[cully651]

It can't handle a loop in the selection criteria? I'll probably have to find another way to handle it.

 

[synapsevampire]

Cully: I'm not using a loop, and you're mistaken, loops do work, at least in CR XI, but it doesn't pass the criteria to the database, just use what I supplied...

Posting it doesn't needs to include whatever error messages are returned or the observations if it does work, this is a technical forum, so please state specifics.

-k

 

[lbass]

Yes, I know it doesn't pass, but I thought the loop would work better for potentially changeable or large numbers of multiple selections, rather than hard coding it each time.

-LB

 

[synapsevampire]

From someone concerned with gathering performance tips?

-k

 

[lbass]

I reserve the right to be inconsistent. In some cases, speed isn't the primary or only performance issue.

-LB

 

[synapsevampire]

<chuckling>

I'm consistently more inconsistent...

-k

 

[RustyAfro]

This is good info, both solutions (the loop or the hard code) have good uses, a start to both of ya ;-)

Joel

 

[cully651]

Sorry I haven't repsonded sooner, I've been out of town. My parameter has dozens of codes, and they change every year.

synapsevampire, the hard coding you supplied will work as along as I have way more "ORs" than they could ever dream up in numbers of codes. It's not a bad solution, thank you.

Lbass, your solution is what I hoped would work. Oddly enough it gives a very strange "like" operation to both sides of the equation. I've never seen anything like it. If I have 2 codes, 'e' and 'cheese', and I selected 'e' I'd expect to see 'cheese' as well. BUT if I seleted 'cheese' I was seeing 'e' as well. I can't figure that one out.

Thank you both for helping out!

 

[synapsevampire]

How many do you anticipate them using?

It's easy enough to copy and paste them in.

I don't see how LB's would work anyway.

What you want are discreet like conditions, and an IN doesn't provide that, it didn't test out properly here either.

The solution I offered provides what you want, plus it will pass the criteria to the database to provide optimal speed.

It may mean that you have to copy and paste for a few minutes...

-k

 

[cully651]

synapsevampire, you're right about the speed. Having it show up in the SQL is a big factor.

However, when I used lbass's code in a formula and included the forumla in the selection criteria, then it worked fine. It doesn't show up in the SQL (in fact I don't get any where clause at all) but the result set is correct. For a smaller dataset, lbass's solution would be preferred because you don't need to anticipate how many codes they might use (currently 16, and growing every year).

I try to reserve hard coding as the method of last resort. It has always come back to haunt me, and usually at the worst time and in the worst way.