Date arithemtic, add_years, add_months, add_weeks, add_days and DB2

[Alex Kolesnik]

I have the following complexish query where I use a case expression with date arithmetic. The case expression chooses to add years or months or days to a date field depending on the value of an interval resolution field. The resulting date is checked against a fixed date.

I construct the case expression with the CasesBuilder like this:

 public DateTimeExpression<Date> addiereZeitraum(DateTimeExpression<Date> dateExpression, QZeitraum zeitraum) {

        DateTimeExpression<Date> cases = Expressions.cases()
                .when(zeitraum.zeiteinheit.eq(Zeiteinheit.JAHRE))
                .then(addDateAmount(dateExpression, Ops.DateTimeOps.ADD_YEARS, zeitraum.anzahl))
                .when(zeitraum.zeiteinheit.eq(Zeiteinheit.MONATE))
                .then(addDateAmount(dateExpression, Ops.DateTimeOps.ADD_MONTHS, zeitraum.anzahl))
                .when(zeitraum.zeiteinheit.eq(Zeiteinheit.WOCHEN))
                .then(addDateAmount(dateExpression, Ops.DateTimeOps.ADD_WEEKS, zeitraum.anzahl))
                .when(zeitraum.zeiteinheit.eq(Zeiteinheit.TAGE))
                .then(addDateAmount(dateExpression, Ops.DateTimeOps.ADD_DAYS, zeitraum.anzahl))
                .otherwise(dateExpression);

        return cases;

    }

This will get translated o a query similar to this...

select o
from ...
where o.id in (select o.id
from o
where (case when (o.resolution= ?4) then add_years(o.someDate,o.amount) when (o.resolution= ?5) then add_months(o.someDate,o.amount) else ...) > ?8

and this query seems to be valid.

But when the query gets translated to sql for DB2, for instance the add_years function won't get translated properly. It is found in the sql as it is.

  ...case when ...=? then add_years(someDateField , resolutionField)

A possible translation for DB2 would be

someDateField + resolutionField YEARS

Unfortunatly I don't understand the mechanism of translation and why it fails here.

Can this translation be configured somehow?

Thanks a lot for an answer, for ideas and for QueryDsl in general of course.

(I use QueryDsl 3.7.4, with JPA2 on JBoss EAP6.4 with Hibernate 4.x.)

[Alex Kolesnik]

Supplement on how the DateTimeExpression is constructed...

    private DateTimeExpression<Date> addDateAmount(DateTimeExpression<Date> date, Operator<Comparable> operator,
            NumberExpression<Integer> amount) {

        return Expressions.dateTimeOperation(Date.class, operator, date, amount);
    }

[Alex Kolesnik]

I understand now. This has nothing to do with QueryDsl any more. QueryDsl constructs jpql which may contain function calls and the JPA provider is responsible for translating such function calls.

Hibernate uses dialects, here as DB2Dialect.

I ve subclassed this dialect an registered translations to DB2 sql for the jpql function calls.

public class BimDB2Dialect extends DB2Dialect {

    public BimDB2Dialect() {
        super();

        registerFunction("add_years", new SQLFunctionTemplate(StandardBasicTypes.DATE, "?1 + ?2 years"));
        registerFunction("add_months", new SQLFunctionTemplate(StandardBasicTypes.DATE, "?1 + ?2 months"));
        registerFunction("add_weeks", new SQLFunctionTemplate(StandardBasicTypes.DATE, "?1 + 7 * ?2 days"));
        registerFunction("add_days", new SQLFunctionTemplate(StandardBasicTypes.DATE, "?1 + ?2 days"));

    }

}

[Alex Kolesnik]

Forgot parens:

   registerFunction("add_weeks", new SQLFunctionTemplate(StandardBasicTypes.DATE, "?1 + (7 * ?2) days"));

[timowest]

Good you figured it out. The layers are indeed Querydsl -> JPA/Hibernate -> SQL