Derby is missing out a lot of functions from the set of functions that other databases usually provide. One example is the `TRUNC(value, decimals)`

function. According to the Wikipedia, truncation can be achieved as such:

-- trunc(x, n) CASE WHEN x > 0 THEN floor(power(10, n) * x) / power(10, n) ELSE ceil(power(10, n) * x) / power(10, n) END

Unfortunately, there is no `POWER(base, exponent)`

function in Derby either. But no problem, we can simulate that as well. Let’s consider the Wikipedia again and we’ll find:

power(b, x) = exp(x * ln(b))

If we substitute that into the original simulation, we get for Derby:

-- trunc(x, n) CASE WHEN x > 0 THEN floor(exp(n * ln(10)) * x) / exp(n * ln(10)) ELSE ceil(exp(n * ln(10)) * x) / exp(n * ln(10)) END

Verbose, probably quite inefficient, but effective! Let’s run a short test, too:

create table test (x numeric(10, 5), n int); insert into test values (11.111, 0); insert into test values (11.111, 1); insert into test values (11.111, 2); insert into test values (11.111, -1); select x, n, case when x >= 0 then floor(exp(n * ln(10)) * x) / exp(n * ln(10)) else ceil(exp(n * ln(10)) * x) / exp(n * ln(10)) end "trunc(x, n)" from test;

The above yields

X | N | TRUNC(X, N) |
---|---|---|

11.111 | 0 | 11 |

11.111 | 1 | 11.1 |

11.111 | 2 | 11.11 |

11.111 | -1 | 10 |

## One thought on “Simulation of TRUNC() in Derby”