Re: embedded SQL: group by rollup()

[Michael Schutte <mschutte369@xxxxxxxxx>]

are L1, L2 and L3 fields in FILEA?

If so add them to the Select and group by.

The way you currently have this coded, it will not work. You are trying to
fetch a single result set that has two fields into only one host variable.
You would need to add a fetch first row only to limit result set being
returned if you want to keep it that way. Or do a declare cursor and read
through the cursor.


Declare c1 cursor for
select LOC, L1, L2, L3, SUM(ORD)
  from FILEA
where ITM = :wItem
  group by rollup (LOC,L1,L2,L3)
 order by LOC

You've put a coalesce on field ORD.  The only reason for needing that would
be if you expect there to be null values in the file.

In which case all you need it
SUM(COALESCE(ORD,0))





On Mon, Jan 18, 2016 at 3:50 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

> LOC can be "L1" "L2" or "L3"
>
> This compiles:
>          exec sql Select LOC,
>            SUM(COALESCE(Sum(ORD), 0)) into :wORD
>            from FILEA
>            where ITM = :wITM
>            group by rollup(LOC)
>            order by LOC
>
> but now what? How do I get the rolled up totals for L1.wORD, L2.wORD, and
> L3.ORD?
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.