Hallo,

i create a store procedure like this :

SET TERM ##;
CREATE PROCEDURE GET_OBJECT_TYPE_ID1 (ObjectTypeId INTEGER)
RETURNS (Result INTEGER)
AS
DECLARE VARIABLE TEMP1 INTEGER;
DECLARE VARIABLE TEMP2 INTEGER;
BEGIN
BEGIN
FOR SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE OBJECT_TYPE_ID = :ObjectTypeId
INTO :TEMP1
DO
FOR SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE OBJECT_TYPE_ID IN ( SELECT OBJECT_TYPE_ID FROM
M_OBJECT_TYPE
WHERE PARENT_OBJECT_TYPE_ID =
:TEMP1)
UNION
SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE OBJECT_TYPE_ID =
:ObjectTypeId
INTO :TEMP2
DO
BEGIN
FOR SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE PARENT_OBJECT_TYPE_ID IN ( SELECT
OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE PARENT_OBJECT_TYPE_ID =
:TEMP2)
UNION
SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE OBJECT_TYPE_ID = :ObjectTypeId
OR OBJECT_TYPE_ID IN ( SELECT OBJECT_TYPE_ID FROM
M_OBJECT_TYPE
WHERE PARENT_OBJECT_TYPE_ID =
:ObjectTypeId)
INTO :Result
DO
SUSPEND;
END
END
END ##

The result if i am running it : "SELECT * FROM GET_OBJECT_TYPE_ID(4)"
is 4,10,11,12,13,48,48,49,50,51,52,4,10,11,12,13,4,10,11,12,13

where the result of 4 is from SQL : "SELECT OBJECT_TYPE_ID WHERE
OBJECT_TYPE ID = :ObjectTypeId"
and the result of 10,11,12,13 is from SQL : "SELECT OBJECT_TYPE_ID WHERE
OBJECT_TYPE_ID IN ( SELECT OBJECT_TYPE_ID FROM

M_OBJECT_TYPE WHERE
PARENT_OBJECT_TYPE_ID = :ObjectTypeId)"
and the result of 48,49,50,51,52 is from SQL : "SELECT OBJECT_TYPE_ID FROM
M_OBJECT_TYPE
WHERE
PARENT_OBJECT_TYPE_ID IN ( SELECT OBJECT_TYPE_ID FROM M_OBJECT_TYPE
WHERE
PARENT_OBJECT_TYPE_ID = :TEMP2)"

but i want 4,10,11,12,13,48,49,50,51,52.

please help, what's wrong with this procedure ?