Re: RPG embedded SQL GROUP BY with cursor

One more thing...

You should never use SELECT * in production code.

By doing so, you're tying the code to the file layout...just like RPG's
RLA. Always explicitly list the fields you want. Even if today you want
all of them. That way tomorrow, if somebody adds a column, you code
continues to work unchanged.

Same thing applies to INSERT. Always do INSERT into mytable (FLD1, FLD2,
FLD3, <...>) VALUES(<..>). As long as new columns are added to the table
with an appropriate default, your code continues to work.

There are a couple of exceptions that prove the rule. Namely, in data
replication processes.

But as a rule, avoid SELECT * in production code.

This applies to production views also.

Charles



On Thu, Jan 14, 2016 at 8:58 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

"order by item and then by date, totaled by day"

So you know you want an ORDER BY...along with an aggregate function, SUM()
most likely..

You don't mention what field(s) you want "totaled by day", but you do have
to explicitly list them and specify which aggregate to use on each field.

select item, date, sum(fld?), sum(fld?), count(*), <...>
from filea
where item = :wSqlItem
group by item, date
order by item, date

Aggregate functions and GROUP BY are each half of the solution. You don't
usually use one without the other.

The exception being, if you want a single result row
returned consisting solely of aggregations Then you don't need a GROUP BY
select count(*), sum(fld)
from fiela

Is perfectly valid.

All the above is basic GROUP BY and aggregation. But note that in 6.1,
IBM added some awfully powerful enhancements: GROUPING SETS, CUBE, ROLLUP.
Some examples: http://www.itjungle.com/fhg/fhg031109-story02.html

Charles



On Wed, Jan 13, 2016 at 5:48 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

My intention is to list transaction rows in order by item and then by
date, totaled by day. I would like to use a cursor. Every variation I can
think of fails. Here is where I am at:

exec sql declare C1 cursor for // for Days
select *
from FILEA
where ITEM = :wsqlITEM
group by ITEM, DATE
order by ITEM, DATE;

Without the "group by" it works. The result is a tad too many pages, but
it works.


--
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.