HANA SQL- ROUND Function

light111ROUND( ) – function in HANA SQL SCRIPT


In most of the calculations, rounding is applied to the decimal numbers to round the values to the nearest number.

Syntax- ROUND(<number> [, <position> [, <rounding_mode>]]

<number>–  numeric argument .

<position>–  the amount of places after the decimal point to round <number> . Default value is 0. A positive number rounds to the specified position to the right of the decimal point and negative number rounds to the specified position to the left of the decimal point .

<rounding_mode>
ROUND_HALF_UP
ROUND_HALF_DOWN
ROUND_HALF_EVEN
ROUND_UP
ROUND_DOWN
ROUND_CEILING
ROUND_FLOOR


ROUND_HALF_UPThe value is rounded up to the next round figure. This is the default.

If the value falls precisely halfway between two rounded values, it is rounded up away from zero (as is done in commercial rounding).

ROUND_HALF_DOWNThe value is rounded down to the next round figure. If the value falls precisely halfway between two round values, it is rounded down towards zero.

ROUND_HALF_EVENThe value is rounded to the next round figure. If the value falls precisely halfway between two rounded values, it is rounded to the value whose last decimal place is an even number.

ROUND_UPThe value is always rounded away from zero, to the larger round figure.

ROUND_DOWNThe value is always rounded towards zero, to the smaller round figure.

ROUND_CEILINGThe value is always rounded in a positive direction, to the larger value.

ROUND_FLOORThe value is always rounded in a negative direction, to the smaller value.


Below is an AMDP Procedure with round function applied on decimal number and the result provided in the comment part of each line.


CLASS zcl_amdp_explore DEFINITION PUBLIC FINAL CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS: get_info EXPORTING VALUE(et_spfli) TYPE spfli_tab
RAISING cx_amdp_error.
ENDCLASS.
CLASS zcl_amdp_explore IMPLEMENTATION.
METHOD get_info BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT USING spfli.
Syntax- ROUND(<number> [, <position> [, <rounding_mode>]])

lt_v111 = SELECT ROUND (111.38, 1, ROUND_HALF_UP ) “val” FROM DUMMY; — (111.40)
lt_v112 = SELECT ROUND (111.38, 1, ROUND_HALF_DOWN ) “val” FROM DUMMY; — (111.40)
lt_v113 = SELECT ROUND (111.38, 1, ROUND_HALF_EVEN ) “val” FROM DUMMY; — (111.40)
lt_v114 = SELECT ROUND (111.38, 1, ROUND_UP ) “val” FROM DUMMY; — (111.40)
lt_v115 = SELECT ROUND (111.38, 1, ROUND_DOWN ) “val” FROM DUMMY; — (111.30)
lt_v116 = SELECT ROUND (111.38, 1, ROUND_CEILING ) “val” FROM DUMMY; — (111.40)
lt_v117 = SELECT ROUND (111.38, 1, ROUND_FLOOR ) “val” FROM DUMMY; — (111.30)
lt_v118 = SELECT ROUND (-111.38, 1, ROUND_UP ) “val” FROM DUMMY; — (-111.40)
lt_v119 = SELECT ROUND (-111.38, 1, ROUND_DOWN ) “val” FROM DUMMY; — (-111.30)
lt_v120 = SELECT ROUND (-111.38, 1, ROUND_CEILING ) “val” FROM DUMMY; — (-111.30)
lt_v121 = SELECT ROUND (-111.38, 1, ROUND_FLOOR ) “val” FROM DUMMY; — (-111.40)

 
lt_v221 = SELECT ROUND (111.35, 1, ROUND_HALF_UP ) “val” FROM DUMMY; — (111.40)
lt_v222 = SELECT ROUND (111.35, 1, ROUND_HALF_DOWN ) “val” FROM DUMMY; — (111.30)
lt_v223 = SELECT ROUND (111.35, 1, ROUND_HALF_EVEN ) “val” FROM DUMMY; — (111.40)
lt_v224 = SELECT ROUND (111.35, 1, ROUND_UP ) “val” FROM DUMMY; — (111.40)
lt_v225 = SELECT ROUND (111.35, 1, ROUND_DOWN ) “val” FROM DUMMY; — (111.30)
lt_v226 = SELECT ROUND (111.35, 1, ROUND_CEILING ) “val” FROM DUMMY; — (111.40)
lt_v227 = SELECT ROUND (111.35, 1, ROUND_FLOOR ) “val” FROM DUMMY; — (111.30)
lt_v228 = SELECT ROUND (-111.35, 1, ROUND_UP ) “val” FROM DUMMY; — (-111.40)
lt_v229 = SELECT ROUND (-111.35, 1, ROUND_DOWN ) “val” FROM DUMMY; — (-111.30)
lt_v230 = SELECT ROUND (-111.35, 1, ROUND_CEILING ) “val” FROM DUMMY; — (-111.30)
lt_v231 = SELECT ROUND (-111.35, 1, ROUND_FLOOR ) “val” FROM DUMMY; — (-111.40)

lt_v331 = SELECT ROUND (111.32, 1, ROUND_HALF_UP ) “val” FROM DUMMY; — (111.30)
lt_v332 = SELECT ROUND (111.32, 1, ROUND_HALF_DOWN ) “val” FROM DUMMY; — (111.30)
lt_v333 = SELECT ROUND (111.32, 1, ROUND_HALF_EVEN ) “val” FROM DUMMY; — (111.30)
lt_v334 = SELECT ROUND (111.32, 1, ROUND_UP ) “val” FROM DUMMY; — (111.40)
lt_v335 = SELECT ROUND (111.32, 1, ROUND_DOWN ) “val” FROM DUMMY; — (111.30)
lt_v336 = SELECT ROUND (111.32, 1, ROUND_CEILING ) “val” FROM DUMMY; — (111.40)
lt_v337 = SELECT ROUND (111.32, 1, ROUND_FLOOR ) “val” FROM DUMMY; — (111.30)
lt_v338 = SELECT ROUND (-111.32, 1, ROUND_UP ) “val” FROM DUMMY; — (-111.40)
lt_v339 = SELECT ROUND (-111.32, 1, ROUND_DOWN ) “val” FROM DUMMY; — (-111.30)
lt_v340 = SELECT ROUND (-111.32, 1, ROUND_CEILING ) “val” FROM DUMMY; — (-111.30)
lt_v341 = SELECT ROUND (-111.32, 1, ROUND_FLOOR ) “val” FROM DUMMY; — (-111.40)

lt_v441 = SELECT ROUND (115.38, 1 ) “val” FROM DUMMY; — (115.40)
lt_v442 = SELECT ROUND (115.38, -1 ) “val” FROM DUMMY; — (120.00) 

et_spfli = SELECT * FROM spfli WHERE mandt = session_context( ‘CLIENT’ );

ENDMETHOD.
ENDCLASS.


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s