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     [SHARING] Case Insensitive WHERE Searches

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SHARING] Case Insensitive WHERE Searches
 Login/Join
 
Master
posted
I don't know what's more surprising, that I just thought to try this or that it actually works in WebFOCUS.

Issue:
Our WebFOCUS data is case sensitive and searching for values can be a pain. I have seen people hold entire tables locally and use a define field to upcase the contents and search that list. I have seen very tedious manual upper casing statements that attempt to take all deviations into account, etc.

Resolution:
One of my favorite techniques in SQL is upcasing the search field and comparing that to an upcase'd value (i.e. WHERE Upper(fieldname) = 'SEARCHVALUE'). Turns out you can do the same thing in WebFOCUS (locase in the below example since CAR data is uppercased by default).

TABLE FILE CAR
PRINT
*
WHERE LOCASE(16,CAR,CAR) CONTAINS 'jag';
END


I didn't know this, so my assumption is others do not as well. Hope this helps someone.

- ABT


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Expert
posted Hide Post
Good One ... Clever !




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
That is indeed rather nice.

There's usually a downside to this kind of query though: the result of the lowercasing (or uppercasing) usually isn't indexed, which means that to find the matching records, the entire table needs to be scanned sequentially applying the expression to each row. That's fine on a small file like CAR, but not so great on files that contain several thousands of records and more.

In (some) databases, the solution is to create an index on the expression result. I know WF has some feature that allows you to create separate indices on files, is that feature perhaps capable of creating an index on LOCASE(16, CAR, CAR)?

(We don't have the index feature, so I can't test that here - might be a reason to get it though)


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
quote:
Originally posted by Wep5622:
... but not so great on files that contain several thousands of records and more.


The example table that I originally wrote/discovered this on contains several thousand rows and the search wasn't a big deal. Of course, this was a single table query, simple logic, etc. YMMV but point noted.

quote:
In (some) databases, the solution is to create an index on the expression result.


I'm not seeing how that approach is much different than what I started with (a local dump of records, DEFINE-ing the search field with an uppercase value and then searching/joining the result table from a local foc/ftm).


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Virtuoso
posted Hide Post
It's very different, as you don't need to modify the existing stored data to do so. You just create an extra index, for example:
CREATE INDEX car_lower_car_idx ON car (lower(car));


With that index in place, when you query CAR on lower(CAR), the query expression matches the index expression and the database will know to use that index.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
I stand corrected. Thanks WEP!


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
Gold member
posted Hide Post
Interesting; thanks!


WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.
 
Posts: 88 | Location: MI | Registered: July 23, 2009Report 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     [SHARING] Case Insensitive WHERE Searches

Copyright © 1996-2020 Information Builders