Wrong output getting from query

All we need is an easy explanation of the problem, so here it is.

Hi I have written this query as per my project requirement. But it is generating wrong output.

select (to_char((33)/nullif((17400/3600),0),'FM99,999,999'))::character varying as userdatas

currently it is generating 8 as output. But according to calculation it is different,

= 33/(17400/3600)

= 33/(4.8333333333333)

= 33/4.8333333333333

= 6.8275862068966

It should display 7

Can anyone help me with this issue ?

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

All your numbers are integers, so the whole expression is done using integer division. You need to either provide numeric constant, e.g. using 3600.0 or cast at least one number to numeric

select 33/(17400/3600::numeric);

returns 6.8276, when applying to_char() it’s correctly rounded to 7:

select to_char(33/(17400/3600::numeric),'FM99,999,999')

Online example

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

Leave a Reply