Currently we are converting Oracle to DB2. We are able to convert most of
the queries but converting the functions we are struck up.

Most of our functions are returning objects. Manipulations I mean inserts
into objects and manipulations in loops using bulk collect are also being
done in functions. We are able to do it in MSSQL using declare table.

But struck up in DB2.

I am sending the dummy table, data and the alternate db function in mssql
for oracle connect by function.



I need a example of a function which can return a table as the following
mssql function does.



CREATE TABLE PARENTCHILD_MASTER(

PKID numeric(15, 0) ,

NAME varchar(500) NOT NULL,

PARENTID numeric(15, 0) NOT NULL,

) ;



INSERT INTO PARENTCHILD_MASTER VALUES(1,'ME',1); INSERT INTO
PARENTCHILD_MASTER VALUES(2,'ME1',1); INSERT INTO PARENTCHILD_MASTER
VALUES(3,'ME2',2); INSERT INTO PARENTCHILD_MASTER VALUES(4,'ME3',3); INSERT
INTO PARENTCHILD_MASTER VALUES(5,'ME4',4); INSERT INTO PARENTCHILD_MASTER
VALUES(6,'ME5',5); INSERT INTO PARENTCHILD_MASTER VALUES(7,'ME6',6); INSERT
INTO PARENTCHILD_MASTER VALUES(8,'ME7',7); COMMIT;



create function get_childs(@id int)

returns @ids table(id_no int,lev int)

as

begin

declare @level int

declare @v_id int

select @level=1

insert into @ids select PKID,1 from PARENTCHILD_MASTER where PARENTID=@id
and PKID!=PARENTID set @v_id=(select min(id_no) from @ids) while @v_id is
not null begin set @level=@level+1 insert into @ids select PKID,@level from
PARENTCHILD_MASTER where PARENTID=@v_id and PKID not in (select id_no from
@ids) set @v_id=(select min(id_no) from @ids where id_no>@v_id) end return
end





Please help me in this. Thanks in advance.





Thanks & Regards

Mujeeb






DB2 & UDB email list listserv db2-l LazyDBA home page