Update tabla through Oracle Stage

Devyani Sarkar · Post by **Devyani Sarkar** » Tue Jun 07, 2005 2:21 am

How do I achieve Update as TFM --> Oracle Stage.
Query: UPDATE Meta_Ftp_Adapter SET col1=:1 WHERE col1=:2
Since DStage does not let two columns with same name in the col definitions.

Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Jun 07, 2005 3:04 am

Within DataStage use different column names. The first column in the grid replaces the parameter marker :1, the second replaces :2, and so on. Make sure that the one to appear in the WHERE clause is marked as Key.
Choose user-defined SQL as the preferred method, then create the UPDATE statement that you indicated. You may get a warning about metadata mismatch, but it will work.

Devyani Sarkar · Post by **Devyani Sarkar** » Tue Jun 07, 2005 3:18 am

Thanks Ray,
I was doing exactly you have mentioned. The stop comes then the required task is to update the same column which appears in the where clause as shown
UPDATE Table SET col1=:1 WHERE col1=:2.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jun 07, 2005 3:32 am

I am not sure whether you have successfully implemented the logic described by Ray or whether you are having problems.

You need to use 2 columns in your link to update - say fld1 and fld2. One of them (say fld2) is to be marked as key. The datatype for both fields must be identical to that of col1.

In your update SQL, you say,

update tablename set col1 = :1 where col1 = :2

Devyani Sarkar · Post by **Devyani Sarkar** » Tue Jun 07, 2005 3:38 am

Thanks Ray my code works.

Can we implement UPDATE Table SET col1=rowid ?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jun 07, 2005 3:49 am

You must be able to do that (as long as col1 has datatype to hold the value).

But why will anyone require to issue such a command as rowid may change upon the update itself.

Devyani Sarkar · Post by **Devyani Sarkar** » Tue Jun 07, 2005 4:00 am

I have this requirement where I have a column named rowid_id which holds the value of rowid of the record. I could not achieve this, hence if anyone has some solution to it. Thanks.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jun 07, 2005 4:11 am

Can you explain why you have it, what you wish to achieve from it and how you are approaching it so we can try to find a solution or alternative way.

Devyani Sarkar · Post by **Devyani Sarkar** » Tue Jun 07, 2005 4:34 am

The Datamodel and already existing design has it working. We do the task Oracle query by Cognos. We are over to porting DStage environment, Hence I need to achive it in DStage so let me know if you can implement the update. Thanks.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Jun 07, 2005 4:46 am

Did you try running it outside DataStage?

If you can, then you can include the SQL as a post-job script.

But still, I believe such an update cannot be utlized as rows may migrate during updates or or migration or rebuild process.

Let us know if you encounter any difficulty in this.

Devyani Sarkar · Post by **Devyani Sarkar** » Tue Jun 07, 2005 5:08 am

My question was to know if I can implement the update by Dstage?

chulett · Post by **chulett** » Tue Jun 07, 2005 6:39 am

Devyani Sarkar wrote:I have this requirement where I have a column named rowid_id which holds the value of rowid of the record.

This is a... strange... requirement. I have no idea what value you would bring to the table by pulling the ROWID out into a data field.

However, it can be done. Keep in mind the fact that ROWID is a psuedo-column, so the only way I know you can achieve this is by pulling it out and putting it in a VARCHAR2 or CHAR field. Use the ROWIDTOCHAR function to get it out and into a 'saveable' format. Of course, there's a CHARTOROWID function to get it put back.

Or is your 'existing design' storing it in a different datatype?