Hi Lukas,
Still I am getting same error.
It seems just stupid that I need to orderBy all selected column , sill it
does not run properly.
JOOQ does not have a simple auto increment logic. Its just very cumbersome
to use orderBy in this case....even if I use too...still same error.
Really painful.
*I tried below stuff*
Table<Record5<Integer, Short, Integer, String, Integer>> hlTempTable =
ctx.select( *hl.ITEM_ID, hl.HA_ID, hl.PERIOD_ID, *
* hl.LEAF_LABEL,(DSL.rowNumber().over().add(1)).as("ID")).from(hl)*
.join(hlmTempTable)
.on(( hl.ITEM_ID.eq(hlmTempTable.field(ITEM_ID)))
.and(hl.PERIOD_ID.eq(hlmTempTable.field(PERIOD_ID)))
.and(hl.HA_ID.eq(hlmTempTable.field(HA_ID)))).*orderBy(hl.ITEM_ID,
hl.HA_ID, hl.PERIOD_ID,hl.LEAF_LABEL*).asTable().as("hlTempTable");
*Error:*
Exception in thread "main" org.jooq.exception.DataAccessException: SQL
[select [hlTempTable].[ITEM_ID], [hlTempTable].[HA_ID],
[hlTempTable].[PERIOD_ID], [hlTempTable].[LEAF_LABEL], [hlTempTable].[ID]
from (select top 100 percent [hl].[ITEM_ID], [hl].[HA_ID],
[hl].[PERIOD_ID], [hl].[LEAF_LABEL], (row_number() over () + ?) [ID] from
[dbo].[HIERARCHY_LEAF] [hl] join (select [hl].[ITEM_ID], [hl].[HA_ID],
max([hl].[PERIOD_ID]) [PERIOD_ID] from [dbo].[HIERARCHY_LEAF] [hl] group by
[hl].[ITEM_ID], [hl].[HA_ID]) [hlmTempTable] on ([hl].[ITEM_ID] =
[hlmTempTable].[ITEM_ID] and [hl].[PERIOD_ID] = [hlmTempTable].[PERIOD_ID]
and [hl].[HA_ID] = [hlmTempTable].[HA_ID]) order by [hl].[ITEM_ID] asc,
[hl].[HA_ID] asc, [hl].[PERIOD_ID] asc, [hl].[LEAF_LABEL] asc)
[hlTempTable] -- SQL rendered with a free trial version of jOOQ 3.2.2]; The
ranking function "row_number" must have an ORDER BY clause.
at org.jooq.impl.Utils.translate(Utils.java:1211)
at
org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:495)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:313)
at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:324)
at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:1034)
at
org.jooq.samples.SampleTemp_phantom_add_detail_Old.main(SampleTemp_phantom_add_detail_Old.java:321)
Caused by: java.sql.SQLException: The ranking function "row_number" must
have an ORDER BY clause.
Thank you.
~Shyam
On Friday, January 31, 2014 3:32:43 PM UTC+5:30, Sha wrote:
>
> Will try it ....hope it wont change the logic.
>
> On Friday, January 31, 2014 1:17:55 PM UTC+5:30, Lukas Eder wrote:
>>
>> Hi Shyam
>>
>>
>> 2014-01-31 Sha <[email protected]>:
>>
>>> Hi Lukas sorry for delay reply.
>>>
>>> Here are my updates/findings Lukas,
>>>
>>> *Scenario 1 :*
>>> *The actual quary i used.*
>>>
>>> Table<Record5<Integer, Short, Integer, String, Integer>> hlTempTable =
>>> ctx.select( hl.ITEM_ID, hl.HA_ID, hl.PERIOD_ID,
>>> hl.LEAF_LABEL,*(DSL.rowNumber().over()).as("ID"))*
>>> ).from(hl).asTable().as("hlTempTable");
>>>
>>> *Error *
>>> Caused by: java.sql.SQLException: The ranking function "row_number" must
>>> have an ORDER BY clause.
>>>
>>
>> True, I forgot about this SQL Server "limitation". You can add the ORDER
>> BY clause, trivially:
>>
>> DSL.rowNumber().over().orderBy(... the ordering column, e.g. hl.ITEM_ID
>> ...)
>>
>>
>> *Scenario 2 :*
>>> *The actual quary i used.*
>>> Table<Record5<Integer, Short, Integer, String, Integer>> hlTempTable =
>>> ctx.select( hl.ITEM_ID, hl.HA_ID, hl.PERIOD_ID,
>>> hl.LEAF_LABEL,(*DSL.rownum()).as("ID")*
>>> ).from(hl).asTable().as("hlTempTable");
>>>
>>> *Error :*
>>> Caused by: java.sql.SQLException: Invalid column name 'rownum'.
>>>
>>> I need to modify the logic from SQL queries to JOOQ queries.
>>> I have no authority to change the logic as I dont know how it is going
>>> to impact at the end.
>>> So this wont fit in my scenario.
>>>
>>> Are there any clues how to go about it ?
>>>
>>> Thank you.
>>> ~Shyam
>>>
>>>
>>>
>>>
>>> On Thursday, January 30, 2014 1:08:22 PM UTC+5:30, Lukas Eder wrote:
>>>
>>>> While this is probably hard to emulate *in general*, in this particular
>>>> case, you might be able to emulate identities using ROW_NUMBER() OVER(),
>>>> or
>>>> DSL.rowNumber().over(), respectively.
>>>>
>>>>
>>>> 2014-01-30 Sha <[email protected]>:
>>>>
>>>>> Hi Lukas,
>>>>> As you advised me trying to emulate the temporary table scenario
>>>>> using asTable() method.
>>>>>
>>>>> But I one more challenge here.
>>>>>
>>>>> Below is the sample scenario to explain the problem.
>>>>>
>>>>> *The piece which i am trying to covert to JOOQ equalent is below*
>>>>>
>>>>>
>>>>> CREATE TABLE #hlm -- *this is a temp table in SQLServer DB *
>>>>>
>>>>> (
>>>>> * id INT IDENTITY(1, 1) NOT NULL, *
>>>>> item_id INT,
>>>>> ha_id INT,
>>>>> period_id INT
>>>>> )
>>>>> insert into #hlm (item_id, ha_id, period_id)
>>>>> SELECT item_id,
>>>>> ha_id,
>>>>> MAX(period_id) AS period_id
>>>>> from h_leaf
>>>>> group by ITEM_ID, ha_id
>>>>>
>>>>>
>>>>> *JOOQ equalent i have written*
>>>>>
>>>>> HLeaf hl = H_LEAF.as("hl");
>>>>>
>>>>> Table<Record3<Integer, Short, Integer>> hlmTempTable =
>>>>> ctx.select(hl.ITEM_ID,hl.HA_ID,((hl.PERIOD_ID).max()).as("PERIOD_ID"))
>>>>> .from(hl).groupBy(hl.ITEM_ID,hl.HA_ID).asTable().as("
>>>>> hlmTempTable");
>>>>>
>>>>> This working as expected but the problem is how to achieve the
>>>>> effecting of auto increment.
>>>>> i.e the missing column "* id INT IDENTITY(1, 1) NOT NULL,
>>>>> "*
>>>>>
>>>>> How to about it with JOOQ ?
>>>>>
>>>>> Thank you,
>>>>> ~Shyam
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> 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/groups/opt_out.
>>>>>
>>>>
>>>> --
>>> 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/groups/opt_out.
>>>
>>
>>
--
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/groups/opt_out.
