Hi,
I have a job which retrieves data from a Oracle database and writes into a flat file. Then I have to ftp that file and go back to the database and delete the rows which have been extracted.
I have to delete the rows from the table by selecting the maximum time stamp from the flat file and delete all the rows which have timestamp equal or less than that. Basically deleting the rows from the table which was extracted and written into the flat file. The table gets populated dynamically.
I am using an ODBC stage to connect to Oracle and cannot use the ORAOCI or any other stages(due to license issues) at this point.
I tried using an user -defined query in the odbc stage but didnt know how to delete rows based on the time-stamp, bcos its in a flat file.
Any suggestions would be really helpful.
thanks,
arun.
deleting rows from table using odbc
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Read the flat file using a Sequential File stage.
In a Transformer stage pass along only the timestamp column, and mark it as Key.
In the OBDC stage use user-defined SQL to construct a DELETE statement referring to this key in the WHERE clause.
"Key" in this context means "search key" rather than "primary key".
In a Transformer stage pass along only the timestamp column, and mark it as Key.
In the OBDC stage use user-defined SQL to construct a DELETE statement referring to this key in the WHERE clause.
Code: Select all
DELETE FROM tablename WHERE timestampcol = ?
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.