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.



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







Loyd Goodbar

Business Systems

BorgWarner Shared Services

662-473-5713