Hi,
In oracle, we can create a sequence(eg., myseq) and use it in any sql-query as
insert into ttest values(myseq.nextval,'abc');
Can we create a sequence similarly in teradata also? if so please let me know how.
I want to use that sequencer in user-defined sql statement in teradata-multiload stage.
regards,
ssunda.
calling a sequencer from teradata-multi load stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Have you tried using user-defined SQL? Have you tried using a database sequence in the manner you describe? The important thing, if you do, is NOT to provide a value from DataStage for that particular column; the number of columns in the DataStage job must exactly match the number of parameter markers in the SQL statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Hi Ray,
I tried creating a sequencer in teradata. It is not identifying the syntax "create sequence". So, may be it has some other syntax or I am not sure if we can create a sequencer in teradata.
Iam not able to read the whole content posted by you, Ray.(Iam not a charter memeber). So , I dont know wat you have discussed in the previous reply. Please post the whole reply again. or shall I give my e-mail ID.
thanks in advance,
regards,
ssunda.
I tried creating a sequencer in teradata. It is not identifying the syntax "create sequence". So, may be it has some other syntax or I am not sure if we can create a sequencer in teradata.
Iam not able to read the whole content posted by you, Ray.(Iam not a charter memeber). So , I dont know wat you have discussed in the previous reply. Please post the whole reply again. or shall I give my e-mail ID.
thanks in advance,
regards,
ssunda.
Don't you have access to any Teradata resources where you work? You need to find out if they even support the concept, something analogous to an Oracle 'sequence' object or other database's SERIAL field - and that's got nothing to do with DataStage.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi All,
Creating sequences in teradata(using IDENTITY) was not possible, as Database structure changes was not allowed in our case. Hence we went with the following approach. Input file contains empnames. Into target table, unique numbers for empno should be inserted.
we used a column generator to generate the sequence numbers with the following logic:
The stages used are-
column generator (adding a column 'dummy' with value '1') ->
Join stage ( with Teradata enterprise stage as another input to Join stage(which gets the max(empno),1 as dummy) ) ->
column generator ( to generate the column 'count'-column generator generates values from 1, with incremental value of '1' ) ->
transformer (which performs addition of count and max(empno))->
target( teradata stage)
Explanation:
Add a dummy column (e.g., dummy ) with a value '1' to the incoming data. The main purpose of adding a dummy column is to have a common column so that we can perform Join operation. We will get max(empno),1 as dummy from teradata stage. Output of join stage is empname,max(empno). Column generator generates unique values for 'count'(starting from 1 ). In the transformer stage, add max(empno) and 'count' -this is value for empno. Finally empno, empname are inserted into target table.
Here we need not maintain a temp file and update the max(empno) everytime to the file.
Any inputs regarding the above approach are welcome.
regards,
ssunda.
Creating sequences in teradata(using IDENTITY) was not possible, as Database structure changes was not allowed in our case. Hence we went with the following approach. Input file contains empnames. Into target table, unique numbers for empno should be inserted.
we used a column generator to generate the sequence numbers with the following logic:
The stages used are-
column generator (adding a column 'dummy' with value '1') ->
Join stage ( with Teradata enterprise stage as another input to Join stage(which gets the max(empno),1 as dummy) ) ->
column generator ( to generate the column 'count'-column generator generates values from 1, with incremental value of '1' ) ->
transformer (which performs addition of count and max(empno))->
target( teradata stage)
Explanation:
Add a dummy column (e.g., dummy ) with a value '1' to the incoming data. The main purpose of adding a dummy column is to have a common column so that we can perform Join operation. We will get max(empno),1 as dummy from teradata stage. Output of join stage is empname,max(empno). Column generator generates unique values for 'count'(starting from 1 ). In the transformer stage, add max(empno) and 'count' -this is value for empno. Finally empno, empname are inserted into target table.
Here we need not maintain a temp file and update the max(empno) everytime to the file.
Any inputs regarding the above approach are welcome.
regards,
ssunda.