Kbase 20777: ORACLE Dsrv: Query with SUBSTR Gives Different Results
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
FACT(s) (Environment):
Progress 8.x
Oracle DataServer
SYMPTOM(s):
Different result set is seen with a query that uses the SUBSTRING function in Progress versions prior to 8.x versus 8.x and higher
CAUSE:
Prior to Progress 8.x, the DataServer does not send the SUBSTRING function to ORACLE. Therefore, the function is resolved on the client side. The DataServer starts to send the SUBSTRING function on the SQL statement with
In Progress 8.x and after, the DataServer sends the SUBSTRING function on the SQL statement. Therefore, the result set is the one that ORACLE returns to the DataServer. This can cause different result sets when you compare with blank positions.
FIX:
If the 4th parameter of the SUBSTRING function is not a constant, the client resolves the function. Use the 4th parameter as an expression and the DataServer will behave as Progress versions prior to 8.x, thus not sending the SUBSTRING function to Oracle.
For example:
The following query is resolved by ORACLE on Progress Version 8.x
and later:
FOR EACH customer where SUBSTR(customer.name,4,1) <> 'M'.
This is an example to force the client to resolve the query:
DEFINE VARIABLE varx AS CHAR INITIAL "CHARACTER".
FOR EACH customer where SUBSTR(customer.name,4,1, varx) <> 'M'.