Maybe I missed something, but when I try the PostgreSQL example from the
otherwise great "No More MultipleBagFetchException Thanks to Multiset
Nested Collections" blog post, I get the following error:
[Code: 0, SQL State: 42P01] ERROR: missing FROM-clause entry for table "a"
Position: 156 [Script position: 156 - 168]
I've tried with DBVisualizer and DBeaver.
The faulty SQL:
SELECT
f.title,
(
SELECT coalesce(
jsonb_agg(jsonb_build_array("v0", "v1")),
jsonb_build_array()
)
FROM (
SELECT
a.first_name AS "v0",
a.last_name AS "v1"
FROM film_actor AS fa
JOIN actor AS f
USING (actor_id)
WHERE fa.film_id = f.film_id
) AS "t"
) AS actors,
(
SELECT coalesce(
jsonb_agg(jsonb_build_array("v0")),
jsonb_build_array()
)
FROM (
SELECT c.name AS "v0"
FROM film_category AS fc
JOIN category AS c
USING (category_id)
WHERE fc.film_id = f.film_id
) AS "t"
) AS categories
FROM film AS f
ORDER BY f.title
'a' isn't declared somewhere and the actor table doesn't contain column
film_id ...
Kind regards
Dominik
--
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/0fca15d0-b12b-471a-9258-44b9310d7482n%40googlegroups.com.
