-----Original Message-----
From: Rotandiko Sastroprawiro [mailto:rsastroprawiro@...]
Sent: Monday, July 08, 2002 11:18 AM
To: 'ib-support@yahoogroups.com'
Subject: [ib-support] create procedure




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 ?


My Tables is

OBJECT_TYPE_ID NAME PARENT_OBJECT_TYPE_ID
1 T
2 R
4 E
10 W 4
11 C 4
12 V 4
13 D 4
30 S 1
48 F 10
49 H 11
50 J 12
51 N 12
52 P 13