1) except that DB doesn't know ahead of time they're unique, so it'll do
(now extraneous) DISTINCT processing anyway, incurring overhead
unnecessarily
V6R1 does provide the enhancements you mentioned. See:
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzg
roupbyclause.htm?tocNode=int_224745
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: RE: Rollup SQL function for v5r4?
Thanks Chuck and Elvis.
1. Union vs. union all is fine in this case, as the detail is already
unique by item.
2. Both of the solutions presented require what Elvis called a helper
column for collating. Adding such a column allows for order by 1,2 which
is the desired result.
3. I agree with the sorting explanation. Whether it always holds true,
I've found that multiple unioned statements are generally presented in
top-down order when the ORDER BY is omitted.
4. The equivalent (untested) statement in SQL server syntax is something
like
Select coalesce(item,'Total'), sum(workamount), sum(loadamount),
sum(difference), sum(diffround)
From temp2
Group by item with rollup
This will subtotal and sort by item number. In effect, SQL server sorts
data by each of the columns in the rollup. One could also add ORDER BY
GROUPING(ITEM),ITEM if desired. The function grouping(column) returns 0
if a detail row, or 1 if a grouped row. When grouping(item) is 1, that
is the summarizing group, and the item column is null.
I have not looked but perhaps V6R1 supports WITH ROLLUP or the DB2
syntax ROLLUP() and GROUPING SETS() syntax.
Loyd Goodbar
midrange.com
