Thank you Lukas.
let me try that exploring your given clues. and update you the status.
What about adding one more column for existing Table columns?
How to achieve that using JOOQ ?
*Actual procedure *
CREATE TABLE #CATALOG_NODE_TEMP
(
HC1_ITEM_ID numeric(15, 0) NULL,
* already_exists numeric(1,0) default 0 not null*
)
insert into #CATALOG_NODE_TEMP
(
HC1_ITEM_ID,)
select
cd.hc1_leaf_id
from c_detail cd
where cd.is_leaf = 1
*My written JOOQ code :*
Table<Record1<Integer>> tab1 =
ctx.select(cd.HC1_LEAF_ID).from(cd).where(cd.IS_LEAF.equal((byte) 1))
.asTable().as("tab1");
As you observe my derived/temp table "*tab1"* has only one column
i.e.HC1_ITEM_ID , me missing one more column "* already_exists
numeric(1,0) default 0 not null"*
How to add that column (into new table) to/from derived table* "tab1" ?*
~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.
