DSXchange

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

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.

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.

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.

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

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

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 ??

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

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.

Thank you ...