Convert SQL query to JooQ

[Nico van de Kamp]

I've written an SQL query and put this in the JooQ converter.

I have tried a lot but the Select statement on this moment the issue why it is not correct:

usingDSL { context ->

        context.select(
            case_()
                .when(count().gt(inline(0)), inline(true))
            .otherwise(inline(false))
        )

I have tried to put in the case_(count()) and then when (true, true), but still an error.

I have tried with else_ (false), is also wrong.

-----

Another thing what I'm asking myself is why is everything quoted when convertedwith the JooQ converter?

Why not just this:

.from(ZAAK).`as`("zz"),

          (AGENDA). `as`("za"),

          (AGENDA_ITEM). `as`("za"),

)

Instead off:

.from(
        table(unquotedName("zaak")).`as`(unquotedName("zz")),
        table(unquotedName("agenda")).`as`(unquotedName("za")),
        table(unquotedName("agenda_item")).`as`(unquotedName("zai"))

"table(unquotedName("zaak")).`as`(unquotedName("zz"))" used.

Everything is quoted also field names like:

.where(field(name("zai2", "agenda_id")).eq(field(name("zai", "agenda_id")))

Why not:

.where(zai2.AGENDA_ID.eq(zai.AGENDA_ID)

This is so hard to read

[Lukas Eder]

Thanks for your message.

On Mon, Aug 26, 2024 at 5:54 PM Nico van de Kamp <nicova...@gmail.com> wrote:

I've written an SQL query and put this in the JooQ converter.

I have tried a lot but the Select statement on this moment the issue why it is not correct:

usingDSL { context ->

        context.select(
            case_()
                .when(count().gt(inline(0)), inline(true))
            .otherwise(inline(false))
        )

I have tried to put in the case_(count()) and then when (true, true), but still an error.

I have tried with else_ (false), is also wrong.

I'm not sure what this means, I'm sorry. Why would it be wrong? Are you getting an error? Are you getting the wrong result? Please be specific.

Btw, you don't have to turn your condition into a boolean. Just select the condition itself: select(count().gt(0))

 

Another thing what I'm asking myself is why is everything quoted when convertedwith the JooQ converter?

Why not just this:

.from(ZAAK).`as`("zz"),

          (AGENDA). `as`("za"),

          (AGENDA_ITEM). `as`("za"),

)

Instead off:

.from(
        table(unquotedName("zaak")).`as`(unquotedName("zz")),
        table(unquotedName("agenda")).`as`(unquotedName("za")),
        table(unquotedName("agenda_item")).`as`(unquotedName("zai"))

"table(unquotedName("zaak")).`as`(unquotedName("zz"))" used.

Everything is quoted also field names like:

.where(field(name("zai2", "agenda_id")).eq(field(name("zai", "agenda_id")))

Why not:

.where(zai2.AGENDA_ID.eq(zai.AGENDA_ID)

This is so hard to read

See: https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-name-style/ 

[Nico van de Kamp]

Ah sorry Lukas, What I mean is I get a compiler error for this:

override fun InconsistencyCheck(zaakId: ZaakId): Boolean = usingDSL { context ->

context.select(                             // error: "Not enough information to infer type variable T1" and see also my screebshot. I think is a JooQ error not Kotlin.
case_()
.when(count().gt(inline(0)), inline(true))  // error at ',', "expecting ')', but if count correct then pairs () are correct
.otherwise(inline(false))
)                                            // error: "Expecting an Element", but I do not understand what is meant by that?
.from(                                       // error "import from". But I have used from in other fun as well, in the same Kotlin file

table(unquotedName("zaak")).`as`(unquotedName("zz")),

....

these are the compiler errors, but I don't know what they exactly mean, so that I know how to solve it. (I know how to get work in SQL)

Thanks for helping me, after the whole day trying and puzzling....

Op maandag 26 augustus 2024 om 17:58:11 UTC+2 schreef lukas...@gmail.com:

[Lukas Eder]

Your when() method call should be quoted in kotlin, i.e. write `when`(...).

I hope this helps,

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/63ab4c02-e6c1-45e6-a4d3-dd31092dd6b0n%40googlegroups.com.

[Nico van de Kamp]

I have found an solution... with (ChatGPT)

override fun agendaInconsistencyCheck(zaakId: ZaakId): Boolean = usingDSL { context ->
context
.select(
`when`(count().gt(0), `val`(true)).otherwise(`val`(false))     // `val`(false) ensure that true and false are converted into Field<Boolean> objects that JOOQ understands
)
.from(

...

.where(...

).

.fetchOne(0, Boolean::class.java) == true                           // == true on 'advise' of IntelliJ.

It is working but unfortunenatly I do not exactly understand why == true is needed.

Op dinsdag 27 augustus 2024 om 07:54:30 UTC+2 schreef lukas...@gmail.com: