Hi
I have the following query
select * from REP_REPORTPERASSESSOR('2002/10/01','2002/11/30')
which generates the following plan:
PLAN JOIN(JOIN())

The ddl of the procedure is:
SET TERM ^! ;
CREATE PROCEDURE REP_REPORTPERASSESSOR (STARTDATE TimeStamp, ENDDATE
TimeStamp) returns (PLANNEDASSESOR VarChar(30) CHARACTER SET NONE,
ASSESMENTCOUNT Integer, DIDNOTMAKEAPPOINTMENT Integer,
APPOINTMENTPERCENTAGE VarChar(10) CHARACTER SET NONE, MEETINGSPOSTPONED
Integer, MEETINGSPERCENTAGE VarChar(3) CHARACTER SET NONE) AS
begin
for select
a.PA,
a.AssesmentCount,
B.DIDNOTMAKEAPPOINTMENT,
C.MeetingsPostPoned
from (REP_REPORTPERASSESSORA(:STARTDATE,:ENDDATE) A
left join REP_REPORTPERASSESSORB(:STARTDATE,:ENDDATE) B on
A.PA=B.PA)
left join REP_REPORTPERASSESSORC(:STARTDATE,:ENDDATE) C on
C.PA=A.PA
into
:PLANNEDASSESOR,
:ASSESMENTCOUNT,
:DIDNOTMAKEAPPOINTMENT,
:MEETINGSPOSTPONED
do
begin
if (:DIDNOTMAKEAPPOINTMENT is null) then
DIDNOTMAKEAPPOINTMENT = 0;
if (:MEETINGSPOSTPONED is null) then
MEETINGSPOSTPONED = 0;

APPOINTMENTPERCENTAGE =
cast((DIDNOTMAKEAPPOINTMENT*100/ASSESMENTCOUNT)as varchar(4))||'%';
MEETINGSPERCENTAGE = cast((MEETINGSPOSTPONED*100/ASSESMENTCOUNT) as
varchar(4))||'%';

Suspend;
end
end

^!
SET TERM ; ^!


If I execute the select clause in the sp seperately the plan is

PLAN JOIN (JOIN (SORT (JOIN (D NATURAL,A INDEX (RDB$PRIMARY2))),SORT
(JOIN (D NATURAL,A INDEX (RDB$PRIMARY2)))),SORT (JOIN (D NATURAL,A INDEX
(RDB$PRIMARY2))))

I have backup and restored the database and the problem persists.

Does anybody know what is going on here.

Groete
Gerhardus