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
database sequence
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: database sequence
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
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.
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.
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
seq.nextval
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
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