Kbase P76543: FIND statement not finding records after applying delta.sql
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/9/2004 |
|
Status: Verified
FACT(s) (Environment):
Progress 9.1D
FACT(s) (Environment):
Oracle
SYMPTOM(s):
FIND statement not finding records after applying delta.sql
Records created before applying delta.sql not being found
Records created after applying delta.sql are found
CHANGE:
delta.sql applied to migrate schema changes from Progress to ORACLE
CAUSE:
An index in the ORACLE database used by the FIND statement was redefined by a delta.sql to be case-insensitive.
ORACLE supports only case-sensitive indexes, but Progress allows you to set the attributes of a field so that it is either case-sensitive or case-insensitive (case insensitive being the default). The DataServer makes this feature compatible across Progress and ORACLE databases.
In order for an index to act like it is case-insensitive, an additional column named U##emp_id is created in the relevant ORACLE table. This column will contain uppercase values.
If an index is changed from case-sensitive to -insensitive by applying a delta.sql, although the script will create a second column for the uppercase values, so that the index will work as case-insensitive, the column won't get populated with any values for data already existing in the database.
FIX:
The new U##emp_id column must be populated with uppercase values using SQL, e.g.
UPDATE table-name
SET U##column-name = UPPER(column-name);