Kbase 21004: Comparison on Non-sensitive Field Uses UPPER() in Oracle
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/19/2007 |
|
Status: Verified
FACT(s) (Environment):
Oracle DataServer
SYMPTOM(s):
Statements that have a WHERE clause including a non-sensitive field have the function UPPER added when translated by Oracle.
The following SQL code is translated by Oracle
CODE:
SELECT COUNT(*) FROM customer WHERE name <= CHR(160)
ORACLE Translation CODE:
SELECT COUNT(*) FROM customer WHERE u##name <= UPPER(CHR(160))
CAUSE:
If one side of the expression is case insensitive, the other side gets the UPPER function. The case-insensitive column causes a case insensitive comparison to be done. That is why there is the UPPER function.
Consider the STATE table, with its case insensitive column STATE, which has a shadow column U##STATE which has the upper case version of STATE.
FIND state WHERE state = "NH"
is CORRECTLY done as:
WHERE U##STATE = upper (:1)
FIX:
To avoid using the UPPER function, redefine the column in the ORIGINAL Progress database to be case sensitive, and rerun protoora to create the table and index without the shadow column. Then the comparisons will be case sensitive.