RE: Using MIN in embedded SQL

[Michael Smith <msmith@xxxxxxxxxxxxxx>]

> From my original email:
" I need to be able to set an RPG program variable with the year results(2003).  Is this possible, if so, please provide a coding example."

Thanks for your input.

Michael 


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Thursday, April 10, 2014 5:04 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Using MIN in embedded SQL

Ah, Michael - you switched up your requirements on us!!!

I interpreted it as you just wanted the minimum value, not the year that had the minimum value - I think Buck gave you a direction to look.

In both his and my examples, the idea is to get a "temporary" file with the summary information, then do some min-ing or max-ing on that.

It's a fairly general technique.

Vern

On 4/10/2014 2:08 PM, Michael Smith wrote:
> Thanks for the suggestion,
>
> "select min(yearcount) into :count from (SELECT count(*) as yearcount FROM macfile WHERE ordyr between 2003 and 2013 GROUP BY ordyr)"
>
> Will this code provide me the year of the "min(yearcount)"... which is the value that I am after, as in this sample data below; I am looking for 2003 to be populated in my RPG variable.
> 2,003             225
> 2,004             392
> 2,005             447
> 2,006             248
> 2,007             379
> 2,008             544
> 2,009             262
> 2,010             568
> 2,011             532
> 2,012             288
> 2,013             314
>
> Michael

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