1.
--#SET DELIMITER !

CREATE FUNCTION LOCATE_ALL(searchstr VARCHAR(500)
,lookinstr VARCHAR(500))
RETURNS VARCHAR(500)
BEGIN ATOMIC
DECLARE locatevar INT DEFAULT 0;
DECLARE beginlook INT DEFAULT 1;
DECLARE returnvar VARCHAR(100) DEFAULT '';
WHILE beginlook <= LENGTH(lookinstr) DO
SET locatevar = LOCATE(searchstr,SUBSTR(lookinstr
,beginlook
,LENGTH(searchstr)));
IF locatevar > 0 THEN
IF returnvar <> '' THEN
SET returnvar = returnvar || ',';
END IF;
SET returnvar = returnvar || RTRIM(LTRIM(CHAR(beginlook)));
END IF;
SET beginlook = beginlook + 1;
END WHILE;
RETURN returnvar;
END!
COMMIT!


To compile this function:
1. db2cmd
2. db2 connect to sample
2. db2 -vf DB2_Function2.txt -td!


2.
CREATE FUNCTION foo2() RETURNS TABLE(a INT, b INT)
RETURN SELECT * FROM T1;






Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Jeanine Blomberg is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: Mujeeb
[mailto:db2udbdba-ezmlmshield-x79411194.[Email address protected]
Sent: Friday, October 26, 2007 9:42 AM
To: LazyDBA Discussion
Subject: RE: HI all Oracle to DB2 conversion problem


Hi DBAs ,

Any body is there to help


Thanks & Regards
Mujeeb

-----Original Message-----
From: Mujeeb
[mailto:db2udbdba-ezmlmshield-x76917060.[Email address protected]
Sent: Friday, October 26, 2007 5:53 PM
To: LazyDBA Discussion
Subject: HI all Oracle to DB2 conversion problem

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







---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

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