> Oh, the error message is:
> ISC ERROR MESSAGE:
> Dynamic SQL Error
> SQL error code = -104
> Invalid expression in the select list (not contained in either an
> aggregate function or the GROUP BY clause)
>
> version: 2.0.3.12981

If you use an aggregate or group by, you need to use an aggregate
or group by on all resulting columns.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> Thanks
>
>
>
> --- In firebird-support@yahoogroups.com, "Martijn Tonies"
> <m.tonies@...> wrote:
> >
> > Hi,
> >
> > > We use the following query in Interbase and would to make it work with
> > > Firebird also:
> > > SELECT prw_WeekEnding, f_RoundReal(SUM(pwr_TotalPay), 2) AS Pay,
> > > (SELECT SUM(PW.pwr_TotalWeek) FROM PayrollWeekResults PW
> > > WHERE PW.prw_WeekEnding = PWR.prw_WeekEnding AND
> > > PW.pwr_LineNum IN (1,2,3,5) AND PW.Employee_ID =
> > > PWR.Employee_ID) AS Hours
> > > FROM PayrollWeekResults PWR
> > > WHERE prw_WeekEnding >= :BeginDate AND prw_WeekEnding < :EndDate +
> 1 AND
> > > Employee_ID = :EmpNum
> > > GROUP BY prw_WeekEnding
> > > ORDER BY prw_WeekEnding
> > >
> > > Any suggestions on this, since Firebird does not allow the subquery in
> > > the select clause?
> >
> > Yes it does.
> >
> > >I was thinking maybe a self join, but couldn't
> > > quite work it out right.
> >
> > What's the error message you're getting?
> >
> > And the Firebird version?
> >
> > Martijn Tonies
> > Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB,
> Oracle &
> > MS SQL Server
> > Upscene Productions
> > http://www.upscene.com
> > My thoughts:
> > http://blog.upscene.com/martijn/
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.com
> >
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>