Hi Otto, Thanks for your message. Do note that SELECT DISTINCT in no way guarantees that you will get only a single value from a subquery, it might do so by accident, occasionally. Perhaps, you meant to correlate your subquery to the outer query by adding some predicate? Or, perhaps you meant to use ARRAY_AGG instead, in the subquery? I don't know what you're really trying to achieve here. What's your complete SQL query that you want to write?
Cheers, Lukas 2018-04-30 18:00 GMT+02:00 <[email protected]>: > Hi > > Not sure if this belongs here, but wanted to ask anyways since i can't > figure out what is wrong. > > I have a Postgres table where I got an array column with id values such as > {1, 5, 22}. I am trying to query the table and return that array as > Field<Long[]> as a subquery for a bigger main query > that i have. However, I always get the result of > "org.postgresql.util.PSQLException: > ERROR: more than one row returned by a subquery used as an expression". > > This is weird, because if I take the whole jooq generated query out from > debugger and paste it my sql editor, it works just fine and returns the > results with the array as one column. Also, I have > other subqueries where I use jooq.selectDistinct() to get ids and combine > them with PostgresDSL.array() and they work just fine, there is something > weird going on when I select the array here. > > What am I missing :( > > The query: > > private Field<Long[]> addIdQuery() { > > Field<Long[]> ids = jooq.selectDistinct(*MATERIALIZED_VIEW_TABLE.ID_ARRAY*) > // This is {1, 5, 22} > .from(*MATERIALIZED_VIEW_TABLE*) > .WHERE(*MATERIALIZED_VIEW_TABLE*.IS_ACTIVE.isTrue()) > .asField(FIELD_NAME); > > return ids; > > } > > -- > 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]. > For more options, visit https://groups.google.com/d/optout. > -- 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]. For more options, visit https://groups.google.com/d/optout.
