OraFAQ Forum: SQL & PL/SQL » Execution of multiple statements under WHEN NOT MATCHED clause in MERGE

Home » SQL & PL/SQL » SQL & PL/SQL » Execution of multiple statements under WHEN NOT MATCHED clause in MERGE (Oracle 10g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #364853]

Wed, 10 December 2008 05:00

manyal
Messages: 84
Registered: March 2005

Member

Dear Friends

I want to execute multiple statements under "WHEN NOT MATCHED" portion of MERGE statement as possible in "WHEN MATCHED".

Pl. let me know the procedure of the same is exists.

Regards

MANISH MANYAL

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #364854 is a reply to message #364853]

Wed, 10 December 2008 05:03

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is not possible.
But if you posted your actual problem (with a test case and in a formatted way) maybe you could help more and find a solution.

Regards
Michel

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #364866 is a reply to message #364854]

Wed, 10 December 2008 05:31

manyal
Messages: 84
Registered: March 2005

Member

I have a table with following structure:

TABLE1
------
empno number(5) PK
srno number(2) PK
amount number(5)

TABLE2
------
empno number(5) PK
dcode number(3) PK
amount number(5)

I am trying the merge the two table based on empno. Whenever there is a insert in TABLE1 then the srno should automatically be incremented.

I tried using a function but the "MUTATING" error is generated.

Any help?????????

MANISH MANYAL

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #364867 is a reply to message #364853]

Wed, 10 December 2008 05:32

cookiemonster
Messages: 13973
Registered: September 2008
Location: Rainy Manchester

Senior Member

Use a sequence.

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #364872 is a reply to message #364866]

Wed, 10 December 2008 05:46

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I am trying the merge the two table based on empno. Whenever there is a insert in TABLE1 then the srno should automatically be incremented.

What is the relation between the 2?

Post a test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #365099 is a reply to message #364867]

Wed, 10 December 2008 23:40

manyal
Messages: 84
Registered: March 2005

Member

Hi

Actually I missed one field in TABLE1. Field is DCODE number(3).

The tables are related on empno and dcode.

TABLE1 is a master table and TABLE2 is detail table where the monthly deducted amount is stored and TABLE1 hold the total amount deducted.

The SRNO field in TABLE1 is reset to 1 whenever the empno changes. Example for empno 100 srno will start from 1 to n again for empno 200 srno will start from 1 to m.

The use of sequence is not possible in this case.

Whenever DCODE is not found in TABLE1 then the same is inserted with incremented SRNO for that CCNO otherwise the AMT field is added with the AMT field in TABLE2.

The MERGE statement is used in PL/SQL.

Hope now I am clear.

Regards

MANISH MANYAL

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #365110 is a reply to message #365099]

Thu, 11 December 2008 00:31

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at ROW_NUMBER function.

Regards
Michel

Report message to a moderator

Re: Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #365238 is a reply to message #365099]

Thu, 11 December 2008 03:15

dr.s.raghunathan
Messages: 540
Registered: February 2008

Senior Member

if it is related to earlier or last inserted record of slno then you shall write a trigger before insert using new old. maybe it is round about the way of achieving the desired results. rownumber is good still trigger still better since rownumber depends on other parameters too. ofcourse i had failure on using rownumber but the reasons are yet to be analysed.
yours
dr.s.raghunathan

Report message to a moderator

Previous Topic:	Statistical Computations
Next Topic:	Storage space allocation for user

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 16 22:05:01 CST 2025