What I meant was table(name("getCommand")).as("cmd")
2018-05-04 0:04 GMT+02:00 Amit Roy <[email protected]>:
> In name("getCommand").as("cmd") - name returns Name and as() takes a
> SELECT not a string or a table. So, the solution won't compile.
>
> On Wednesday, May 2, 2018 at 10:10:09 AM UTC-4, Lukas Eder wrote:
>>
>> Yes of course, my bad. You should write:
>>
>> *...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, name("getCommand").as("cmd"))
>> .where(aliasedCmd.ID.eq(cmd.PARENT_ID))));
>>
>>
>> 2018-05-01 18:02 GMT+02:00 Amit Roy <[email protected]>:
>>
>>> essentially, in the second union I cannot reference getcommand, which
>>> I'm doing in the SQL.
>>>
>>>
>>> On Tuesday, May 1, 2018 at 11:51:04 AM UTC-4, Amit Roy wrote:
>>>>
>>>> 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(getComman
>>>> d).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]>:
>>>>>
>>>>>> 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(getComman
>>>>>> d).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.
>>>
>>
>> --
> 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.
