I have come across an interesting case with our synonyms and filtering using a LIKE. On one of our synonyms, if we filter on the field customer name where customer_name like '%', we get problems if a user types in the wrong case. Example:

TABLE FILE STAR_INVENTORY
BY STAR_INVENTORY.D_CUSTOMER.CUSTOMER_NAME
WHERE STAR_INVENTORY.D_CUSTOMER.CUSTOMER_NAME LIKE 'F%';
END

This query returns every customer whose name starts with a capital F. If I change it to LIKE 'f%', it does not return any results. In the Database, most of the customer names are capitalized, but if a user types into a search box the wrong case, they will get no results. I'm trying to figure out what would cause a certain synonym to be case sensitive when searching with a LIKE.

Any thoughts?

This message has been edited. Last edited by: <Kathryn Henning>,

WebFOCUS 8007

MS SQL Server (and probably other RDMS) allow for case insensitive searches. What RDBMS are you using?

SQL Server.

The funny thing is that the lowercase like works for some but not others. We have a couple synonyms that reference the customer table. Some synonyms will return values on customer_name whether the like is upper or lower case. But then, I just discovered that if I query the customer table directly, the lower case returns no data:

TABLE FILE D_CUSTOMER
BY CUSTOMER_NAME
WHERE CUSTOMER_NAME LIKE 'f%'
END

In SQL Server, I think you can set the COLLATION at a column and table level. Perhaps you have a mixed set of COLLATION settings.

There may be a SQl Server command to override the collation of the table or column, one that you can set before the data retrieval. Or you can try the WebFOCUS command SET COLLATION, documented in Creating Reports With WebFOCUS Language Release 8.0 Version 08 > Sorting Tabular Reports > Controlling Collation Sequence:

How to Establish Binary or Case-Insensitive Collation Sequence
Add the following command to the server edasprof.prf profile:

SET COLLATION = {BINARY|SRV_CI|SRV_CS|CODEPAGE}

where:
BINARY
Bases the collation sequence on binary values.
SRV_CI
Bases collation sequence on the LANGUAGE setting, and is case-insensitive.
SRV_CS
Bases collation sequence on the LANGUAGE setting, and is case-sensitive.
CODEPAGE
Bases collation sequence on the code page in effect, and is case-sensitive. CODEPAGE
is the default value.
In most cases, CODEPAGE is the same as BINARY. The only differences are for Danish,
Finnish, German, Norwegian, and Swedish in an EBCDIC environment.

I've not had too much luck with this...