Home » SQL & PL/SQL » SQL & PL/SQL » finding sum from inner query (Oracle 9i)
finding sum from inner query [message #428334] Tue, 27 October 2009 23:41 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
Please advice me on my query which seems to be running slow as I have reused the same things twice.

CREATE TABLE TEST_PO
(
  PO_PENDING        NUMBER,
  PO_NUMBER         VARCHAR2(20 BYTE),
  LINE_LOCATION_ID  NUMBER
)

INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
4700, '200900104446', 306958); 
INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
2500, '200900104446', 306957); 
INSERT INTO TEST ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
2500, '200900104446', 306957); 
INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
38000, '200900104522', 307163); 
INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
38000, '200900104522', 307163); 
INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
38000, '200900104522', 307163); 
INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
40000, '200900104545', 307195); 
INSERT INTO TEST_PO ( PO_PENDING, PO_NUMBER, LINE_LOCATION_ID ) VALUES ( 
40000, '200900104545', 307193); 
COMMIT;


SELECT * FROM Test_PO;

PO_PENDING PO_NUMBER            LINE_LOCATION_ID
---------- -------------------- ----------------
      4700 200900104446                   306958
      2500 200900104446                   306957
      2500 200900104446                   306957
     38000 200900104522                   307163
     38000 200900104522                   307163
     38000 200900104522                   307163
     40000 200900104545                   307195
     40000 200900104545                   307193

8 rows selected.


I have to find the SUM(PO_PENDING) for a particular PO_NUMBER. There should be one record per PO_NUMBER.
The SUM of po_pending has to be calculated as :-
For a particular PO_NUMBER, there might be one or more Line_Location_Id. The PO_Pending for one Line_location_id
would appear as repeating for a PO_NUMBER. In that case only one value has to be taken. But when the
line_location_id is different for a particular PO_NUMBER then their Sum has to be taken.

For PO_NUMBER '200900104446', The line_location_id '306957' is repeating so only one value '2500' would be taken
in account for summation of this po_number. So total po_pending for this po_number would be (4700+2500) = 7200.

For PO_NUMBER '200900104522' there is one line_location_id '307163' repeating so only one value '38000' should be taken.

For PO_NUMBER '200900104545', the two line_location_id is different ,so sum od po_pending should be taken and
so sum(po_pending) would be (40000+40000) = 80000. Hence the below result:-

SUM(PO_PENDING) PO_NUMBER    
 7200            200900104446 
38000            200900104522
80000            200900104545

I have got the result using the below script :-

select sum(po_pending),PO_number from(
select distinct PO_PENDING, PO_NUMBER, line_location_id from test_po
 )
 group by po_number


But this doesn not look good as per performance as I have to use this script as a part of a big script as temp.

The table test_po is only a simulation of my inner script which I have used in the big query to understand better.
The result coming is correct from the big query too. But its working slow.
Please advice as how can I change the whole script.

INSERT INTO smp_po_details
   SELECT DISTINCT p.po_number, p.po_date, TO_NUMBER (p.po_quantity),
                   TO_NUMBER (p.po_rate), p.po_status,
                   TO_NUMBER (p.po_totalamount), temp.po_pending,
                   TO_NUMBER (v.vendor_location_item_id), p.po_amendement
              FROM parking_shalina_erp_data p,
                   smp_vendor_location_item v,
                   (SELECT   SUM (po_pending) po_pending, po_number
                        FROM (SELECT DISTINCT p.po_pending po_pending,
                                              p.po_number, p.line_location_id
                                         FROM parking_shalina_erp_data p,
                                              smp_vendor_location_item v
                                        WHERE p.line_location_id IS NOT NULL
                                          AND p.po_number IS NOT NULL
                                          AND p.po_need_by_date IS NOT NULL
                                          AND v.vendor_id =
                                                     TO_NUMBER (p.vendor_code)
                                          AND v.itemcode = p.item_code)
                    GROUP BY po_number) temp
             WHERE p.po_number IS NOT NULL
               AND v.vendor_id = p.vendor_code
               AND v.itemcode = p.item_code
               AND temp.po_number = p.po_number;



Regards,
Mahi
Re: finding sum from inner query [message #428347 is a reply to message #428334] Wed, 28 October 2009 00:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
That is the answer I would have gone with. It is what it is. At some point you reach an irreducible minimum amount of work needed to get the right answer. I think you are there.

Good luck, Kevin
Re: finding sum from inner query [message #428357 is a reply to message #428334] Wed, 28 October 2009 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The question is why do you need a DISTINCT?

Regards
Michel
Re: finding sum from inner query [message #428383 is a reply to message #428357] Wed, 28 October 2009 03:03 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
The data is coming from a parking table. And the columns which I am selecting from parking table might have duplicacy when only the selected columns taken together. As a whole there is no duplicacy in parking table but if I selected only some columns as required there might be some duplicacy so taking DISTINCT.
Re: finding sum from inner query [message #428384 is a reply to message #428383] Wed, 28 October 2009 03:12 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
see if you can remove smp_vendor_location_item v table from your inner query
Re: finding sum from inner query [message #428387 is a reply to message #428383] Wed, 28 October 2009 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have a model problem. Instead of repeating the same data in ONE parking table, you should investigate to use SEVERAL parking tables in 3NF.
This will in the same time solve your performances problem.

Regards
Michel
Re: finding sum from inner query [message #428394 is a reply to message #428387] Wed, 28 October 2009 03:45 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
Is there no other solution, as I am working on maintenance and table structure can't be changed at this point of time. Sad
Re: finding sum from inner query [message #428397 is a reply to message #428394] Wed, 28 October 2009 04:01 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you can't change you are stuck to DISTINCT and as Kevin said: "At some point you reach an irreducible minimum amount of work needed to get the right answer.".

Regards
Michel
Previous Topic: Error after running control file in toad
Next Topic: SQL code
Goto Forum:
  


Current Time: Sun Sep 25 21:23:43 CDT 2016

Total time taken to generate the page: 0.04823 seconds