It seems you're simply missing (as the error helpfully indicates 😉) the "cmd" table in your union's second subquery. Add
.from(aliasedCmd, cmd) I hope this helps, Lukas 2018-04-25 0:53 GMT+02:00 Amit Roy <[email protected]>: > In the section "The equivalent Jooq representation", there is a typo. > > Please read "Result<?> obj = create.with(getCommand).select().fetch();" > as "Result<?> obj = create.withRecursive(getCommand).select().fetch();" > > The run time error message as a result changes to > =========== > org.jooq.exception.DataAccessException: SQL [with recursive > "getCommand"("id", "parent_id") as ((select "cmd"."id", "cmd"."parent_id" > from "internal"."command" as "cmd" where "cmd"."id" = ?) union (select > "aliasedCmd"."id", "aliasedCmd"."parent_id" from "internal"."command" as > "aliasedCmd" where "aliasedCmd"."id" = "cmd"."parent_id")) select 1]; > ERROR: missing FROM-clause entry for table "cmd" > Position: 274 > =========== > > > > On Tuesday, April 24, 2018 at 11:47:25 AM UTC-4, Amit Roy wrote: >> >> I'm trying to write the following query is jooq and so far not having >> success. Getting a run time exception, where I see that the JOOQ generated >> sql is not matching my original one. >> >> My SQL >> ========== >> final String sql = WITH RECURSIVE getCommand(id, parent_id) AS( >> SELECT id, parent_id FROM command WHERE id='some_val' >> UNION >> SELECT aliasedCmd.id, aliasedCmd.parent_id FROM getCommand cmd, command >> aliasedCmd WHERE aliasedCmd.id = cmd.parent_id) >> SELECT * FROM getCommand; >> ========== >> >> The equivalent Jooq representation >> ========== >> xxx.jooq.internal.tables.Command cmd = COMMAND.as( "cmd" ); >> xxx.jooq.internal.tables.Command aliasedCmd = COMMAND.as( >> "aliasedCmd" ); >> >> CommonTableExpression<Record2<String,String>> getCommand = >> name("getCommand") >> .fields("id","parent_id") >> .as(create >> .select(cmd.ID, cmd.PARENT_ID) >> .from(cmd) >> .where(cmd.ID.eq("some_val")) >> .union(create >> .select(aliasedCmd.ID, aliasedCmd.PARENT_ID) >> .from(aliasedCmd) >> .where(aliasedCmd.ID.eq(cmd.PARENT_ID)))); >> >> >> Result<?> obj = create.with(getCommand).select().fetch(); >> ========== >> >> The run time error message >> ========== >> org.jooq.exception.DataAccessException: SQL [with "getCommand"("id", >> "parent_id") as ((select "cmd"."id", "cmd"."parent_id" from >> "internal"."command" as "cmd" where "cmd"."id" = ?) union (select >> "aliasedCmd"."id", "aliasedCmd"."parent_id" from "internal"."command" as >> "aliasedCmd" where "aliasedCmd"."id" = "cmd"."parent_id")) select 1]; >> ERROR: missing FROM-clause entry for table "cmd" >> Position: 264 >> ========== >> >> Thanks, >> >> Amit. >> > -- > 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.
