I'm trying to perform a following calculation in a query:

t0.value1::decimal * 10 ^ (-1 * t2.value2::decimal) * t0.value3::decimal as total_value

However since (-1 * t2.value2::decimal) returns -18 all the results are rounding up to 0. I tried explicitly stating decimal scale and precision//changing it to numeric but got the same output. It works just fine as long as (-1 * t2.value2::decimal) is more than -10 and less than 10 (if the number of decimals is less than 10).

How can it be solved?

UPD: here is the data sample

CodePudding user response：

10 is interpreted as a decimal with scale 20. Give it higher precision and scale:

t0.value1::decimal * 10::decimal(40,20) ^ (-1 * t2.value2::decimal) * t0.value3::decimal as total_value

CodePudding user response：

x^(-y) = 1/x^y and that works.

select value1::decimal * (1/10 ^(value2::decimal)) * value3::decimal as total_value
from   t

Fiddle