All we need is an easy explanation of the problem, so here it is.
Hi I stuck in strange issue with one of my query.
Formula : (28800/3600-(28382.6/3600-(1920/3600+1860/3600))) =
So, Input Equation:
= (28800/3600-(28382.6/3600-(1920/3600+1860/3600)))
= (28800/3600-(28382.6/3600-(0.53333333333333+1860/3600)))
= (28800/3600-(28382.6/3600-(0.53333333333333+0.51666666666667)))
= (28800/3600-(28382.6/3600-(1.05)))
= (28800/3600-(28382.6/3600-1.05))
= (28800/3600-(7.8840555555556-1.05))
= (28800/3600-(6.8340555555556))
= (28800/3600-6.8340555555556)
= (8-6.8340555555556)
= (1.1659444444444)
= 1.1659444444444
But I am getting 0 as output from my query, it supposed to print 1
select (to_char (NULLIF(GREATEST(coalesce(28800/3600,0) - (coalesce(28382.6/3600,0) -
(coalesce(1920/3600,0) + (coalesce(1860/3600,0)))),0),0),'FM99,999,999,999'))::character varying as mydatas
Now, same formula and same query with different values working fine. please check following.
(28800/3600-(13552.24/3600-(900/3600+0/3600)))
Input Equation:
= (28800/3600-(13552.24/3600-(900/3600+0/3600)))
= (28800/3600-(13552.24/3600-(0.25+0/3600)))
= (28800/3600-(13552.24/3600-(0.25+0)))
= (28800/3600-(13552.24/3600-(0.25)))
= (28800/3600-(13552.24/3600-0.25))
= (28800/3600-(3.7645111111111-0.25))
= (28800/3600-(3.5145111111111))
= (28800/3600-3.5145111111111)
= (8-3.5145111111111)
= (4.4854888888889)
= 4.4854888888889
so my query also gives 4 as output
select (to_char (NULLIF(GREATEST(coalesce(28800/3600,0) - (coalesce(13552.24/3600,0) -
(coalesce(900/3600,0) + (coalesce(0/3600,0)))),0),0),'FM99,999,999'))::character varying as mydatas
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
The expression:
select ((NULLIF(GREATEST(coalesce(28800/3600,0) - (coalesce(28382.6/3600,0) -
(coalesce(1920/3600,0) + (coalesce(1860/3600,0)))),0),0)))::character varying as mydatas
evaluates to 0.11594…
You are getting it wrong at this step:
= select (28800/3600-(28382.6/3600-(1920/3600+1860/3600)));
= select (28800/3600-(28382.6/3600-(0.53333333333333+0.51666666666667))); -- False
Because 1920/3600 = 0 and so is 1860/3600 due to integer division. You can fix your expression by making sure that one of the operands is not an integer type, example:
= select (28800.0/3600-(28382.6/3600-(1920.0/3600+1860.0/3600)));
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0