Being late with replying, but I still want to conclude this. On Friday, 29 September 2023 at 08:38:30 UTC+2 Lukas Eder wrote:
In SQL, the column that you've nested ceases to exist outside of the UNION operator because you've nested it. This is different without a UNION (and without DISTINCT), because the column names that are not projected with SELECT may still be accessed by ORDER BY in most "reasonable" dialects. Though again, there might be exceptions, e.g.: https://github.com/trinodb/trino/issues/18726 I mentioned DISTINCT, because that creates a similar situation like UNION. I've described in in this blog post: https://blog.jooq.org/how-sql-distinct-and-order-by-are-related/ Ah, so is this the reason why the version without UNION works? Because it is accessing the table name and not the alias? Here my thinking was wrong. Good to know! In fact, separating the names of different things, I can validate this – the following doesn't work: select row (p.id, p.first_name, p.last_name, ...) as a, book.title from author as p join book on p.id = book.author_id where ... order by a.first_name Use (a).first_name to dereference nested row attributes in PostgreSQL Thanks for the hint! Though, when I try this, I get an error «ERROR: column "a" does not exist» Did I miss something? Do I need a different syntax? -- 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/abe6ad0e-815d-47e3-9007-26fa6eb0cc06n%40googlegroups.com.
