You can translate the question and the replies:

Need 3 digits after decimal

Hi Team, I want to know is there any function in denodo just like to_number in oracle ,which can give me 3 digits after decimal. For example :Suppose in a column(let suppose amount) there are values like. 1.01 1.256 .355 1.1 Expected output 1.010 1.256 .355 1.100
user
02-12-2019 04:19:36 -0500

1 Answer

Hi, There are a few ways that you can carry out this requirement. You can use the [“Create Base View from Query”](https://community.denodo.com/kb/view/document/Using%20the%20Create%20Base%20View%20From%20Query%20Option?category=Data+Sources) option to make use of number formatting functions. These functions will be delegated entirely to the underlying data source. Also, it seems that the function you are looking for is TO_CHAR instead of TO_NUMBER since TO_NUMBER will not be able to format decimals according to your requirements. You can supply the below query when creating the base view: `SELECT TO_CHAR(<column_name>, '9.999') FROM <table_name>` Note: This column will be of data type TEXT. You can use the [CAST](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast) function later on if you need to perform any arithmetic. Otherwise, you can create a JAVA-based custom function that is delegated to a database which implements this functionality using the Denodo4E extension available in the directory "<DENODOHOME>/tools/denodo4e" and then deploy the custom function in Virtual DataPort. For more information on creating custom functions, you can have a look at the [Developing Custom Functions that Can Be Delegated to a Database](https://community.denodo.com/docs/html/browse/7.0/vdp/developer/developing_extensions/developing_custom_functions/creating_custom_functions_with_annotations#developing-custom-functions-that-can-be-delegated-to-a-database) section of Virtual DataPort Developer Guide. You can find samples of custom functions in the directory "<DENODOHOME>\samples\vdp\customFunctions." Hope this helps!
Denodo Team
02-12-2019 21:19:11 -0500
You must sign in to add an answer. If you do not have an account, you can register here