Focal Point Banner


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 community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Set value to UPCASE then use in WHERE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Set value to UPCASE then use in WHERE
 Login/Join
 
Member
posted
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 Smiler 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,


WebFOCUS 7701
Windows 2008
All Formats

 
Posts: 17 | Registered: August 05, 2010Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Member
posted Hide Post
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.


WebFOCUS 7701
Windows 2008
All Formats

 
Posts: 17 | Registered: August 05, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Set value to UPCASE then use in WHERE

Copyright © 1996-2020 Information Builders