Home » SQL & PL/SQL » SQL & PL/SQL » Clean up multiple sum queries
Clean up multiple sum queries [message #308522] Mon, 24 March 2008 12:15 Go to next message
WAE Books
Messages: 2
Registered: March 2008
Junior Member
Is it possible to write a cleaner script combining the three different sums and one count function, thus not bogging down the database?

select o.ord_no, o.host_ord_type, o.host_prio, o.dest_id,

(select count(ord_line_no)
from load l
where l.ord_cntrl_no = o.ord_cntrl_no) as tot_lines,
(select sum(tot_wght)
from shipbol s
where s.ord_cntrl_no = o.ord_cntrl_no) as tot_weight,
(select sum(qty)
from load l
where l.ord_cntrl_no = o.ord_cntrl_no) as tot_units,
(select sum(act_ship_charge)
from shipbol s
where s.consol_cntrl_no = o.consol_cntrl_no) as tot_cost,

o.box_total, o.cust_ord_date, o.assgn_ship_date
from ordhead o, clsd_task_detail d, shipbol s
Re: Clean up multiple sum queries [message #308524 is a reply to message #308522] Mon, 24 March 2008 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you:
- say what do you expect this query gives?
- read and follow 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.
- post your Oracle version (4 decimals).

Regards
Michel
Re: Clean up multiple sum queries [message #308526 is a reply to message #308522] Mon, 24 March 2008 12:39 Go to previous messageGo to next message
WAE Books
Messages: 2
Registered: March 2008
Junior Member
That was a piece of the code. I have uploaded all of the code per SQL Formatter standard.
I am using a PL/SQL format to create a script that returns shipping information within a given date set.
The reason for the the multiple sum functions is that more than one container can be in one shipment order. I need add up all weight, cartons, lines, etc so my output gives all information in one neat line rather than mutliple lines in Excel when exported.
Re: Clean up multiple sum queries [message #308531 is a reply to message #308526] Mon, 24 March 2008 13:18 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have several lines with the same count, sum...?
Read the guide, it explains how to use tags to format your post.

Previous Topic: DBMS_OUTPUT.PUT_LINE in Trigger
Next Topic: ORA error to PL/SQL exception
Goto Forum:
  


Current Time: Sun Dec 04 06:30:00 CST 2016

Total time taken to generate the page: 0.09658 seconds