Page 1 of 1
Oracle stage Update
Posted: Wed Jun 06, 2007 11:39 pm
by wannabexpert
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.
Posted: Thu Jun 07, 2007 12:53 am
by ray.wurlod
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.
Posted: Thu Jun 07, 2007 1:00 am
by wannabexpert
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.
Posted: Thu Jun 07, 2007 1:17 am
by ray.wurlod
Put an EXISTS test in the extraction.
Code: Select all
SELECT 1 FROM sourcetable WHERE EXISTS ( condition );
Or include a sequentially-executed Transformer stage constrained so that its output link passes only one row. For example: