Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Simulation of TRUNC() in Derby

DZone's Guide to

Simulation of TRUNC() in Derby

· Java Zone
Free Resource

Learn how to troubleshoot and diagnose some of the most common performance issues in Java today. Brought to you in partnership with AppDynamics.

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

 

 

 

 

Understand the needs and benefits around implementing the right monitoring solution for a growing containerized market. Brought to you in partnership with AppDynamics.

Topics:

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}