I just used this method of performing a case-insensitive search and it works well.

In prior versions of WebFOCUS, you had to change the case of the string being searched for and the data column being searched to properly perform a case-insensitive search.

In WF v7.7 you can set the collation sequence which affects sorting, aggregation and WHERE statements.

SET COLLATION=SRV_CI
-RUN

"Bases collation sequence on the LANGUAGE setting, and is case-insensitive"

The WHERE statement is now case-insensitive.

Look up "Controlling Collation Sequence" in the documentation.

This message has been edited. Last edited by: Francis Mariani,

I'd like to see the underlying SQL statement that the iWay translator is using when hitting the database just to make sure the filter is passed on to the DB server to handle.

I'll try to allocate some time to test this ... one of these days.

The SQL doesn't look different - the value in the WHERE statement is however you type it.

Hmmm, that must be because apparently SQL Server does case-insensitive search by default. some other databases won't make them just like that.

In Oracle for instance, one needs to adjust session settings (NLS_SORT and NLS_COMP) previous to issuing a SELECT statement for that to happen, or resort to an ugly: WHERE UPPER(field) = 'VALUE', which usually renders any index on "field" useless unless such an index is a function-based one.

That's why I wondered if iWay were doing some 'magic' in the underlying SQL to make sure that "case-insensitivity" kicked in depending on the database adapter.

Thanks for checking that up Francis.

quote:

SET COLLATION=SRV_CI

I just tested this in Oracle 10g. As you said, the underlying SQL does not get impacted so the COLLATION stuff is happening at the WebFOCUS level and nothing gets passed to the database to support the case-insensitive search.

So, for databases that support that stuff by default such as SQL Server that settings makes sense but it's of no use when the DB technology is slightly different. My attempt to TABLE FILE oracle_table WHERE FIRST_NAME = 'fRaNcIs' rendered rien.


Based on what I understood after reading about SET COLLATION, before this setting was available any filter such as "WHERE FIRST_NAME EQ 'FRANCIS'" issued against a SQL Server database table from WebFOCUS would return values such as: FRANCIS, francis, Francis, etc. as that's how that DB operates but then WebFOCUS would perform a second-level filter to exclude anything different to 'FRANCIS' to honour the original WHERE condition.

Apparently with COLLATION=SRV_CI WebFOCUS no longer performs that second-level filter so every record fetched from the database is assumed to be valid.

This may still be used with Oracle and others provided one does stuff at the DB level to guarantee that case-insensitive searches are supported. COLLATION will make sure whatever we return as result will find its way into the reports.

Very interesting feature nevertheless.

Sorry for overusing of your name in my sample WHERE's above

Well, for the record.

it's doesn't work here...

( iSeries / DB2 )

quote:

it's doesn't work here ... iSeries / DB2 ...

Yup. I think very few databases support case-insensitive search by default. They don't do it unless explicitly instructed to do so, which is what I thought SET COLLATION did behind the scenes when TABLE FILE was converted to SQL but it really has nothing to do with the database but with the FOCUS engine directly.

That said, I can attempt to do that in Oracle by issuing something like:

alter session set NLS_SORT=BINARY_CI;
alter session set NLS_COMP=LINGUISTIC;

Any WHERE condition issued after that on a VARCHAR2 field will do a case-insensitive comparison.

Those alter sessions can be issued via SQL Passthru before the actual TABLE FILE request takes place.

There must be something similar for DB2 ...

This message has been edited. Last edited by: njsden,

Yes, you're unfortunately right - the setting appears to make no difference in DB2. I'll change the title of this thread.

Another great but half-baked idea. I read through the documentation and found nothing restricting successful use of this to particular types of dbms.

Creating Reports With WebFOCUS Language > Sorting Tabular Reports > Controlling Collation Sequence

Is it possible this must be set in the server profile and not in a fex?

quote:

Another great but half-baked idea

I would say that the documentation should make it very clear that this is a FOCUS-specific setting non applicable to RDBMS (unless they do it by default such as SQL Server). That's not too bad I guess ... after all, we all know that JOIN ... TO UNIQUE ... has a semantically and technically different meaning in FOCUS vs. databases so COLLATION would probably fall under the same group.

quote:

Is it possible this must be set in the server profile and not in a fex?

What in particular Francis? SET COLLATION or the specific database session-level settings?

Well, either I guess but there are drawbacks to just go ahead and alter NLS settings in Oracle. If I'm not mistaken, if one changes NLS_SORT and NLS_COMP then any existing indices will be useless to support the new case-insensitive query.

This may or may not be the case in other databases out there, which is, I think, why IBI does not try to "force" the functionality onto those DB servers but just keep it at the FOCUS level. If the database supports it, SET COLLATION will honour those case-insensitive filters which was not the case before that setting came into effect.

Definitely the documentation about SET COLLATION should make that clearer.

I haven't looked at IBI's Adapter for Oracle documentation to see if there is any mention of COLLATION and how to make it work. I'll try to search to see what I can find.

Yes, the SET COLLATION setting. It is mentioned that it should be added to the server profile (I suppose EDASPROF), but I can't see how that is different than doing it in a fex.