database sequence

[sundar]

Hi all
I have created the surrogate key in database(oracle) using sequence.I have imported the Sequence value in the datastage using a table with the query

SELECT seq.nextval,B.DUMMY_SEQ.NUM FROM DUMMY_SEQ

and i am getting surrogate value in the DS

how do i integrate this in to the target


with regards
sundar

[kwwilliams]

The easiest way to do it would be to not use a lookup, but rather use user-defined sql, add the sequence.nextval to the statement and you are done. If you are set on using a lookup, you will need to create a dummy field in your source stream and fill it with a constant say 1. Then you could have a sparse lookup that looks like this.

select 1 as dummy, sequence.nextval from dual

The first option is probably faster. One thing to point out is that this will always limit your implementation to Oracle. You could use the surrogate key generator or write a routine to create surrogate keys for you.

[sundar]

Hi Williams,

Thank you very much.I am trying to use a user-defined sql in the update action. I am able to insert if i use a query like
insert into table1(field1,field,....) values(:1,:2,......)
but i want to insert the sequence generated by database for each row
if i use a query like
insert into table1(field1,field,....) values(:sequencename.nextval,:2,......) i am getting an error.
Any suggestions.

with regards,
sundar.

[kwwilliams]

insert into table1(field1,field,....) values(schema.sequencename.nextval,:2,......)

Try it without the colon. The colon is telling oracle that this is a bind variable, when what you really want to do is use a database object.

[knowledge]

Hi sundar ,

In the target table , change the option to auto insert and update , copy insert part of the query and then change the option to user defined update , paste the insert part into update option , then wherever ur key column is ,that is , insert into tablename_key,............. values seq.nextval , same order and then delete key column name from the target table ,9 that is you are not taking the value for this colunm from orchastate but u r generating through sequence ),

This will work