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