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 |
Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder