| Clean up multiple sum queries [message #308522] |
Mon, 24 March 2008 12:15  |
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 #308526 is a reply to message #308522] |
Mon, 24 March 2008 12:39  |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
