Problems using having clause

[chri schneid]

Hello user group,

I'm new to jooq. I'm facing a problem with the following:

Original SQL running on IBM DB2

select
    MEDIAN(days(MHD) - days(LIEFER_DATUM)) as MEDIAN,
    FLOOR(MEDIAN(days(MHD) - days(LIEFER_DATUM))) as  EDLE_RLZ,
    ARTIKEL_NR,
    LAGER_NR
from schema.table
WHERE LIEFER_DATUM >= CURRENT date - 200 DAYS
AND LAGER_NR = 39
group by Lager_NR, ARTIKEL_NR
HAVING count(CONCAT(VARCHAR_FORMAT(LIEFER_DATUM, 'yyyy-MM-dd'), VARCHAR_FORMAT(MHD, 'yyyy-MM-dd'))) > 7
with ur;

now I'm trying it using jooq 3.17.10:

ResultQuery<Record4<Double, Integer, BigInteger, BigInteger>>recordResultQuery = context
.select(
DSL.median(localDateDiff(table.MHD, table.LIEFER_DATUM).cast(SQLDataType.INTEGER)).cast(SQLDataType.DOUBLE).as("MEDIAN"),
  floor(DSL.median(localDateDiff(table.MHD, table.LIEFER_DATUM).cast(SQLDataType.INTEGER)).cast(SQLDataType.DOUBLE)).cast(SQLDataType.INTEGER).as("EDLE_RLZ"),
table.LAGER_NR,
table.ARTIKEL_NR)
.from(table)
.where(table.LAGER_NR.eq(warehouse))
.and(table.ARTIKEL_NR.eq(BigInteger.valueOf(418)))
.and(table.LIEFER_DATUM.greaterOrEqual(startDate()))
.groupBy(table.LAGER_NR, table.ARTIKEL_NR);

this works. But when I add the having clause it doesn't:

ResultQuery<Record4<Double, Integer, BigInteger, BigInteger>>recordResultQuery = context
.select(
DSL.median(localDateDiff(table.MHD, table.LIEFER_DATUM).cast(SQLDataType.INTEGER)).cast(SQLDataType.DOUBLE).as("MEDIAN"),
  floor(DSL.median(localDateDiff(table.MHD, table.LIEFER_DATUM).cast(SQLDataType.INTEGER)).cast(SQLDataType.DOUBLE)).cast(SQLDataType.INTEGER).as("EDLE_RLZ"),
table.LAGER_NR,
table.ARTIKEL_NR)
.from(table)
.where(table.LAGER_NR.eq(warehouse))
.and(table.ARTIKEL_NR.eq(BigInteger.valueOf(418)))
.and(table.LIEFER_DATUM.greaterOrEqual(startDate()))
.groupBy(table.LAGER_NR, table.ARTIKEL_NR)
.having( count(concat( table.MHD, table.LIEFER_DATUM)).greaterThan(7));

results in
org.springframework.jdbc.BadSqlGrammarException: jOOQ;
bad SQL grammar [select cast(median(cast(cast(("table"."MHD" - "table"."LIEFER_DATUM") as integer) as integer)) as double) "MEDIAN",
cast(floor(cast(median(cast(cast(("table"."MHD" - "table"."LIEFER_DATUM") as integer) as integer)) as double)) as integer) "EDLE_RLZ",
"table"."LAGER_NR",
"table"."ARTIKEL_NR"
from "table"
where ("table"."LAGER_NR" = ? and "table"."ARTIKEL_NR" = ? and "table"."LIEFER_DATUM" >= ?)
group by "table"."LAGER_NR", "table"."ARTIKEL_NR"
having count((cast("table"."MHD" as varchar) || cast("table"."LIEFER_DATUM" as varchar))) > ?]

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.translate(JooqExceptionTranslator.java:94)
at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.handle(JooqExceptionTranslator.java:81)
at org.springframework.boot.autoconfigure.jooq.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:55)
at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:276)

table.MHD and table.LIEFER_DATUM are of type LocalDate in jooq. In the DB2 table these fields are of type DATE.

Thanks for any hints

Christian

[Lukas Eder]

Hi Christian,

I don't see what is conceptually wrong with your query. What particular syntax element is Db2 complaining about? Can you provide a more minimal reproducer?

Best Regards,

Lukas

--
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/6334a1e1-7a20-4836-b209-09de76b59417n%40googlegroups.com.