| Formula Column Datatype Char Show "/" on to_char for format number [message #402549] |
Mon, 11 May 2009 07:52  |
carpediem
Messages: 6
Registered: May 2009
|
Junior Member |
|
|
Hi, i have a custom report for daily book, in some cases for a lot information this report fails, in debug log show an incongruent information about results of formula items. Ie a formula item calcs an get this number 268265934.78 when show the item in the other item formula column show 000000700.00 for the same first item.
When run a to_char(000000700.00,'FML999G999G999G999G999G999G999G999D00') show $/0/,0/0,70/.0/
Please help, thanks a lot.
Regards.
|
|
|
|
|
|
| Re: Formula Column Datatype Char Show "/" on to_char for format number [message #402779 is a reply to message #402704] |
Tue, 12 May 2009 07:50  |
carpediem
Messages: 6
Registered: May 2009
|
Junior Member |
|
|
Hi Littlefoot, thanks for interest
select to_char(000000700.00,'FML999G999G999G999G999G999G999G999D00') from dual
return $700.00
select value from nls_session_parameters
return
AMERICAN
AMERICA
$
AMERICA
.,
GREGORIAN
DD-MON-YY
AMERICAN
BINARY
HH.MI.SSXFF AM
DD-MON-RR HH.MI.SSXFF AM
HH.MI.SSXFF AM TZR
DD-MON-RR HH.MI.SSXFF AM TZR
$
BINARY
BYTE
FALSE
but the concurrent log output show
Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
LATIN AMERICAN SPANISH_AMERICA.WE8ISO8859P1
'.,'
[Updated on: Tue, 12 May 2009 07:51] Report message to a moderator |
|
|
|
|
|
| Re: Formula Column Datatype Char Show "/" on to_char for format number [message #402895 is a reply to message #402780] |
Wed, 13 May 2009 00:30 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
That's crazy ... some numbers are correctly converted, while the others (looking more or less the same) ended up with an error!
From your second image, there was
C_CALC_DR = 191728356.52
converted = $191,728,356.52 --> OK
C_CR_TOT_DAY = 191728600.00
converted = $191,728,60/.0/ --> wrong!
C_CALC_DR = 000000400.00
converted = $/0/,0/0,40/.0/ --> wrong!  I thought that, somehow, group and decimal characters were set in an unusual manner (slash and dot or comma), but I was wrong.
Though, there IS a pattern that leads to an error - too many zeros in a string. See, '000000400.00' or '191728600.00' produced an invalid result, while '191728356.52' seems to be OK.
Now, does having all those leading zeros (000000400.00) mean that "input" values are characters? If so, can you convert them to numbers first and then apply required format, so that you'd have TO_CHAR(400.00, ...) instead of TO_CHAR(000000400, ...)?
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
