RE: Rollup SQL function for v5r4?

Loyd, I haven't examined your query in detail, but provided you're happy
with the UNION solution, your challenge seems to be the ordering of the
result set.
I think you can address that challenge by appending an on-the-fly helper
column to your projection clauses and then using that to aid in the
ordering. Let's call that column 'orderingAssistant'. Here's an example of
what I'm thinking of:

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, 'A' as
orderingAssistant from temp2
union
select 'Total' as item,sum(workamount) as workamount,sum(loadamount) as
loadamount,sum(difference) as difference,sum(diffround) as diffround,
'B' as orderingAssistant
from temp2
ORDER BY orderingAssistant,....

The ellipsis stand for the ordering columns you're using presently, but
since they're secondary in this example, the 'Total' would sort to the
bottom of the result set regardless of what they are.

BTW, consider using UNION ALL if you don't need to weed out duplicate rows
via UNION's DISTINCT processing nature (ALL qualifier tells it not to
perform DISTINCT processing).

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Rollup SQL function for v5r4?

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.