Home » SQL & PL/SQL » SQL & PL/SQL » merge statment (Oracle 10g)
merge statment [message #356090] Wed, 29 October 2008 08:28 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have the following code to merge at 3 different cases.

Can you please suggest what needs to be done to minimize the following code into a single merge statment or anyother way to do it.
the 3 merge have similar code except this line
USING (select * from user_jobs where failures>=1)  


--merge1 for failures>=1

     MERGE INTO alert_failed_jobs_pointers t
        USING (select * from user_jobs where failures>=1) o
           ON (t.job = o.job)
      WHEN MATCHED THEN
         UPDATE
            SET t.broken = o.broken, t.failures = o.failures
      WHEN NOT MATCHED THEN
         INSERT (t.job, t.broken, t.failures, t.mail_sent)
         VALUES (o.job, o.broken, o.failures, 1);


  --merge2 for failures=0      
         MERGE INTO alert_failed_jobs_pointers t
        USING (select * from user_jobs where failures=0) o
           ON (t.job = o.job)
      WHEN MATCHED THEN
         UPDATE
            SET t.broken = o.broken, t.failures = o.failures
      WHEN NOT MATCHED THEN
         INSERT (t.job, t.broken, t.failures, t.mail_sent)
         VALUES (o.job, o.broken, o.failures, 0);


    --merge3 for failures is  null     
         MERGE INTO alert_failed_jobs_pointers t
        USING (select * from user_jobs where failures is null) o
           ON (t.job = o.job)
      WHEN MATCHED THEN
         UPDATE
            SET t.broken = o.broken, t.failures = o.failures
      WHEN NOT MATCHED THEN
         INSERT (t.job, t.broken, t.failures, t.mail_sent)
         VALUES (o.job, o.broken, o.failures, null);

Regards,
Pointers.

Re: merge statment [message #356097 is a reply to message #356090] Wed, 29 October 2008 08:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can a single o.job occur in more than one of the merge conditions?

If not, then I'd say a simple x <=1 OR x=0 OR x is null would do..
Re: merge statment [message #356105 is a reply to message #356090] Wed, 29 October 2008 09:46 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Frank,
there is no o.job which occur in more than one job....
But the fact here it is, we need to insert new row that changes according to the
USING (select * from user_jobs where failures>=1) ..
line
the last line in each merge statement dipicts this which changes in each merge statment..

e.g
INSERT (t.job, t.broken, t.failures, t.mail_sent)
         VALUES (o.job, o.broken, o.failures, 1);


Regards,
Pointers.
Re: merge statment [message #356116 is a reply to message #356105] Wed, 29 October 2008 11:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This is assuming job is unique otherwise merge will fail.

MERGE INTO alert_failed_jobs_pointers t
        USING (select u.*, case when failures >= 1 then 1
                                else failures end l_failures from user_jobs u) o
           ON (t.job = o.job)
      WHEN MATCHED THEN
         UPDATE
            SET t.broken = o.broken, t.failures = o.failures
      WHEN NOT MATCHED THEN
         INSERT (t.job, t.broken, t.failures, t.mail_sent)
         VALUES (o.job, o.broken, o.failures, l_failures);


Hope this helps.

Regards

Raj

[Updated on: Wed, 29 October 2008 11:18]

Report message to a moderator

Re: merge statment [message #356321 is a reply to message #356090] Thu, 30 October 2008 05:29 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
The solution is really nice..

Thanks Rajaram..

Regards,
Pointers.
Previous Topic: decode syntax in Stored Procedure
Next Topic: Can't figure out the problem, can anyone help
Goto Forum:
  


Current Time: Sun Dec 04 08:59:07 CST 2016

Total time taken to generate the page: 0.13888 seconds