sql grouping function

I am using an odbc connection to an as400, I know the connection string is proper and working as all my other queries work without any problem.

why is it that this sql statement works:

select
  rdqty,
  alib.rcvshpdt.mbsnodesc,
  rdauthor,
  rdtitle,
  rdediton,
  ifnull(iecactno,actvty) as actlvl,
  class,
  ifnull(ieclp,lp) as cst
from
  alib.rcvshpdt
  left outer join
  alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno
  left outer join
  alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno
  and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc
where
  alib.rcvshpdt.rcvno='3930697'
order by
  actlvl asc

but as soon as I try to sum a quanity and group 开发者_如何学JAVAthe results as the following it fails.

select
  sum(rdqty) as quanity,
  alib.rcvshpdt.mbsnodesc,
  rdauthor,
  rdtitle,
  rdediton,
  ifnull(iecactno,actvty) as actlvl,
  class,
  ifnull(ieclp,lp) as cst
from
  alib.rcvshpdt
  left outer join
  alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno
  left outer join
  alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno
  and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc
where
  alib.rcvshpdt.rcvno='3930697'
group by
  alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, actlvl, class, cst
order by
  actlvl asc

---

As far as I know, you can't group by an alias name. Try changing the Group clause to use the calculations directly like the following:

alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, ifnull(iecactno,actvty), class, ifnull(ieclp,lp)