cancel
Showing results for 
Search instead for 
Did you mean: 

Calculations in Schema

Adetweiler
Cosmonaut

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

 AVGSTDEV.P
input1010
input2525
input4040
calc2512.24745
3 REPLIES 3

ZachBreimayer
Employee
Employee

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
Cosmonaut

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?

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