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

... Clever !

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)

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

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.

I stand corrected. Thanks WEP!

Interesting; thanks!