RE: Rollup SQL function for v5r4?

[<lgoodbar@xxxxxxxxxxxxxx>]

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