[SHARING] Case Insensitive WHERE Searches

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, 2010

Doug

Expert

posted

Hide Post

... 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, 2005

Ignored post by Doug posted

Show Post

Wep5622

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, 2010

Ignored post by Wep5622 posted

Show Post

ABT

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).

Posts: 561 | Registered: February 03, 2010

Ignored post by ABT posted

Show Post

Wep5622

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, 2010

Ignored post by Wep5622 posted

Show Post

ABT

Master

posted

Hide Post

I stand corrected. Thanks WEP!