Subject Can't Have subquery in select in Firebird
Author mthendley
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? I was thinking maybe a self join, but couldn't
quite work it out right.