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