Merge statement with Oracle stage

hi_manoj · Post by **hi_manoj** » Mon Aug 18, 2014 7:13 am

Hi,

I have to convert a oracle stored procedure to DataStage job. In procedure
their is merge statement.

I have tried with oracle connector stage (with insert and update mode) but the job failed with error " is of incorrect type. The required statement type is: INSERT .........."

Is there any direct way to execute that merge statement in oracle connector stage or enterprise stage. Or I have to convert it to upsert statement.

DataStage - 9.1, Oracle 10g

Regards
Manoj

chulett · Post by **chulett** » Mon Aug 18, 2014 7:32 am

Do the MERGE 'After SQL' once the work table has been loaded.

hi_manoj · Post by **hi_manoj** » Mon Aug 18, 2014 7:56 am

Thanks Craig,

I think I can not add the merge to the after SQL statement, because this my first statement in the procedure.

Regards

chulett · Post by **chulett** » Mon Aug 18, 2014 8:02 am

Meaning what, the table has already been loaded? Then just build a job with a single row into the Connector that does nothing and then run the MERGE after SQL. What else does the proc do besides that?

hi_manoj · Post by **hi_manoj** » Tue Aug 19, 2014 1:20 am

Hi,

I have asked some what similar question to my client,
I am looking for a dummy table where i can insert a record (which does not have any business impact) and as you have suggested I will execute the merge in the After SQl part and in the actual SQL i will execute the insert into dummy table.

Regards
Manoj

chulett · Post by **chulett** » Tue Aug 19, 2014 6:52 am

You can use any table, just craft some SQL that "does nothing"... say, update a record "where 1 = ?" and then pass in a 2 as the value. Select the 2 from DUAL.

priyadarshikunal · Post by **priyadarshikunal** » Wed Aug 20, 2014 7:14 am

I think merge statement does work in Oracle enterprise stage, seen that in earlier version, you can give that a shot as well.