[jira] [Created] (OPENJPA-2720) Invalid syntax with coalesce and BigDecimal using DB2 for z/OS

["Pierre-Yves Soblet (JIRA)" <ji...@apache.org>]

Pierre-Yves Soblet created OPENJPA-2720:
-------------------------------------------

             Summary: Invalid syntax with coalesce and BigDecimal using DB2 for z/OS
                 Key: OPENJPA-2720
                 URL: https://issues.apache.org/jira/browse/OPENJPA-2720
             Project: OpenJPA
          Issue Type: Bug
          Components: criteria, sql
    Affects Versions: 2.4.2, 2.2.0
            Reporter: Pierre-Yves Soblet


Assuming an entity with a BigDecimal column:

{code:java}
@Entity
@Table(name = "TEST_TABLE")
public class TestTable {

    @Id
    @Basic(optional = false)
    private Integer id;

    private BigDecimal amount;
// ...
{code}

executing the following criteria query against a DB2 for z/OS database:
{code:java}
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();
{code}

produces the following SQL:

{code:sql}
SELECT COALESCE(t0.amount,0) 
    FROM TEST_TABLE t0 optimize for 1 row 
{code}

which generates the following error:

{noformat}
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=,0;??( [ CONCAT || / MICROSECONDS MICROSECOND SECONDS SECOND
{noformat}

which is caused by the missing space between the COALESCE parameters.

A workaround is to use another function around the BigDecimal literal. For instance:

{code:java}
criteriaQuery.select(builder.coalesce(root.get(TestTable_.amount), builder.function("DECIMAL", BigDecimal.class, builder.literal(BigDecimal.ZERO))));
{code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)