Thanks Lukas for pointing out the issue. Now, it's not throwing exception
but the result is different from the original query. The jooq generated
query is slightly different from the original one. I'm copying the original
query, jooq expression and the jooq generated query.
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
==========
*...jooq.internal.tables.Command cmd = COMMAND.as( "cmd" );
*...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, cmd)
.where(aliasedCmd.ID.eq(cmd.PARENT_ID))));
Result<?> obj =
create.withRecursive(getCommand).select().from(getCommand).fetch();
==========
The jooq generated query
==========
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", "internal"."command" as "cmd" where
"aliasedCmd"."id" = "cmd"."parent_id")) select "getCommand"."id",
"getCommand"."parent_id" from "getCommand"
==========
Amit.
On Friday, April 27, 2018 at 9:41:26 AM UTC-4, Lukas Eder wrote:
>
> 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] <javascript:>>:
>
>> 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] <javascript:>.
>> 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.
