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.
*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] <javascript:>>:
>
>> 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] <javascript:>.
>> 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.
