AW: AW: Using MIN in embedded SQL

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

I added the FETCH FIRST ROW ONLY clause only because the original post used
a SELECT ... INTO-Statement.
Instead of FIRST ROW ONLY, FIRST 10 ROWS ONLY or any other number of rows
can be specified

If it has to be used with embedded SQL and multiple rows could be returned,
I'd always declare a cursor and consider the level check.
Not sure if a CTE or a nested sub-select would be faster in this case.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Vernon Hamberg
Gesendet: Friday, 11.4 2014 17:48
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: AW: Using MIN in embedded SQL

Hi Birgitta

Yes, that was a solution posted earlier.

It gets around the issue of multiple results when MIN value is the same
- I had offered the CTE/NTE solution, because I usually don't like to trust
"first row only"

Regards
Vern

On 4/10/2014 11:58 PM, Birgitta Hauser wrote:
> I think this requirement can be done without CTE and/or sub-select:
>
> Select  Ordyr, count(*) as Orders
>     From MacFile
>     Where Ordyr between 2003 and 2013
>     Group By Ordyr
>     Order By Orders
> Fetch First Row Only;
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." 
> (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok) 
> "What is worse than training your staff and losing them? Not training 
> them and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael Smith
> Gesendet: Thursday, 10.4 2014 22:43
> An: RPG programming on the IBM i (AS/400 and iSeries)
> Betreff: RE: Using MIN in embedded SQL
>
> Thanks,  I will try this.
>
> with years as (
>    select ordyr, count(*) as orders
>    from macfile
>    where ordyr between 2003 and 2013
>    group by ordyr)
> select year, orders
> from years
> where orders = (select min(orders) from years)
>
> --
> 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.

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