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
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/6334a1e1-7a20-4836-b209-09de76b59417n%40googlegroups.com.
