DSXchange

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

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

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

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)?

Yep!

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

Peter

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.

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

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

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

The :1, :2 syntax is appropriate only for Oracle. Use "?" with other databases. Nor does Sybase have a TO_DATE function of this form. 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.