How to handle precision of number data type

by Ravi
(India)

Hi,


I have a table with a column VOL which is a number data type NUMBER(7,3). The value in the column gets populated with the code

MAX (NVL (0.493039/1000,
NVL ((82*82*82)/ 1000000000,0)))

Now problem is that whenever a the result of above snippet comes out with decimal places more than 3 that is suppose 0.0004 then the column gets populated with a value of 0. I need to set a default value of 0.1 into those columns which is coming to be 0 after the precision being done. Can you suggest me a way to do so?

Comments for How to handle precision of number data type

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 29, 2016
Rating
starstarstarstarstar
Informative Post NEW
by: Anonymous

This is wonderful blog about managing the number of type data. You have written on a very good topic. I really liked this. I am book Marking your page for the new interesting blog. Keep share you more blog. Good Work. Keep it up. [url="http://packagingboxessuppliers.co.uk/cardboard-boxes-printing.html"]kraft paper packaging boxes[/url]

Nov 27, 2016
Rating
starstarstarstarstar
Excellent blog NEW
by: Anonymous

This is wonderful website handle about the data. I really liked this. I was on your website and reading your blog. You have shared very informative blog for every one. Please keep update from your more blogs. Thanks for sharing this with me. [url="http://packagingboxessuppliers.com/printed-packaging-boxes.html"]Packagingboxessuppliers.com[/url]

Dec 08, 2015
Rating
starstarstarstarstar
Nice Website
by: Anonymous

This is indeed a nice website on oracle. Oracle is a tough subject and I learned a lot of new things from this website.

Nov 27, 2015
Rating
starstarstarstarstar
Nice Website
by: Anonymous

Oracle is a vast and complicated language. Not everyone is able to deal with Oracle with proper care. But this blog provides useful information.

Nov 27, 2015
Rating
starstarstarstarstar
I am very happy
by: clark

I am very happy because you tell us in this post that how to handle precision of number data type. so please share more information about this topic.

Sep 02, 2015
Rating
starstarstarstarstar
Good
by: Robert Nelson

You have told the easy way that how we can handle precision of number data type. very few people the proper use of Oracle that is also web language. Now this whole website is about the Oracle and discuss some useful questions and its answers as well. I'm a student and happy to find this site because i have great interest in web development.

Aug 17, 2015
Rating
starstarstarstarstar
This is fantastic post
by: Anonymous

Thanks for sharing this informative article.

Aug 16, 2015
Rating
starstarstarstarstar
This is great post you have shared with us
by: Anonymous

.

Aug 01, 2015
Rating
starstarstarstarstar
Good effort
by: Robert Nelson

You have told the easy way that how we can handle precision of number data type. very few people the proper use of Oracle that is also web language. Now this whole website is about the Oracle and discuss some useful questions and its answers as well. I'm a student and try to find out and also happy to find this site because i have great interest in web development.

Jul 30, 2015
Rating
starstarstarstarstar
Great informat
by: Anonymous

Useful information you have shared with us. this is really informative for everyone. This is all about handling precision of number data type. thanks for this info.

Jul 29, 2015
Rating
starstarstarstarstar
Awesome
by: Anonymous

Your writing skills are phenomenal and you make full use of them and always deliver the best.

Jul 24, 2015
Rating
starstarstarstarstar
This is great post
by: jordanwer4

I think this is an informative post and it is very useful and knowledgeable. Thanks a lot for sharing such a great post.

Jul 23, 2015
Rating
starstarstarstarstar
I am very happy to read this website
by: clark

I am very happy to read this website. It is very useful for me. I like it very much. Thanks for sharing and keep on sharing.

Jul 22, 2015
Rating
starstarstarstarstar
זה פוסט פנטסטי
by: ירדן ש

זה פוסט נהדר ששיתפת אותנו. אני אגיד לי החברים שלי למעקב הבלוג שלך זה מאוד מועיל. אני רוצה לומר תודה על הפוסט הזה.

Jul 15, 2015
Rating
starstarstarstarstar
This is great post
by: Anonymous

and I guess that they are having fun to read this post, they shall take a good site to make information.

Jul 13, 2015
Rating
starstarstarstarstar
Great Article
by: Harry Alex

Great post I’ll definitely recommend it to my friends. In a nutshell, brilliant analysis! Very helpful.

Jul 09, 2015
Rating
starstarstarstarstar
I visited many sites
by: clark

I visited many sites but your site is so informative then other. I am very happy to see this nice post. thanks for this information.

Jul 09, 2015
Rating
starstarstarstarstar
Wonderful Post
by: Anonymous

It is heart touching blog you know. It is because it contains very unique data of information. I always like these types of blogs and thanks for sharing.

Jul 09, 2015
Rating
starstarstarstarstar
aery
by: Anonymous

I wish to say that this post is amazing, nice written and include approximately all important infos. I would like to see more posts like this.

Jun 13, 2015
Rating
starstarstarstarstar
Good post
by: Bennie Simonis IV

You have a professional niche site, thank you for your awesome work! Thank you!

Apr 24, 2015
Rating
starstarstarstarstar
Rounding is the problem
by: Anonymous

The problem is that your result in this case (0.000493039) is less than 0.0005 so when Oracle rounds the number to 3 decimal places the answer is 0. The solution therefore is to detect whether your input value is less than 0.5 and if so return 0.1 instead of 0.

You could use a CASE statement in SQL to do this which would give you something like the following:

MAX (CASE WHEN

NVL (0.493039/1000,

NVL ((82*82*82/ 1000000000),0))) < 0.0005 THEN 0.1

ELSE (NVL (0.493039/1000,

NVL ((82*82*82)/ 1000000000,0)))

END

You might want to check the input vale before you divide by 1000 but obviously you would still need the checks for NULL. It might not make much difference in performance either way, depending on how well the code can be optimized.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.