Home » SQL & PL/SQL » SQL & PL/SQL » Execution of multiple statements under WHEN NOT MATCHED clause in MERGE (Oracle 10g R2)
Execution of multiple statements under WHEN NOT MATCHED clause in MERGE [message #364853] Wed, 10 December 2008 05:00 Go to next message
manyal
Messages: 77
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
manyal
Messages: 77
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
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 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use a sequence.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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
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 Go to previous messageGo to next message
manyal
Messages: 77
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

Regards
Michel
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 Go to previous message
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
Previous Topic: Statistical Computations
Next Topic: Storage space allocation for user
Goto Forum:
  


Current Time: Mon Dec 05 21:00:19 CST 2016

Total time taken to generate the page: 0.13269 seconds