-
Oracle decimal point retention problem
-
2013-03-03 11:45:49 I'd like to say two shm2008520
- collections I want to contribute
Oracle decimal point retention problem If you need to use the Oracle decimal point reservation problem in your application, the method is very simple and we can do it with round (_data,2) ), but if it is not neatly formatted, You can use the round, the following is the network search to the processing method: www.2cto.com method One: The use of TO_CHAR FM format, namely: 1.to_char (Round ( data.amount,2), ' FM9999999999999999.00 ') as amount the disadvantage is that if the value is 0, it will be displayed as. 00 instead of 0.00. Another note is that the number of decimal points on the left side of the format is more than 9, otherwise the number of queries will be displayed as n symbols "#". solutions are as follows: www.2cto.com 1.select decode (salary,0, ' 0.00 ', (To_char (round), ' fm99999999999999.00 ')) from can_do; method Two: Use case and then else end to handle various cases of judgment: 1.case 2. When InStr (To_char (Data.amount), '. ') <1 Then 3.data.amount | | '. xx ' 4.when InStr (To_char (Data.amount), '. ') + 1 = length (data.amount) then 5.data.amount | | ' 0 ' 6.else 7.to_char (round (Data.amount, 2)) 8.end as amount_format Method Three: You can use the Oracle's own parameter settings, that is, 1.column amount format l9999999999.99 This method is insufficient, the number of decimal points to the left of format 9 is known Otherwise, more than the number appears as ####### #的情况. Another Oracle decimal point retention problem is that when you use column, the setting takes effect at the session or system level. Maybe the value column of a table does not always require all places to be displayed, it is the format of the two digits after the decimal point, this time only uses the session level, but there is a database connection session time-out of the Oracle decimal point retention problem, This method is not recommended if it is not used at the system level. method Four: Use the To_char+trim method as follows: 1.select trim (to_char (1234, ' 99999999999999.99 ')) from dual ; or www.2cto.com 1.select LTrim (Trim (To_char (1234.525, ' 00000000000000.00 ')), ' 0 ') From dual; using 14 9 or 14 0 formats, it is recommended to use 14 9 in a convenient way. The disadvantage of method four is that if the value is 0, after the conversion is. 00 instead of 0.00, the remedy is, decode. Another note is that the number of decimal points on the left 9 or 0 of the format is enough, and the numbers that are responsible for querying are displayed as n symbols "#". as follows: 1.select decode (salary,0, ' 0.00 ', Trim (to_char (Salary, ' 99999999999999.99 '))) from Can_do ; or 1.select decode (salary,0, ' 0.00 ', LTrim (Trim (to_char (Salary, ' 00000000000000.00 ')), ' 0 ') From can_do; Conclusion: it is recommended to use method four in the way of Trim+to_char or method one after the remedy of the way, and preferably using the decimal point to the left N 9 way, do not use 0 of the way, otherwise, to be more trim processing. i.e.: 1.select decode (salary,0, ' 0.00 ', Trim (to_char (Salary, ' 99999999999999.99 ')) from can_do; or 1.select decode (salary,0, ' 0.00 ', To_char (round (salary, (2), ' fm99999999999999.00 ')) from can_do; above is an introduction to the Oracle decimal point retention issue, and you can expect something to be gained.
Oracle retains decimals, such as 0.00
