Re: Rollup SQL function for v5r4?

[CRPence <CRPbottle@xxxxxxxxx>]

  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