Calculations in Schema

[Adetweiler]

Two questions - 

1. Round - according to documentation and per our testing round only works to the nearest whole integer. Anyone find a way to round to two decimals (like you can in Oracle SQL developer or excel?)

Ex. 2.5621 in incorta will round to 3 but we want to round to 2.56 as part of an additional calculation

2. Standard Deviation - in oracle it is Std function and  stdev.p function

	AVG	STDEV.P
input	10	10
input	25	25
input	40	40
calc	25	12.24745

[ZachBreimayer]

Hi Adetweiler,

Assuming you want to do this in Analyzer or the Business View you can simply multiply the numeric field by 100 to move the decimals then apply round to the 3rd point of precision determines the rounding up or down, then simply divide by 100 to reset to your 2 decimals as follows: 

ROUND(

    100 * YourField

) / 100

There is also the option to derive this in the physical schema which would allow you to use PL-SQL to derive this with the syntax you currently use in the source table or materialized view.

Regards,

Zach

[Adetweiler · Accepted solution]

Thanks will try! Sorry for the delay i didnt get an email/will check email boxes. This answer only applies to the first scenario, any thought on the second?

[ZachBreimayer]

Hi Adetweiler,

For the Standard Deviation you may want to simply edit the SQL and run that function directly on the table in the physical schema itself, alternatively you could create and MV and use STD function and curate the MV to support your use case.

Regards,

Zach