update columns dynamically

[PeterPol]

Hello,

I have a table with 4 start/end datetime columns (=8 columns).
During jobrun these columns should be filled with the currentdate dependent on run status.
One way to do this is by creating 8 jobs, each updating one of the date columns.
Another, more elegant option - would be to create 1 job in which a datefield is passed as parameter and sets that field to currentdate. Is this possible?

Peter

[ArndW]

Peter, it sounds like it would be easy to do this in one job, unless there are difficulties that you haven't mentioned.

[PeterPol]

Hello,

I tried doing it in one job, but I got stuck because datastage does not allow me to enter the column name (as parameter) in the columns tab?

Peter

[ArndW]

Ah, now I understand the question. You want to pass the column name dynamically, not the column's value. You cannot do it by putting a parameter in place of a column name.

Are you writing a PX or a Server job (you posted in Server but specified a PX job)?

[PeterPol]

Yep!

I am writing in PX but if solution is only possible in SE than that's ok to me..

Peter

[ArndW]

You can pass the column name into the job as a parameter and use the parameter as a placeholder in custom SQL to read/write/update your database.

[PeterPol]

Hello

I think I still don't get it...

Do you mean entering a user-defined SQL query like this:

UPDATE THETABLE SET #DATECOL#:2 WHERE file_registration_id=:1

What then should be the second row in the columns tab?

Peter

[ArndW]

The name of column in the metadata becomes irrelevant in this case, you can use whatever name you wish.

[PeterPol]

Hello,

I followed your advice, but get the following error on the (user-defined) update sql statement:

DB_SYB_FILE_REGISTRATION,0: Warning: DB_SYB_FILE_REGISTRATION: Sybase Server warning 102 (severity 15) from stored procedure 'Input_0', line 1: Incorrect syntax near ':'.

I created a PX job with 2 DRS/Sybase stages:
One to retrieve the row in the table that should be updated, and
one to update this row using the following user-defined SQL:

UPDATE FRS_FILE_REGISTRATION
SET #DATECOL#=TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS')
WHERE file_registration_id=:1

#DATECOL# is the name of the date column
:2 refers to a timestamp column 'currentdate' to which getdate() is assigned in the first stage.

Peter

[ray.wurlod]

The :1, :2 syntax is appropriate only for Oracle. Use "?" with other databases. Nor does Sybase have a TO_DATE function of this form.

Code: Select all

UPDATE FRS_FILE_REGISTRATION
SET #DATECOL# = ?
WHERE FILE_REGISTRATION_ID = ?

Make sure that the job delivers two columns, one containing the date as a non-Key column, and the file registration ID as a Key column.