Skip to Content
Technical Articles
Author's profile photo Kapil Kumar

Round Up & Round Down Any Fraction Number

Requirement

Sometime we gets requirement to Round Up\Down any Fraction number to the nearest integer number in HANA View or HANA Sql.

Expected Round Up Result

Input Fraction Number  Expected Result
1.1 2
1.298 2
1.8978 2

Expected Round Down Result

Input Fraction Number  Expected Result
1.1 1
1.298 1
1.8978 1

Solution

To get the desired output, we can use below mentioned in-built HANA functions.

  1. CEIL      – Round Up
  2. FLOOR – Round Down
-- Sample Result Set

SELECT CEIL (1.1) FROM DUMMY;
-- Result 2
SELECT CEIL (1.298) FROM DUMMY;
-- Result 2
SELECT CEIL (1.8978) FROM DUMMY;
-- Result 2

SELECT FLOOR (1.1) FROM DUMMY;
-- Result 1
SELECT FLOOR (1.298) FROM DUMMY;
-- Result 1
SELECT FLOOR (1.8978) FROM DUMMY;
-- Result 1

Conclusion

These two functions can help in this special requirement to make a fraction number equivalent to it’s nearest integer number. By using these function we can avoid creating a custom calculation to get the complete integer number for a fraction number.

Reference

CEIL Function –

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/20db6dd575191014b704978452f92bbd.html

FLOOR Function –

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/20e1c2b47519101484d8af631b6845f9.html

Hope this will be helpful..!

Please do let me know if there is any other workaround available.

Thanks.!

Assigned tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Oleksandr Master
      Oleksandr Master

      Thanks Kapil.

      Do you know how to make a normal round, like

      1.1 1
      1.298 1
      1.8978 2

      Best regards,
      Oleksandr

      Author's profile photo Kapil Kumar
      Kapil Kumar
      Blog Post Author

      Hi Oleksandr,

      You can use ROUND function as used in below Queries.

      Hope this Help.

      SELECT ROUND (1.1) AS "ROUNDED_VALUE", 'INPUT NUMBER - 1.1' AS "INPUT_NUMBER" FROM DUMMY
      UNION
      SELECT ROUND (1.298) AS "ROUNDED_VALUE", 'INPUT NUMBER - 1.298' AS "INPUT_NUMBER" FROM DUMMY
      UNION
      SELECT ROUND (1.8978) AS "ROUNDED_VALUE", 'INPUT NUMBER - 1.8978' AS "INPUT_NUMBER" FROM DUMMY;

       

      Regards,

      Kapil Kumar

      Author's profile photo Oleksandr Master
      Oleksandr Master

      Thanks Kapil.

      I meant BEx queries. Sorry for my confusion.

      Best regards,

      Oleksandr

      Author's profile photo Kapil Kumar
      Kapil Kumar
      Blog Post Author

      Hi Oleksandr,

      Sorry, I don't have much expertise in BW. So won't be able to help much regarding BEx queries.

      Regards,

      Kapil Kumar