MERGE STATEMENT in datastage?

karry450 · Post by **karry450** » Wed Mar 04, 2009 7:37 am

Hi

Can anyone help me if we can use merge statement in userdefined sql tab in datastage server jobs?

Im getting this error

Invalid SQL SELECT statement was entered

but the same MERGE statement is getting executed at database end.

Thanks
Karthik

chulett · Post by **chulett** » Wed Mar 04, 2009 7:41 am

Yes, you can but it does get a little 'tricky' and you need to understand all of the rules the stage enforces. Since you (more than likely) don't have a good way to bind the columns in the stage unless you're doing deletes in the merge as well - I'm assuming we're talking OCI stage here, yes? - I found it easier to do the MERGE either in the before or after sql tabs and put a "do nothing" sql in the main area, one that binds all input columns (just really need one) so the stage is happy.

karry450 · Post by **karry450** » Wed Mar 04, 2009 7:48 am

chulett wrote:Yes, you can but it does get a little 'tricky' and you need to understand all of the rules the stage enforces. Since you (more than likely) don't have a good way to bind the columns in the stage unles ...

Hi Chulett can you please help me in detail. I dont have subcription at this moment and I am unable to see your msg.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Mar 04, 2009 7:58 am

Craig,

I also do not have Premium but assume you are referring to placeholders for binding variables.

Karthik,

Can you provide the full error with associated ora code.

karry450 · Post by **karry450** » Wed Mar 04, 2009 8:29 am

Sainath.Srinivasan wrote:Craig,

I also do not have Premium but assume you are referring to placeholders for binding variables.

Karthik,

Can you provide the full error with associated ora code.

hi

I am not getting an ora code error Im only getting

Invalid SQL SELECT statement was entered

chulett · Post by **chulett** » Wed Mar 04, 2009 9:18 am

Bind parameters are definitely part of it. Post your sql and let us know what stage you are using.

raju_chvr · Post by **raju_chvr** » Wed Aug 25, 2010 3:29 pm

Chulett,

Below is the SQL:

MERGE INTO DW_STAR_MDLN.DIM_PROGRAM_TEST A
USING DW_STG_MDLN2.STG_MCD_PROGRAM B
ON (A.PROGRAM_NAME = B.PROGRAM_SHORT_NAME AND A.PROGRAM_TYPE = B.PROGRAM_TYPE)
WHEN MATCHED THEN
UPDATE SET A.LOAD_CONTROL_SKEY =:3,
A.EVENT_TYPE =:4,
A.PROCESS_FLG =:5,
A.LOAD_TIMESTAMP =TO_TIMESTAMP(:6, 'YYYY-MM-DD HH24:MI:SS.FF3'),
A.LOAD_CONTROL_RUN_SKEY=:7
WHEN NOT MATCHED THEN
INSERT INTO
(
A.PROGRAM_SKEY,
A.PROGRAM_NAME,
A.PROGRAM_TYPE,
A.LOAD_CONTROL_SKEY,
A.EVENT_TYPE,
B.PROCESS_FLG,
A.LOAD_TIMESTAMP,
A.LOAD_CONTROL_RUN_SKEY
)
VALUES
(
DW_STAR_MDLN.PROGRAM_SKEY_SEQ.NEXTVAL,
:1,
:2,
:3,
:4,
:5,
TO_TIMESTAMP(:6, 'YYYY-MM-DD HH24:MI:SS.FF3'),
:7
)

The above we are using the 'SQL' tab and not in 'Before SQL' or 'After SQL'. The more I think about it, probably using Merge statement is a wrong design here as Merge Statement is mainly designed for Merging 2 tables and not run for each and every record in your DataStage job.

Any thoughts ??

anbu · Post by **anbu** » Wed Aug 25, 2010 3:34 pm

You are right. Merge statement is merging two tables. In your case you use either Insert Then Update/Update Then Insert.

chulett · Post by **chulett** » Wed Aug 25, 2010 3:42 pm

Re-read this thread. You are correct in that MERGE combines two tables, not one table and an input stream, hence the suggestion to do it before/after and trigger it with a single row into the stage.

raju_chvr · Post by **raju_chvr** » Wed Aug 25, 2010 7:10 pm

Thank you ...