As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at email@example.com for further assistance. Reference the community FAQ to learn more about the community.
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:
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
WHERE CAR CONTAINS '&UPCMPGVAR';
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007