DSXchange

We have a situation in which we have to 'Insert else Update' or 'Update else Insert'.

It works fine at is is for us. In our project the SKEY are the target tables are generated using Oracle Sequencers which have to called in the Insert statements as they are not - called by Triggers.

So, can we make the SQL to be 'Custom SQL' and have both Update and Insert statements one after the other and expect the same Functionality as Upsert or is it that DataStage or Oracle will treat them as separate 2 SQL statements and will execute both of them.

Which one occurs first depends on which option you choose. Obviously you only invoke the sequence in a custom INSERT statement - for an UPDATE statement you must already know the key value.

Actually my question is - if we use CUSTOM SQL with both Insert and Update statements one after the other, will DataStage and Oracle still treat it as Upsert Statement or not ?

I'm actually not sure, never tried it as I tend to avoid those 'combo' actions. Why not give it a shot and let us know?

Tried and Tested it. It actually executes both Insert and Update statements one after the other and doesn't do them conditionally ?

Can I use Merge Statement in ORAOCI stage ?

PS: We are on Linux Platform with DataStage 8.1 Server edition connecting to Oracle 10g using OraOci 9i stage..

You should be able to use Merge statement