Tuesday, March 20, 2012

script problem with POWER(x,y)

hi,

i can't see why the following field is not working correctly, ie: it's always coming up with 0:

((POWER((1+((((((POWER((1+(dbo_table1.APR/100)),(1/12)))-1))*12))/12)), dbo_table1.FREQUENCY)-1)*(12/dbo_table1.FREQUENCY))*100 AS YIELD

breaking the code down to a smaller formula it does work.

it looks like i can't have more calculations/references inside a POWER(x,y) function.

any comments would be appreciated.

thank you

The problem is integer math. This formula can never return anything other than zero because (1/12) = 0. Guessing at datatypes:

declare @.apr decimal (4,2)
declare @.frequency decimal(3,0)

set @.apr = 6.5
set @.frequency = 24

select ((POWER((1+((((((POWER((1+(@.apr/100)),(1.0/12.0)))-1))*12))/12)), @.frequency)-1)*(12/@.frequency))*100

This now returns:

6.711700

Hope this helps.

|||

thank you very much.

it has solved the issue

regards

No comments:

Post a Comment