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
Peter, it sounds like it would be easy to do this in one job, unless there are difficulties that you haven't mentioned.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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)?
Are you writing a PX or a Server job (you posted in Server but specified a PX job)?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
The name of column in the metadata becomes irrelevant in this case, you can use whatever name you wish.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.