Inline UDF returning a computed string

[DevenBasak]

Hi,

I am new in DB2 8.1 below code works/logic works fine in Oracle, pls find below the inline UDF , the intent of this UDF is to create a string for each child and then return the computed string , following query would be fired and it should give result in the stated format



Query
~~~~~~~~~~~~~

SELECT EQUIP_ID , SCH.RANGE(EQUIP _ID) Description
FROM EQUIPMENT




Description
~~~~~~~~~~~~
1 to 100 Decibles, 1 to 50 N/m


on compiling this function it gives me following error

[DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: WITH;DECLARE CUR CURSOR;<SQL_var iable_condition _declaration>
Message: An unexpected token "WITH" was found following "DECLARE CUR CURSOR". Expected tokens may include: "<SQL_variable_ condition_decla ration>"



~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~

CREATE FUNCTION SCH.RANGE( P_EQUIPID INTEGER ) RETURNS VARCHAR(1000)
LANGUAGE SQL
BEGIN ATOMIC   
     DECLARE return_str VARCHAR(1000) DEFAULT ' ';
     DECLARE tmp VARCHAR(1000) DEFAULT ' ';

     DECLARE CUR CURSOR FOR
        SELECT CHAR(EM.LOWERLIMIT) || ' to ' || CHAR(EM.UPPERLIMIT) || CD.DESCRIPTION
        FROM    SCH.EQUIPMETRIC EM, SCH.CODES CD
        WHERE   EM.UOMID = CD.ID
        AND     EM.EQUIPID = P_EQUIPID
        FOR     READ ONLY;


     DECLARE EXIT HANDLER FOR NOT FOUND
       SET return_str = ' ';

     BEGIN
	     OPEN CUR;
	     
	     FETCH FROM CUR INTO tmp;

	     WHILE(SQLSTATE = '00000') DO
		SET return_str = return_str + ', ' + tmp;
		FETCH FROM CUR INTO tmp; 
	     END WHILE;

	     CLOSE CUR;
     END;

     RETURN return_str;
END

Pls help in resolving this issue , I have googled but couldnt get any solution

Thanks
Deven

[sakumar9]

I got some other error :(

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "FOR" was found following "DECLARE CUR CURSOR".
Expected tokens may include: "<SQL_variable_ condition_decla ration>". LINE
NUMBER=9. SQLSTATE=42601

Will find the solution and let you know.

Regards
-- Sanjay