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
On Thu, Jul 20, 2023 at 1:34 PM chri schneid <chrischn...@gmail.com> wrote:
> 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 jooq-user+unsubscr...@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
> <https://groups.google.com/d/msgid/jooq-user/6334a1e1-7a20-4836-b209-09de76b59417n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO5Wn_LgPWK8z1BfW8ehiiw4vh_erosQug4rwSjSJhMJ5A%40mail.gmail.com.
