variable decimal places in sql

tags:

views:

47

answers:

2

Q:

variable decimal places in sql

How do you make it so that all calculations in the DB compute to a pre-specified # of decimal places? Say I have three tables with the following fields

Table1

A int
B decimal(18, 3)

Table2

A int
B decimal (18, 2)
C decimal (18, 3)

Table3

A int
Precision int

Now I need to change it so that all my calculations are based on what precision is set for A in Table3. I started by converting all my decimals to decimal (30, 10) to allow for higher precisions if specified.

+3 A:

Wrap your results in a CAST statement to set them to the desired precision. I.e.:

SELECT CAST((<query>) AS int) AS Result

JNK 2010-10-20 15:40:40

thanks, this is what I was thinking of...my result needs to be a decimal, so it should be SELECT CAST((<query>) AS decimal(18, desired-precision)) AS Result, right?

Prabhu 2010-10-20 15:43:37

Exactly. I put `int` since you said in your example `A`, all of which are `int` in your sample data.

JNK 2010-10-20 15:45:23

so it would be fine to put a variable for desired-precision? Something like decimal(18, @precision)?

Prabhu 2010-10-20 16:08:46

Just clarifying, what you meant is that I should store everything as is in the table, and just before reporting results to the UI, round it based on the specified precision?

Prabhu 2010-10-20 16:16:46

@Prabhu - Correct. This is probably the simplest way to get what you want.

JNK 2010-10-20 17:48:14

+1 A:

Try using the ROUND function in a stored procedure that first retrieves the precision from your Table3 table.

Bernard 2010-10-20 15:41:56

So you're saying store everything as is in the table, and just before reporting to the UI, round it off based on the specified precision?

Prabhu 2010-10-20 16:10:24

I'm saying create a stored procedure that retrieves the desired precision first and uses it in the ROUND function to round it off to that precision. All of this contained in a stored procedure that you create. You could use the CAST (instead of ROUND) function as has already been suggested.

Bernard 2010-10-20 17:10:55

ansaurus

tags:

views:

answers:

variable decimal places in sql

Table1

Table2

Table3

related questions