MERGE STATEMENT in datastage?
Moderators: chulett, rschirm, roy
MERGE STATEMENT in datastage?
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
MERGE STATEMENT in datastage?
Hi Chulett can you please help me in detail. I dont have subcription at this moment and I am unable to see your msg.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 ...
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
MERGE STATEMENT in datastage?
hiSainath.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.
I am not getting an ora code error Im only getting
Invalid SQL SELECT statement was entered
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 ??
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 ??