Re: Using MIN in embedded SQL

Another option or 2 can use a table expression, either correlated or nested. I'm not testing these - see if they work - I like these, because I feel more in control of things than if I use "fetch first row only". That's just me!

Correlated table expression -

with xx as (SELECT count(*) as yearcount FROM macfile WHERE ordyr between 2003 and 2013 GROUP BY ordyr)
select min(yearcount) into :count from xx

Nested table expression -

select min(yearcount) into :count from (SELECT count(*) as yearcount FROM macfile WHERE ordyr between 2003 and 2013 GROUP BY ordyr)

HTH
Vern

On 4/10/2014 11:30 AM, Michael Smith wrote:

Great.

exec sql select ordyr, count(*) into :Year, :Count from macfile
where ordyr between 2003 and 2013 group by ordyr
order by count(*) fetch first row only;

Thank you.

Michael