I am having a doubt in updating a table in oracle
.My requirement is to update a oracle table after my first job gets over.I tried using
oracle(sourcetable)---------->oracle(sametable as target) and have given update userdefined query in the target oracle stage..this is updating the table so many times based on the input row count.How to do update only once.
Oracle stage Update
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Be more specific about the UPDATE statement. It is in the very nature of DataStage to send a row to be updated for every row that arrives in the stage. The only way to update only one row is to cause only one row to arrive in the stage. To update only one row for each row received into the stage the UPDATE statement must have a WHERE clause containing a parameter marker that is bound to a column (or more than one) identified as Key in the Columns grid.
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.
-
- Participant
- Posts: 13
- Joined: Mon Sep 11, 2006 8:01 am
i am updating the date column and ID in the table named X,where ID value is incremented by 1 when the job completes.but in my design
oracle(X table)---->oracle(X table)
its incrementing 15 times as records extracted from source is 15.this record count may vary from time to time.. IS there any work around to update the ID value only once.
oracle(X table)---->oracle(X table)
its incrementing 15 times as records extracted from source is 15.this record count may vary from time to time.. IS there any work around to update the ID value only once.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Put an EXISTS test in the extraction.
Or include a sequentially-executed Transformer stage constrained so that its output link passes only one row. For example:
Code: Select all
SELECT 1 FROM sourcetable WHERE EXISTS ( condition );
Code: Select all
@INROWNUM = 1
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.