error in example SQL ?

[tod...@googlemail.com]

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

[Lukas Eder]

Thanks for your message. Yes of course, there's a wrong table alias here:

JOIN actor AS f

It should be:

JOIN actor AS a

I added the aliases for readability purposes. The actual query generated by jOOQ doesn't alias the tables

Best Regards,

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/0fca15d0-b12b-471a-9258-44b9310d7482n%40googlegroups.com.