How to prevent a column from being updated in an Insert/Upda

[LANDO]

Hello,

I have a table that I'm inserting/updating .
I want to add two columns one 'Insert_date' and 'Update_date'
How can I make sure that the insert column is not updated and will only only be loaded once ( When the record is created )
I do not want to split my DS into seperate operations i.e. - one for insert and one for update.
In a regular PL/SQL I would use a merge statement

So in other words how can I implement a merge statement into DS

[chulett]

Use two links.

[baigdw]

Use two links.

You can use a customized sql to handle your insert/update statement.

[jzparad]

Define the insert_date column with a default value of sysdate. This way you will never have to explicitly set the value and it will be set only once.

[chulett]

When I said "Use two links", I was being facitious and hoping to provoke a reason for the "I don't want to split my DS into separate operations" comment.

You can use a customized sql to handle your insert/update statement.

Next time, instead of simply rolling your eyes and making a generic statement, it would be best if you backed it up and explained the 'customized sql' you can use to accomplish this. All columns in the stage must be bound in each sql statement, you cannot leave columns out of one of the two statements that you don't want to be updated but you do want inserted.

For a database like Oracle, you can leave the insert_date out of the Columns tab and "customize the sql" to use SYSDATE in its place in the insert statement. This way all columns are bound but an additional column is used on the insert side. Use your equivalent function of choice in other databases. I'm assuming this is what you meant when you made your statement.

Now, you can also do as jz suggested by leveraging database default values, but again you accomplish this by not mentioning the insert_date field in the insert statement, hence still binding all columns. It will then take the default value, which is typically NULL but can be overridden to take the system date in that case.

[baigdw]

Use two links.

Chulett
Lando had mentioend he did not want to use two seperate links and u gave him the suggestion of using two links ??
My suggestion of using the customized sql was based on the assumption that the users have a basic knowledge of working with DataStage.
If he had asked about the details of how to write a customized sql then Yes I could have given the explaination. Right ?
Thanks for your comments.

[LANDO]

: You can use a customized sql to handle your insert/update statement.

Thanks baigdw,

Using customized sql solved the issue.

And thanks to everyone for their comments

[chulett]

Chulett
Lando had mentioend he did not want to use two seperate links and u gave him the suggestion of using two links ??
My suggestion of using the customized sql was based on the assumption that the users have a basic knowledge of working with DataStage.
If he had asked about the details of how to write a customized sql then Yes I could have given the explaination. Right ?
Thanks for your comments.

I said why I made the comment. My day got away from me or I would have followed up on it much sooner.

I'm not sure we can make an assumption of "basic knowledge of working with DataStage" anymore. I try to take a user's post count and how they rate themselves in their profile into account, but that doesn't always help. I've found it best to not make too many assumption and to try and be thorough in your answer. This can help people right away without them having to ask. If they do know the answer and don't ask for clarification, having that in the post regardless can help searchers who come later looking for help on the same issue.

My two cents.

[rwierdsm]

The approach I've used in the past is to only insert/update the update date in the main sql. Put an update statement in the 'after' subroutine part of the OCI or ODBC or whatever, setting the insert_date to today's date where the insert date is null.

This should only update the row you've just inserted if this is an insert and no rows if it was an update.