Jooq framework with Kotlin, issue with a union

[Nico van de Kamp]

Hello,

I have to modify\extend an part of an existing query with Jooq. If I made this query in SQL than it's working with expecting results, but in Jooq I do get an error:

First only the existing part which is working:
..., this.select(REG_ORG.NAAM)      .from(REG_ORG)      .where(REG_ORG.ID.eq(OVR_OVERTREDING.HANDHAVING_DOOR_ID))      .asField<String>(), ....

Now with the code what I have added:
..., this.select(REG_ORG.NAAM)      .from(REG_ORG)      .where(REG_ORG.ID.eq(OVR_OVERTREDING.HANDHAVING_DOOR_ID))      .union(select(REG_ORG.NAAM)      .from(REG_ORG)      .join(REG_MEDEWERKER).on(REG_MEDEWERKER.ORGANISATIE_ID.eq(REG_ORG.ID)      .and(REG_MEDEWERKER.GEBRUIKER_ID.eq(gebruikerId.value))))      .asField<String>(), ...
With extending this union part I get the error "org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression"
If I only add the union part without the existing part, than it is also running fine.
So in my opionion the two queries independently are working good, but if combine to a union I get an error.

[Stephan van Hulst]

What rows do you get when you run the following query?
.select(REG_MEDEWERKER.ORGANISATIE_ID) .from(REG_MEDEWERKER) .where(REG_MEDEWERKER.GEBRUIKER_ID.eq(gebruikerId.value)) .asField<String>()

[Nico van de Kamp]

Stephan van Hulst wrote:What rows do you get when you run the following query?
.select(REG_MEDEWERKER.ORGANISATIE_ID) .from(REG_MEDEWERKER) .where(REG_MEDEWERKER.GEBRUIKER_ID.eq(gebruikerId.value)) .asField<String>()

If I just replace the both queries of the union, than I get an error: "java.lang.ClassCastException: class java.util.UUID cannot be cast to class java.lang.String (java.util.UUID and java.lang.String are in module java.base of loader 'bootstrap')"

I've convert that query in SQL than it returns the UUID of the organisation (REG_ORG) what I expect and it is one value. The MEDEWERKER is not linked to more than one organisation, that's maybe what you thought?

I think my Jooq query is not correct, but...? And the union may return more value's, because a dropdown is filled the names of the REG_ORG.

[Nico van de Kamp]

Ah sorry, my fault, I'm quit new to Jooq and on this moment it hard to read for me. But I have converted the query to SQL and than it is a huge query. I simply thought, here all the organisation are put together and are brought to the front-end for the dropdown. So I only have to add the organistion of th user...

- I have converted the Jooq to SQL and the part what I mention is a subquery of the select.
- And before my change every main record "overtreding" has related one organisation  ==> so that means 1:1.
- But now with my change every "overtreding" has one organisation who is related to this "overtreding" and I add for every "overtreding" the organisation of the user ==> 1:2

This complete query returns one or more "overtreding" records.
With every overtreding has his own organisation. Overtreding records can have the same organisation, so that mus be a distinct
And my change is now: for all "ovetreding" records I have to add one time the user his organisation, so that this is can be used in the dropdown?

On this moment I don't how todo that. It's hard in SQL and even harder in Jooq. I hope that I can figure it out.

[Stephan van Hulst]

So if I understood you correctly, you want a list of all organizations that are related to your input list of overtredingen, and then at the very end, you also want to add the organization that is related to the current gebruiker.

Can you please show us where OVR_OVERTREDING comes from?