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
Business Systems
BorgWarner Shared Services
662-473-5713
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, June 26, 2008 11:20 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Rollup SQL function for v5r4?
The assumption that the 'Total' will be the last row without an ORDER
BY is false. No collation can be assumed in any result set, even a
union of two sets, without an ORDER BY. Any expected collation is
achieved only with an explicit request for ORDER BY. This remains the
case, irrespective the number of times the perceived-as-correct-ordering
outcome may have been and will be seen, when the ORDER BY was\is
omitted.
Following the lead of the given example, the following should suffice
to effect ordering of the /total/ after the /detail/ rows:
with
temp1 as (select part
, sum(...
)
,temp2 as (select item
, sum(...
)
select cast(x'40' as CHAR CCSID 37)
, item
, ...
union all
select cast(x'41' as CHAR CCSID 37)
, 'Total'
, ...
order by 1 ASC
Note: The hex values chosen, each appear as a /blank/ in the CCSID 37
[the space and the required space characters], so are chosen where the
intent is presumably a query as report. Those literal values could
prefix the /item/ and /'Total'/ using CONCAT, versus being a separate
field; allowing for better heading options using AS "heading". As a
displayable character, for example using the integers zero and one for
collating, the results are not as directly presentable as a report
because those digits will be presented.
Note: The /union/ was changed to UNION ALL in the above; presumably
that is really what is desired.
Note: The /required space/ character is variant in at least a Hebrew
language CCSID, but it presumably collates after a /space/ character in
all language sort sequences.
Regards, Chuck
lgoodbar@xxxxxxxxxxxxxx wrote:
I'm looking for a way to perform the equivalent of SQL's rollup
function or grouping sets in V5R4; showing detail and total in the
same query. The work around is to union the detail with a total line.
However, one must sort the entire union, not just one select
statement, resulting that the total line may not be at the bottom of
the results. If I leave the selections unsorted, the total will show
at the bottom, but the detail may not necessarily be sorted.
Not pressing, just a curiosity.
with temp as (
select part, sum(amount) as loadamount
from s9abs where account in ('4512','4513')
group by part
),
temp2 as (
select item, sum(absorptionlaborburdenallocation) as workamount,
loadamount,
sum(absorptionlaborburdenallocation) - loadamount as difference,
round(sum(absorptionlaborburdenallocation) - loadamount,2)
as diffround
from s9abswork
join temp on (item=part)
group by item, loadamount
)
select item,workamount,loadamount,difference,diffround
from temp2
union
select 'Total' as item,sum(workamount) as workamount
,sum(loadamount) as loadamount,sum(difference) as difference
,sum(diffround) as diffround
from temp2
midrange.com
