Hi All,
Forgive me for the delay. Been caught up with work. Actually, I need to update a particular field in a table through DataStage.
Which is similar to this statement:
Code: Select all
UPDATE TABLE1
SET Col3 = 'DSX'
WHERE Col3 LIKE 'DSX%'
Sample Input & Output data and formats:
If the incoming data is :
Code: Select all
ULE
RSXoih
DSXchange
DSXhghghg
DSXigjoi099
DSX903u09ru0
DSXncc8
ULE
Then the output should be:
Code: Select all
ULE
RSXoih
DSX
DSX
DSX
DSX
DSX
ULE
My first idea was to pull all the records from the database to a sequential file, where I update the required field and then in an other job I load this sequential file back to the table using ORABLK Stage. The table has around 3-4 million records.
Some what like this:
Design 1 (Two Jobs)
Code: Select all
OracleTable-----------> Transformer -------------> Sequential File
(Update required field)
Code: Select all
Sequential File-----------> Transformer ----------->OracleTable(BLK Load)
Some how, the idea seemed inefficient to me as I was using two jobs instead of just one. So, I thought I would go for Design 2 as below-
Design 2
Code: Select all
Transformer(Dummy) --------------> OCI Stage (run Custom update SQL)
In this design, I will have a dummy transformer by defining a dummy stage variable to make the compiler happy (learned from Mr.Craig)
![Smile :)](./images/smilies/icon_smile.gif)
.
For the link, I have all the meta-data of the table whose derivations contain the dummy stage variable. The link has a constraint "@FALSE".
Now, In the OCI stage, I selected "User defined Query" and my custom SQL statement was:
Code: Select all
UPDATE TABLE1
SET Col3 = 'DSX'
WHERE Col3 LIKE 'DSX%'
Note: The OCI stage has all the columns of the table in the metadata, not the just 'Col3' which is supposed to be updated.
BUT, When I run this job:
I get this error:
Code: Select all
JobName..LinkName: ORA-01036: illegal variable name/number
Am I missing anything or Am I going wrong somewhere?
Many Thanks for your earlier responses,
Naveen.