Home » SQL & PL/SQL » SQL & PL/SQL » Eliminate Duplicate subqueries in MERGE INTO
Eliminate Duplicate subqueries in MERGE INTO [message #301959] Fri, 22 February 2008 08:31 Go to next message
jah4ksu
Messages: 1
Registered: February 2008
Junior Member
I'm a newbie with MERGE INTO functionality and am modifying an existing MERGE INTO statement.
It currently uses a sub Select statment to populate summary column ytd_booked. I need to use the exact same sub Select to populate 2 additional columns, qtd_booked and ptd_booked.
Is there a way to eliminate the duplicate subqueries that I've added in the example below?
One option would be to use a Cursor and Loop through records doing a Update each time, but I need to retain the MERGE INTO statement.

Here's how I currently have it:
MERGE INTO project_sum pj
    USING
        (
        SELECT DISTINCT project_id
          FROM invoice
        UNION
         SELECT DISTINCT project_id
           FROM receipts
        UNION
        SELECT DISTINCT project_id
          FROM wip
        UNION
        SELECT DISTINCT project_id
          FROM expenditure
        ) eiw
    ON (pj.project_id = eiw.project_id)
    WHEN MATCHED THEN
        UPDATE
           SET pj.total_booked = pj.total_booked + 100000,
               pj.ytd_booked = pj.ytd_booked + NVL((SELECT SUM(a.revenue) 
                                                      FROM expenditure a 
                                                     WHERE a.project_id = eiw.project_id),0),
               pj.ptd_booked = pj.ptd_booked + NVL((SELECT SUM(a.revenue) 
                                                      FROM expenditure a 
                                                     WHERE a.project_id = eiw.project_id),0),
               pj.qtd_booked = pj.qtd_booked + NVL((SELECT SUM(a.revenue) 
                                                      FROM expenditure a 
                                                     WHERE a.project_id = eiw.project_id),0),
               pj.last_update_date = SYSDATE
        WHEN NOT MATCHED THEN
        INSERT
           (pj.project_id,
            pj.total_booked,
            pj.ytd_booked, 
            pj.ptd_booked, 
            pj.qtd_booked, 
            pj.last_update_date)
        VALUES
           (eiw.project_id,
            100000,
            NVL((SELECT SUM(a.revenue) 
                   FROM expenditure a 
                  WHERE a.project_id = eiw.project_id),0),
            NVL((SELECT SUM(a.revenue) 
                   FROM expenditure a 
                  WHERE a.project_id = eiw.project_id),0),
            NVL((SELECT SUM(a.revenue) 
                   FROM expenditure a 
                  WHERE a.project_id = eiw.project_id),0),
      		SYSDATE); 

Any suggestions would be appreciated.
Justin

[Updated on: Fri, 22 February 2008 08:39] by Moderator

Report message to a moderator

Re: Eliminate Duplicate subqueries in MERGE INTO [message #301961 is a reply to message #301959] Fri, 22 February 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the subselect in your source and not in the update/insert clause.

Also put your code between [code] and [/code] tags as I did it for you.

Regards
Michel

[Edit: bad answer replaced]

[Updated on: Fri, 22 February 2008 08:48]

Report message to a moderator

Re: Eliminate Duplicate subqueries in MERGE INTO [message #301966 is a reply to message #301959] Fri, 22 February 2008 09:02 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum.

I will give you three clues how to go about doing it.

a) Use nested select
b) Use Outer join
c) Use group by

Last but not least you don't need distinct. Union will take care of it.

Regards

Raj

P.S : Apologies @Michael I didn't see your post after I clicked on submit I realised it.

[Updated on: Fri, 22 February 2008 09:03]

Report message to a moderator

Previous Topic: create table with non default date format
Next Topic: how to find out that how many times trigger fired today
Goto Forum:
  


Current Time: Mon Dec 05 10:52:56 CST 2016

Total time taken to generate the page: 0.14350 seconds