Yeah, I don't know why you're passing the boolean value to SQL when you could just write a dynamic SQL query instead: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/
write: .set(COLUMN, condition ? ifTrue : ifFalse) You'll probably need to explicitly wrap bind values using DSL.val() https://www.jooq.org/doc/latest/manual/sql-building/bind-values/indexed-parameters/ On Tue, Aug 13, 2024 at 7:12 PM Nico van de Kamp <[email protected]> wrote: > Ok it's working now but I'm not satesfied espcially about the set > statments. On this moment I don't know a better way but if somenone has an > advise would be nice. > > > override fun update(agendaItem: AgendaItem, agendaId: AgendaId, zaakId: > ZaakId): Int = usingDSL { context -> > // Define the derived table > val derivedTable = context.select(AGENDA_ITEM.ID, > AGENDA_ITEM.BEGINDATUM, > AGENDA_ITEM_TYPE.HELE_DAG, > AGENDA_ITEM_TYPE.NAAM > ) > .from(AGENDA_ITEM, AGENDA_ITEM_TYPE) > .where(AGENDA_ITEM_TYPE.ID.eq(AGENDA_ITEM.ITEM_TYPE_ID)) > .asTable("derivedTable") > > context.update(AGENDA_ITEM) > .set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum) > > .set(AGENDA_ITEM.EINDDATUM, choose(AGENDA_ITEM_TYPE.HELE_DAG) > .`when`(true, agendaItem.einddatum) > .`when`(false, agendaItem.begindatum)) > > .set(AGENDA_ITEM.BEGINTIJD, choose(agendaItem.itemType.naam.equals("value" > )) > .`when`(true, AGENDA_ITEM.BEGINTIJD) > .`when`(false, agendaItem.begintijd)) > .set(AGENDA_ITEM.EINDTIJD, choose(agendaItem.itemType.naam.equals("value" > )) > .`when`(true, AGENDA_ITEM.EINDTIJD) > .`when`(false, agendaItem.eindtijd)) > > .set(AGENDA_ITEM.AANGEKONDIGD, choose(AGENDA_ITEM.ID.eq(agendaItem.id. > value)) // I'm doubting if this is the best way...? > .`when`(true, agendaItem.aangekondigd) > .`when`(false, AGENDA_ITEM.AANGEKONDIGD)) > > .set(AGENDA_ITEM.FUNCTIONARIS_BEDRIJF_BEPAALD, > choose(AGENDA_ITEM.ID.eq(agendaItem.id.value)) > .`when`(true, agendaItem.functionarisDoorBedrijfBepaald) > .`when`(false, AGENDA_ITEM.FUNCTIONARIS_BEDRIJF_BEPAALD)) > > .set(AGENDA_ITEM.OMSCHRIJVING, choose(AGENDA_ITEM.ID.eq(agendaItem.id. > value)) > .`when`(true, agendaItem.omschrijving) > .`when`(false, AGENDA_ITEM.OMSCHRIJVING)) > > .set(AGENDA_ITEM.TOELICHTING, choose(AGENDA_ITEM.ID.eq(agendaItem.id.value > )) > .`when`(true, agendaItem.toelichting) > .`when`(false, AGENDA_ITEM.TOELICHTING)) > > .set(AGENDA_ITEM.RECORD_VERSION, AGENDA_ITEM.RECORD_VERSION + 1) > .from(AGENDA_ITEM_TYPE, derivedTable) > .where( > AGENDA_ITEM_TYPE.ID.eq(AGENDA_ITEM.ITEM_TYPE_ID) > .and(derivedTable.field(AGENDA_ITEM.ID)?.eq(agendaItem.id.value)) > .and(AGENDA_ITEM.AGENDA_ID.eq(agendaId.value)) > .and(AGENDA_ITEM.BEGINDATUM.eq(derivedTable.field(AGENDA_ITEM.BEGINDATUM))) > .and(derivedTable.field(AGENDA_ITEM_TYPE.NAAM)?.equal("value") > ?.or(AGENDA_ITEM.ID.eq(agendaItem.id.value) > .and(derivedTable.field(AGENDA_ITEM_TYPE.NAAM)?.notEqual( "value")) > ) > ) > ).execute() > } > Op maandag 12 augustus 2024 om 08:09:57 UTC+2 schreef [email protected]: > >> Thanks for your message. The hint about derived tables not being >> supported is a bit unclear. They're not supported (yet) in the translator >> when rendering jOOQ (Java) code. They are supported in jOOQ, though, as you >> seem to have found out: >> >> https://www.jooq.org/doc/latest/manual/sql-building/table-expressions/derived-tables/ >> >> I've created an issue to improve this warning message: >> https://github.com/jOOQ/jOOQ/issues/17040 >> >> Regarding your other questions: >> >> .where(AGENDA_ITEM_TYPE.ID <http://agenda_item_type.id/>). >> eq(AGENDA_ITEM.ITEM_TYPE_ID) // this where gives an >> compiler error >> >> Just look at the parentheses, and you'll figure it out. >> >> .set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum) >> // The agendaItem signature parameter is not recognized >> >> Is this really a jOOQ problem, or a problem with your kotlin usage? >> >> context.execute(updateQuery) >> // I don't know how todo this, exequeting the query? >> >> You just call updateQuery.execute() >> >> On Sun, Aug 11, 2024 at 11:04 PM Nico van de Kamp <[email protected]> >> wrote: >> >>> I've been searching and googling and.. >>> override fun update( agendaItem : AgendaItem, agendaId: AgendaId, zaakId: >>> ZaakId): Int = >>> >>> val derivedTable = select(AGENDA_ITEM.ID, >>> AGENDA_ITEM.BEGINDATUM, >>> AGENDA_ITEM_TYPE.HELE_DAG >>> ) >>> .from(AGENDA_ITEM >>> ,AGENDA_ITEM_TYPE) >>> .where(AGENDA_ITEM_TYPE.ID).eq(AGENDA_ITEM.ITEM_TYPE_ID) >>> // this where gives an compiler error >>> .asTable("heleDagEvent") >>> >>> val updateQuery = usingDSL { context -> >>> context.update(AGENDA_ITEM) >>> .set(AGENDA_ITEM.BEGINDATUM, agendaItem.begindatum) >>> // The agendaItem signature parameter is not recognized >>> .set(AGENDA_ITEM.EINDDATUM, agendaItem .einddatum) >>> .set(AGENDA_ITEM.BEGINTIJD, agendaItem .begintijd) >>> .set(AGENDA_ITEM.EINDTIJD, agendaItem .eindtijd) >>> .set(AGENDA_ITEM.RECORD_VERSION, agendaItem .recordVersion + 1) >>> .from( >>> AGENDA_ITEM_TYPE, derivedTable >>> ) >>> .where( >>> (AGENDA_ITEM_TYPE.ID).eq(AGENDA_ITEM.ITEM_TYPE_ID) >>> .and(derivedTable.id).eq(agendItem.ID) >>> .and(AGENDA_ITEM.AGENDA_ID).eq( agendaItem .id.value) >>> .and(AGENDA_ITEM.BEGINDATUM).eq(derivedTable.begindatum) >>> .and( >>> ((derivedTable.hele_dag).eq(true)) >>> .or( >>> (AGENDA_ITEM.ID).eq( agendaItem .ID) >>> .and(derivedTable.hele_dag).eq(false) >>> ) >>> ) >>> ) >>> >>> context.execute(updateQuery) >>> // I don't know how todo this, exequeting the query? >>> } >>> >>> >>> -- >>> 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/9041c3ac-df2b-40cf-a7d4-0166966aa792n%40googlegroups.com >>> <https://groups.google.com/d/msgid/jooq-user/9041c3ac-df2b-40cf-a7d4-0166966aa792n%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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/jooq-user/052d9b0f-6e00-4e43-a8ce-b810fd96a28bn%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/052d9b0f-6e00-4e43-a8ce-b810fd96a28bn%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO5xqYa%3DsLAo8SJtkQzAKr2r0yDOtbPwW9a73Wkrtn%3DpCQ%40mail.gmail.com.
