Kbase P111634: Convert a character expression to a decimal numeric value in SQL-92 fails.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
SYMPTOM(s):
Convert a character expression to a decimal numeric value in SQL-92 fails.
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-20232
[JDBC Progress Driver]:Invalid number string (7498)
CAUSE:
The TO_NUMBER() function actually do convert using decimals, but the string contained e.g. '123456,45' that failed when using the function. The problem is that the TO_NUMBER() needs to read the '.' in the character string as a comma separator and not a ',' which is treats as a character, and does not manage to convert to a separator.
FIX:
1) If the character fields you are converting are using ',' as the separator you need to use the REPLACE function:
TO_NUMBER( REPLACE(character_string, ',', '.') )
FIX:
2) Another way to solve it is to use VIEWs to have the separator as '.'.