GouthamAnand
asked on
Use Query as table in Merge Statement
Hi,
I am using merge statemnet 30 times for 30 different destination tables like below
MERGE INTO tab2 t2-- this table name changes according to the parameter in using clause
USING (Query Here with respective parameter for the above table) t2
on (t1.a = t2.c and t1.b = t2.d)
WHEN MATCHED THEN
UPDATE
SET t2.flag = t1.flag
WHEN NOT MATCHED THEN
INSERT
VALUES (t1.a, t1.b, t1.flag);
Qry in using clause is something like below
SELECT c,d, flag
FROM mytable1 s,
mytable2 sa,
WHERE s.key_data_name = <<parameter here>> --need to pass parameter
and s.p_code=sa.p_code
ORDER BY s.created_date;--need to use this order
I would like to write this big query "one time" in a function and passing the parameter every time and fetch the data to use in the USING clause of the Merge statement.
So that I can avoid "to write" this query in every USING clause of MERGE statement
Can you please suggest me how can i achieve this?
Thanks in advance.
I am using merge statemnet 30 times for 30 different destination tables like below
MERGE INTO tab2 t2-- this table name changes according to the parameter in using clause
USING (Query Here with respective parameter for the above table) t2
on (t1.a = t2.c and t1.b = t2.d)
WHEN MATCHED THEN
UPDATE
SET t2.flag = t1.flag
WHEN NOT MATCHED THEN
INSERT
VALUES (t1.a, t1.b, t1.flag);
Qry in using clause is something like below
SELECT c,d, flag
FROM mytable1 s,
mytable2 sa,
WHERE s.key_data_name = <<parameter here>> --need to pass parameter
and s.p_code=sa.p_code
ORDER BY s.created_date;--need to use this order
I would like to write this big query "one time" in a function and passing the parameter every time and fetch the data to use in the USING clause of the Merge statement.
So that I can avoid "to write" this query in every USING clause of MERGE statement
Can you please suggest me how can i achieve this?
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much.
Does there exist a list of table names and their key data fields?
ASKER
Yes. there are list of table names and their key data fields.
I thought returning merge statement through function is possible. But.
It is not possible.
Because it is not only the table name change but their column names also changes. So dynamic merge is not possible...
I thought returning merge statement through function is possible. But.
It is not possible.
Because it is not only the table name change but their column names also changes. So dynamic merge is not possible...
If always all columns of for example the primairy key are involved, you can expand your dynamic build by using the USER_IND_COLUMNS view
ASKER
Thank you. Yes. I could achieve now by expanding dynamic sql.
Also there is one other thing WITH clause which you can google around for syntax/more information and try to see if that can suit you but WITH in general is for aliasing a reusable query which is referred to more than once in the same query with the ailas without rewriting the whole query mutlple times in that main query.