0

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
3
  • You are using integer arithmetic. You need to cast at least one of the numbers to numeric or make it a non-integer literal by append .0 , e.g. 2880.0
    – user1822
    Jul 24, 2020 at 17:30
  • @a_horse_with_no_name how to do that, can you help Jul 24, 2020 at 18:30
  • 1
    Use e.g. 28800.0/3600 or 28800::numeric/3600 instead of 28800/3600
    – user1822
    Jul 24, 2020 at 18:48

1 Answer 1

3

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)));

Fiddle

8
  • @Lennart still I am not getting desired output Jul 24, 2020 at 18:29
  • @humbertoherrara, have a look at: dbfiddle.uk/… . Last cell evaluates to 1 which you claim is the expected result Jul 24, 2020 at 18:35
  • @Lennart; right, there are multiple issues. no casts, integer division, unclear requirements, to_char() pattern? Jul 24, 2020 at 18:35
  • @Lennart but I am getting data without .0, but there I can see that .0 is added Jul 24, 2020 at 18:42
  • @ErwinBrandstetter is there any other changes can I do? can you suggest please Jul 24, 2020 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.