Hi All,
I'm trying to create a procedure that will allow the user to enter a string of text and see results where a field contains the string they entered. I can get a case sensative version to work using a simple WHERE statement:

WHERE Campaign_Name CONTAINS '&CMPGVAR.enter a portion of a campaign name.';

However, I would like to make this case insensative. My plan for doing this is to create a defined field that has my Campaign_Name field in Upper case:

DEFINE FILE J2WDM_USERSTATUS
UPCMPG/A50=UPCASE(50, Campaign_Name11, 'A50');

This works fine. I can put that defined field in my output to confirm that it's upcasing ok.

Now I want to take the &CMPGVAR the the user entered and upcase it. I'm doing this with a SET command and an adjusted WHERE statement:

-SET &UPCMPGVAR = UPCASE(50, &CMPGVAR, 'A50');

WHERE UPCMPG CONTAINS '&UPCMPGVAR';

Now though I only get results where the string the user enters gets an exact match, i.e. it's as if my where statement says EQ rather than CONTAINS. For example, if I had a campaign named ABC and the user entered abc, it would work. But I also want the campaign name abc_new to get picked up in the results. abc_new is not picked up.

That last part might not make a ton of sense but does anyone see anything wrong with what I'm trying to do? I'm pretty bad with SET commands and I also get confused sometimes with when to put single quotes around a parameter and when not to.

Thanks!

This message has been edited. Last edited by: Phil_j2w,

If you use the -SET &ECHO = ALL; command to view the parsed code, you will see the problem. Your parsed WHERE clause will look something like this:

WHERE UPCMPG CONTAINS 'ABC                                               ';

All of those extra spaces from the 'A50' in your -SET command are included in the variable. So only values beginning with 'ABC' and followed by spaces or nulls will get selected. Values like 'ABC_NEW' will not.

To fix this problem, use the .LENGTH attribute to set the length of the variable to the exact length of the value it contains. Here is an example using the CAR file:

-SET &ECHO = ALL ;
-SET &CMPGVAR = 'eo';
-SET &UPCMPGVAR = UPCASE(&CMPGVAR.LENGTH, &CMPGVAR, 'A&CMPGVAR.LENGTH');

TABLE FILE CAR
 PRINT
  RETAIL_COST
  DEALER_COST
 BY COUNTRY
 BY CAR
 WHERE CAR CONTAINS '&UPCMPGVAR';
END

THANK YOU DAN!

That was exactly it, right down to the confusing 'abc I was seeing in the parsed WHERE clause.

After I read you post I had my problem fixed in 5 minutes. Really appreciate the help.