Hans,

thanks for your reply. I'd say, your procedure must be faster, but:

execution time your proc: 46,0274 sec
execution time my proc : 32,0028 sec

I can't believe it!

BTW, I haven't got any reply to my previous question: do I need
compound index, when I do
SELECT WHERE Field1 = something AND Field2 = something?

Who knows it, please?

Regards
Richard

--- In ib-support@y..., hans@h... wrote:
> Maybe this goes faster ?
>
> CREATE PROCEDURE MonthTable ( TheYear SMALLINT, Dept_No SMALLINT )
> RETURNS ( Emp_No INTEGER, Name VARCHAR(50), M01 DECIMAL (9,2),
> M02 DECIMAL (9,2), M03 DECIMAL (9,2),
> M04 DECIMAL (9,2), M05 DECIMAL (9,2),
> M06 DECIMAL (9,2), M07 DECIMAL (9,2),
> M08 DECIMAL (9,2), M09 DECIMAL (9,2),
> M10 DECIMAL (9,2), M11 DECIMAL (9,2),
> M12 DECIMAL (9,2) )
> AS
> declare variable myEmp_No integer;
> BEGIN
> for select distinct Emp_No from Pers
> where
> order by Emp_no
> into :myEmp_No
> do
> begin
> mo1 = 0;
> mo2 = 0;
> ....
> mo12 = 0;
>
> FOR SELECT Emp_No, NAME1 || ' ' || NAME2, Work_Hours
> FROM Pers
> WHERE Dept_No = :Dept_No
>
> order by Emp_No,Work_Day
> where and Emp_No = myEmp_No
> and extract(year of Work_Day) = :TheYear // prob want year
limit
>
> INTO :Emp_No, :Name DO
>
> BEGIN
> myMonth = extract(month of Work_Day);
> if (MyMonth = 1) then
> Mo1 = Mo1 + Work_Hours;
> else
> Mo2 = Mo2 + Work_Hours;
> else
> ....
>
> else
> Mo12 = ....
>
> end
> suspend;
> end
> end
>
> Hans