JOOQ : trunc is adding `DD` to the query - which is causing query to fail

user_3133727 0
Viewed 31

Doing some operation on a Date Field

Field field = (DSL.month(dateField).add(12 - startMonthOfYear).div(3));

Then truncating the field to get the integer value

Field quarterField = DSL.trunc(field2);

The query it generates is like

select trunc(((extract(month from my_date_field) + ?) / ?), 'DD') "date" from my_table

trunc seems to add this 'DD'

Now this 'DD' is causing problem while running the query 'ORA-01722: invalid number' , if I remove the 'DD' .. the query runs completely fine.

How do I remove this 'DD'?

Oracle 12c is the SQL dialect

jooq
1 Answers

In jOOQ 3.16, there's currently no TRUNC(numeric) function support in jOOQ, only TRUNC(date) as you can see in the DSL.trunc(Field<T>) Javadoc:

Truncate a date or a timestamp to the beginning of the day.

That's certainly an API design mistake, which should be fixed: https://github.com/jOOQ/jOOQ/issues/12865

In the meantime, use DSL.trunc(Field<T>, int) instead.

Field<?> quarterField = DSL.trunc(field, 0);

Rawtypes

A side note on your rawtype usage. It's highly recommended to avoid doing that to avoid edge cases of compilation errors or wrong overload resolution: https://github.com/jOOQ/jOOQ/issues/11879

user_5217990
Related