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_variable_condition_declaration>
Message: An unexpected token "WITH" was found following "DECLARE CUR CURSOR". Expected tokens may include: "<SQL_variable_condition_declaration>"



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

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_declaration>". LINE
NUMBER=9. SQLSTATE=42601

Will find the solution and let you know.

Regards
-- Sanjay