DSL.timestampAdd function with Teradata

[Samuel Nelson]

Hi,

I've run into some issues using the DSL.timestampAdd function with Teradata.

Here is my code, a simple select statement.

DSLContext create = using(SQLDialect.TERADATA);

Select select = create.select(DSL.timestampAdd(SHIP_DATE, 480, DatePart.MINUTE)).from(SALES_VIEW);

This is the sql that is generated: 

select ("sales_view"."Ship_Date" + cast('0 00:' || lpad(cast(480 as varchar(2)), 2, '0') || ':00' as interval day to second)) from "sales_view"

Running this generated sql in Teradata, produces the following error: 

"[Error 3798] [SQLState 42000] A column or character expression is larger than the max size."

I, admittedly, don't have much experience with Teradata. I'm trying to use JOOQ to translate my queries. Am I doing something wrong? Is it a problem with how I have my database configured?

It seems like JOOQ should output sql more like the following, which works:

select ("sales_view"."Ship_Date" + INTERVAL '10' MINUTE) from "sales_view"

Thank you,

Sam Nelson

[Lukas Eder]

Hi Samuel,

Thank you very much for your report.

The main reason why we produce such an expression is because the second argument of timestampAdd() could be an arbitrary expression, not a constant (or bind value), in case of which the INTERVAL ? MINUTE syntax wouldn't work. But we should definitely produce this syntax in your case. I've created an issue to fix this:

https://github.com/jOOQ/jOOQ/issues/8756

On my version of Teradata, I could not reproduce the max size error. However, there's an additional problem with the existing approach. It truncates the minute value to varchar(2), because in the interval expression, we cannot have more than 59 minutes. So, the existing solution also doesn't work in your case, even if you didn't run into the max size problem.

We'll investigate this via #8756

Thanks again for your report,

Lukas

[Lukas Eder]

Following up, a better way to deal with this is to use cast(? as interval minute(4)). Can you confirm this syntax would work on your version of Teradata?

It seems that with both syntaxes, 4 is the highest possible minute precision.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/8427d895-c725-4e95-9d49-46330ecb7e3b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Samuel Nelson]

That syntax does work with my version of Teradata. I'm using Teradata Expression version 15.10. Thank you.

On Friday, June 7, 2019 at 6:13:14 AM UTC-6, Lukas Eder wrote:

Following up, a better way to deal with this is to use cast(? as interval minute(4)). Can you confirm this syntax would work on your version of Teradata?

It seems that with both syntaxes, 4 is the highest possible minute precision.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Perfect, thanks for the feedback, Samuel. I'm hoping to release 3.11.12 with a fix early next week. If you find any additional issues, please let me know, and I might be able to include them as well in 3.11.12.

Best Regards,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/bc4634e1-ebb8-4b67-9eff-e36e3c25dc8f%40googlegroups.com.