Hello folks,

Can you help me :)
In one of my stored procedure, i have problem with sub select.
I try to reproduce wrong sub select result .

To reproduce wrong sub select result, we have create two procedure with the
same purposes

This both the sp

/* sub select */

CREATE PROCEDURE TESTING2 (
MULAI DATE)
RETURNS (
EMPID_2 INTEGER,
DEPTID_2 INTEGER,
DEPTCODE_2 VARCHAR (4),
DEPTNAME_2 VARCHAR (48),
DEPTNO_2 VARCHAR (4))
AS
BEGIN

for
Select distinct( empid )
from atdhistory_temp
where ( workdate between :mulai and :mulai + 6 )
into :empid_2
do
begin

deptid_2 = 0;
deptcode_2 = '';
deptname_2 = '';
deptno_2 = '';

Select d.deptid, d.deptcode, d.deptname, d.deptno
from deptsections d
where d.deptid = (
Select first 1 deptid
from atdhistory_temp
where empid = :empid_2
and workdate = (
Select max( workdate )
from atdhistory_temp
where workdate between :mulai and :mulai
+ 6
and empid = :empid_2
)
)
into :deptid_2, :deptcode_2, :deptname_2, :deptno_2;

if ( deptid_2 = 0 ) then
SUSPEND;
end
END


/* no sub selects */

CREATE PROCEDURE TESTING (
MULAI DATE)
RETURNS (
EMPID_2 INTEGER,
DEPTID_2 INTEGER,
DEPTCODE_2 VARCHAR (4),
DEPTNAME_2 VARCHAR (48),
DEPTNO_2 VARCHAR (4))
AS
declare variable tempdate date;
declare variable deptid integer;
BEGIN
/* Procedure body */


for
Select distinct( empid )
from atdhistory_temp
where ( workdate between :mulai and :mulai + 6 )
into :empid_2

do
begin


deptid_2 = 0;
deptcode_2 = '';
deptname_2 = '';
deptno_2 = '';
tempdate = null;

Select max( workdate )
from atdhistory_temp
where workdate between :mulai and :mulai + 6
and empid = :empid_2
into :tempdate;

Select first 1 deptid
from atdhistory_temp
where empid = :empid_2
and workdate = :tempdate
into :deptid;

Select d.deptid, d.deptcode, d.deptname, d.deptno
from deptsections d
where d.deptid = ( :deptid
)
into :deptid_2, :deptcode_2, :deptname_2, :deptno_2;

if ( deptid_2 = 0 ) then
SUSPEND;
end
END


Both of sp is refer to 2 tables with this structure

/* Table: ATDHISTORY_TEMP */

CREATE TABLE ATDHISTORY_TEMP (
PRDID INTEGER NOT NULL,
EMPID INTEGER NOT NULL,
WORKDATE DATE NOT NULL,
DEPTID INTEGER);

CREATE INDEX IDX_ATDHISTORY_TEMP ON ATDHISTORY_TEMP (DEPTID, WORKDATE);
CREATE INDEX IDX_ATDHISTORY_TEMP1 ON ATDHISTORY_TEMP (WORKDATE, PRDID,
EMPID);


/* Table: DEPTSECTIONS */

CREATE TABLE DEPTSECTIONS (
DEPTID INTEGER NOT NULL,
DEPTCODE VARCHAR (4) CHARACTER SET NONE NOT NULL COLLATE NONE,
DEPTNAME VARCHAR (24) CHARACTER SET NONE COLLATE NONE,
DEPTNO VARCHAR (4) CHARACTER SET NONE COLLATE NONE,
CALCMETHOD SMALLINT default 0);

ALTER TABLE DEPTSECTIONS ADD PRIMARY KEY (DEPTID);
ALTER TABLE DEPTSECTIONS ADD UNIQUE (DEPTCODE);


Try to retrieve data from procedure

select * from testing( '02/09/2004' ) -> no result
select * from testing2( '02/09/2004' ) -> there is 3 row data

Please, give me some advice, what is "different" from these 2 procedures
which give me the wrong result.
This is my fault or others ?

Off course, this is a little part from my real stored procedure. But with
this sp, we can reproduce the wrong result.
If there is anybody interesting to reproduce this problem on theirs sistem,
i wil sent small data from two tables to you.


Sorry for my bad english


thanks and regards
bayu