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 On Sat, May 4, 2024 at 7:53 PM Nico van de Kamp <[email protected]> wrote: > 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. > > -- > 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/436a0f5a-1768-4540-9cfe-0fa7fdc1dd2dn%40googlegroups.com > <https://groups.google.com/d/msgid/jooq-user/436a0f5a-1768-4540-9cfe-0fa7fdc1dd2dn%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/CAB4ELO5TGxaFux%2B9zw%2BXW2Kc4oyVAgs8q2JLZFePv-DBXL%3De-g%40mail.gmail.com.
