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