Hi Michael, I see, thanks for the clarification.
*A note on selectFrom()* The usage of selectFrom() can lead to such confusion, because the inferred type of <R> in the resulting Select<R> is the type of the table, e.g. HomeEmailRecord or WorkEmailRecord (I'm assuming that ATTENDEES is your schema). In that case, due to how generics work in Java, union cannot be used, because those two records are not compatible. This doesn't work exactly as in SQL, where it is totally possible to write SELECT * FROM home_email UNION SELECT * FROM work_email iff both tables have the same row type. Usually, selectFrom() is used to fetch an "active record" (or UpdatableRecord) for modification and later storing it back to the database: https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords When you write a union, however, you usually do not want to fetch active records, but simply tuples, so you will list all of your columns explicitly. *Regarding your specific query:* I've just noticed from your description, that ATTENDEES is not your schema, but your table. In that case, you've attempted to put a **column** (HOME_EMAIL or WORK_EMAIL) in the FROM clause of your statement, which doesn't work. Here's a translation of your intended SQL statement to jOOQ: context.select( concat(ATTENDEES.LAST_NAME, inline(", "), ATTENDEES.FIRST_NAME, inline(" (home)")).as("name"), ATTENDEES.HOME_EMAIL) .from(ATTENDEES) .where(ATTENDEES.LIST.eq(1)) .union( select( concat(ATTENDEES.LAST_NAME, inline(", "), ATTENDEES.FIRST_NAME, inline(" (work)")).as("name"), ATTENDEES.WORK_EMAIL) .from(ATTENDEES) .where(ATTENDEES.LIST.eq(1))) .fetch(); The above is assuming, you have this import import static org.jooq.impl.DSL.*; More info about the inline() method (as well as bind values in general) here: https://www.jooq.org/doc/latest/manual/sql-building/bind-values I hope this helps, Lukas 2017-11-27 14:17 GMT+01:00 Michael Redlich <[email protected]>: > Hi Lukas: > > Thanks for getting back to me so quickly! > > Here is one variation of what I tried based on the documentation: > > public void testUnion(DSLContext context) { > // String sql = "SELECT CONCAT(last_name,', ',first_name,' (home)') AS > name,home_email FROM attendees WHERE list = 1 UNION SELECT > CONCAT(last_name,', ',first_name,' (work)') AS name,work_email FROM attendees > WHERE list = 1"; > > Result<Record> context.selectFrom(ATTENDEES.HOME_EMAIL) > .union(context.selectFrom(ATTENDEES.WORK_EMAIL)) > .fetch(); > } > > Passing in an instance of *DSLContext* has worked with other methods. And > what I failed to do was consider using *Record2<String,String>*. > > I will have to a look...thanks! > > Mike. > > > > On Monday, November 27, 2017 at 3:18:53 AM UTC-5, Lukas Eder wrote: >> >> Hi Mike, >> >> Thanks for your message. What have you tried? Would you mind posting your >> jOOQ code as well? >> >> Essentially (just like in SQL), your two union subqueries must have >> exactly the same row type. In your case, that would correspond to >> Record2<String, String> >> >> Thanks, >> Lukas >> >> 2017-11-27 2:47 GMT+01:00 Michael Redlich <[email protected]>: >> >>> Hello: >>> >>> I'm still new to jOOQ. I'm trying to duplicate the following SQL >>> statement: >>> >>> String sql = "SELECT CONCAT(last_name,', ',first_name,' (home)') AS >>> name,home_email FROM attendees WHERE list = 1 UNION SELECT >>> CONCAT(last_name,', ',first_name,' (work)') AS name,work_email FROM >>> attendees WHERE list = 1"; >>> >>> I've tried the *.union()* method in conjunction with *.selectFrom()* and >>> with *.select()* methods and still getting errors relative to types, etc.. >>> >>> I would appreciate any help...thanks! >>> >>> Mike. >>> >>> -- >>> 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. > -- 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.
