Description
Assuming an entity with a BigDecimal column:
@Entity @Table(name = "TEST_TABLE") public class TestTable { @Id @Basic(optional = false) private Integer id; private BigDecimal amount; // ...
executing the following criteria query against a DB2 for z/OS database:
CriteriaQuery<BigDecimal> criteriaQuery = builder.createQuery(BigDecimal.class); Root<TestTable> root = criteriaQuery.from(TestTable.class); criteriaQuery.select(builder.coalesce(root.get(TestTable_.amount), BigDecimal.ZERO)); entityManager.createQuery(criteriaQuery).getSingleResult();
produces the following SQL:
SELECT COALESCE(t0.amount,0) FROM TEST_TABLE t0 optimize for 1 row
which generates the following error:
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=,0;??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS SECOND
which is caused by the missing space between the COALESCE parameters.
A workaround is to use another function around the BigDecimal literal. For instance:
criteriaQuery.select(builder.coalesce(root.get(TestTable_.amount), builder.function("DECIMAL", BigDecimal.class, builder.literal(BigDecimal.ZERO))));