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.
