Merge with extra condition [message #346390] |
Mon, 08 September 2008 07:18  |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
Is it possible to put an extra condition in merge statement when the condition falls into 'when not matched then' mode.
I have created the following merge statement.
merge into xml_1 t using app_contact_xml_1 o
on (t.app_name=o.app_name)
when matched then
update set t.APP_TYPE=o.APP_TYPE,
t.APP_OWNER_NAME=(select L1_NAME||';'||L2_NAME||';'||L3_NAME from app_contact_xml_1
where APP_NAME=o.app_name)
when not matched then
--Here i want to put some extra condition(if t.aap_name is not null then execute the insert statement)
insert(t.APP_NAME,t.APP_TYPE,t.APP_OWNER_NAME)
values(o.APP_NAME,o.APP_TYPE,o.L1_NAME||';'||o.L2_NAME||';'||o.L3_NAME)
Regards,
Pointers.
[Updated on: Mon, 08 September 2008 07:55] by Moderator Report message to a moderator
|
|
|
|
|
Re: Merge with extra condition [message #346410 is a reply to message #346390] |
Mon, 08 September 2008 08:14   |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Sorry Michel, I dint get your first point..
for second point, I am sorry it was a typo. It is actually app_name.
I am just concatinating source table different column values and updating for a single column in the destination table.
But, It is required to put some extra condition when not matched part. I tried by doing
merge into xml_1 t using app_contact_xml_1 o
on (t.app_name=o.app_name)
when matched then
update set t.APP_TYPE=o.APP_TYPE,
t.APP_OWNER_NAME=(select L1_NAME||';'||L2_NAME||';'||L3_NAME from app_contact_xml_1
where APP_NAME=o.app_name)
when not matched then
if(o.app_name is not null) then
insert(t.APP_NAME,t.APP_TYPE,t.APP_OWNER_NAME)
values(o.APP_NAME,o.APP_TYPE,o.L1_NAME||';'||o.L2_NAME||';'||o.L3_NAME)
end if;
I have included this whole in a procedure. But It is showing an error. So, is ther any way to put a condition in the 'when not matched part'.
|
|
|
Re: Merge with extra condition [message #346415 is a reply to message #346410] |
Mon, 08 September 2008 08:37   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | I dint get your first point..
|
You use "o.app_name" in the select, remove the select and use "o.<other columns>" directly.
Quote: | for second point, I am sorry it was a typo. It is actually app_name.
|
Use "(select * from app_contact_xml_1 where app_name is not null) o" instead of "app_contact_xml_1 o" in "using" clause.
Regards
Michel
[Edit: add missing end of last sentence]
[Updated on: Mon, 08 September 2008 10:09] Report message to a moderator
|
|
|
|
|