Union with Jooq with Kotlin

[Nico van de Kamp]

Hello,

I'm new to Jooq, but I have to modify a part of a query.

Here I have the existing part of the query which was working well:

<code>

...

this.select(REG_ORG.NAAM)
.from(REG_ORG)
.where(REG_ORG.ID.eq(OVR_OVERTREDING.HANDHAVING_DOOR_ID))

.asField<String>(),
this.select(DSL.coalesce ...

</code>

Now I have change the query like this:

<code>

...

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>(),

this.select(DSL.coalesce ...

</code>

So I have extend this with a union. In console I get the SQL query and If run this part in the "Query console" than I get the result which I expected. But In the console of IntelliJ I get the Error: "org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression"

If add only the new union part without the existing is working also. But the combination  of the two queries (union) is not working.
I try to find out what asField<String>() mean. My feeling is that the it need to be a field in the Select with the generic String.

[Lukas Eder]

Hi Nico,

I think the error message explains it? You cannot have a scalar subquery that produces more than 1 row:

https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/scalar-subqueries/

Check your data. It appears that your union produces 2 rows instead of 1.

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/436a0f5a-1768-4540-9cfe-0fa7fdc1dd2dn%40googlegroups.com.