Lokalisierungsproblem? bei TO_CHAR-Fkt.

[jobo]

Zitat von Jumpy:

SQL-Code:

Select
TRIM(TO_CHAR(BETRAG_EU,999999999999990.99)) AS WERT
From
TABELLE

Ich hab was wichtiges übersehen, die Funktion oben verwendet eine falsche Notation. Die Format Maske gehört in Anführungsstriche. Ist das im Echtsystem auch so?

Was da in diesem Fall genau geschieht, kann ich nicht richtig nachvollziehen, aber auch hier wird offenbar implizit (falsch) konvertiert und zwar die Formatmaske selbst von Zahl zu Text.

Hier mal ein paar Beispiele:

Code:

Connected to Oracle Database 10g Release 10.2.0.2.0 
Connected as jo@db
 
SQL> 
SQL> -- my nls
SQL> select * from nls_session_parameters;
 
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   GERMAN
NLS_TERRITORY                  GERMANY
NLS_CURRENCY                   ¿
NLS_ISO_CURRENCY               GERMANY
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                dd.mm.yyyy
NLS_DATE_LANGUAGE              GERMAN
NLS_SORT                       GERMAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ¿
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
 
17 rows selected
SQL> -- default NLS decimal separators
SQL> alter session set NLS_NUMERIC_CHARACTERS =',.'; -- default settings
 
Session altered
SQL> Select 88.1234 as MyNumber, 999999999999990.99 as MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, 999999999999990.99)) as MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 0,88                      999999999999990,99
SQL> alter session set NLS_NUMERIC_CHARACTERS ='.,';
 
Session altered
SQL> Select 88.1234 as MyNumber, 999999999999990.99 as MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, 999999999999990.99)) as MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 88.12                     999999999999990.99
SQL> alter session set NLS_NUMERIC_CHARACTERS =',.'; -- Default
 
Session altered
SQL> -- do the same with corrected format mask
SQL> Select 88.1234 as MyNumber, 999999999999990.99 as MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, '999999999999990.99')) as MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 88.12                     999999999999990,99
SQL> alter session set NLS_NUMERIC_CHARACTERS ='.,';
 
Session altered
SQL> -- do the same with corrected format mask
SQL> Select 88.1234 as MyNumber, 999999999999990.99 MyMasknumber,
  2         TRIM(TO_CHAR(88.1234, '999999999999990.99')) MyExplizitConvertedNumber,
  3         to_char(999999999999990.99) as MyMaskNumberImplizitConverted
  4    From dual;
 
  MYNUMBER MYMASKNUMBER MYEXPLIZITCONVERTEDNUMBER MYMASKNUMBERIMPLIZITCONVERTED
---------- ------------ ------------------------- -----------------------------
   88,1234 999999999999 88.12                     999999999999990.99
 
SQL>