update columns dynamically
Moderators: chulett, rschirm, roy
update columns dynamically
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
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
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Code: Select all
UPDATE FRS_FILE_REGISTRATION
SET #DATECOL# = ?
WHERE FILE_REGISTRATION_ID = ?
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.