I’m trying to generate where clauses, and one issue I’m coming across is case sensitivity. For example, I have an employee named ‘Judy’ in my database. However, my user may fat finger the search result, i.e. type in ‘JudY’, and I still want the result to come back.

i.e., I have the following report:

TABLE FILE BV_EMPLOYEE
PRINT
'BV_EMPLOYEE.PERSONAL_INFORMATION.FIRSTNAME'
HEADING
""
FOOTING
""
WHERE BV_EMPLOYEE.PERSONAL_INFORMATION.FIRSTNAME EQ 'JudY';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

Through SQL trace, I can determine that web focus is generating the following SQL

set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

SELECT
T1."FirstName"
FROM Employee T1
WHERE
(T1."FirstName" = 'JudY')

And since my database is not case sensitive, this returns results.

However, it seems that WebFocus then does do a case sensitive filter here, eliminating my results from SQL server. Is there a setting that I can use to turn this behavior off? I know if I absolutely had to, I could use the UPCASE function, but then my where clause isn’t going to be passed to my relational database, which will reduce my query efficiency. Because of my database size, this is not acceptable...

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

Look at the settings for your RDBMS for case insensitive results. If I remember correctly, this has been asked about MS SQL before and possibly Oracle. A search on "mixed case" or something like that might give you the relevant post.

T

This message has been edited. Last edited by: Tony A,

DB2 can also be configured for case insensitivity. If the DBMS can handle it, this is the easiest way to approach the problem.

Otherwise there are LCWORD, UPCASE, and LOCASE functions. I would format your input parameters to match the data field formats. If you do it the other way (matching to a reformatted db field), as you mentioned, you would take an efficiency hit.

Last note, based on the SQL you've shown, if the db was truly case insensitive (which in most cases you have to explicitly set) the query should work. Try using a native query tool for SQL Server and plug in that value and see what results you get. If it does return "Judy" then it's still something in WF. If not, then you need to get the correct setting for case insensitivity for the DBMS.

Yes, the database is set up for case insensitive. I opened a case with ibi, and the issue appears to be with the WebFocus SQL Translator. Even if the database brings back the result, it re-evaluates the where clauses in a case sensitive manner. There's supposed to be a resolution to this in version 7.10. I'll be looking forward to that coming out, as that will provide the best solution to my problem. Thanks everyone for your input!

You provide no information about your database, but if you are using Oracle 10g release 2 or later, you can make your queries case-insensitive by using two Oracle session settings (both are necessary). These settings will allow a simple CONTAINS/EQ comparison to be translated and passed to Oracle, thereby avoiding the performance degradation associated with use of WebFOCUS case-modifying functions.

SQL SQLORA
ALTER SESSION SET NLS_COMP=LINGUISTIC ;
END
-*
SQL SQLORA
ALTER SESSION SET NLS_SORT=BINARY_CI ;
END
-*
TABLEF FILE BV_EMPLOYEE
 PRINT BV_EMPLOYEE.PERSONAL_INFORMATION.FIRSTNAME
 HEADING
  ""
 FOOTING
  ""
 WHERE BV_EMPLOYEE.PERSONAL_INFORMATION.FIRSTNAME EQ 'JudY';
 ON TABLE SET PAGE-NUM OFF 
 ON TABLE PCHOLD FORMAT HTML
END