Thank you Serge. I passed this step.
I am using Tonkuma example as a template to learn how to call SP from Udf:
--#SET TERMINATOR !
CREATE FUNCTION Calculate (inStr VARCHAR(100))
RETURNS TABLE (Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE Result FLOAT;
CALL Calculate(inStr, Result);
RETURN VALUES Result;
END!
--#SET TERMINATOR !
CREATE PROCEDURE Calculate (IN inStr VARCHAR(100),OUT Result FLOAT)
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE CalcSelect VARCHAR(130) DEFAULT '';
DECLARE PrepSelect STATEMENT;
DECLARE C1 CURSOR FOR PrepSelect;
SET CalcSelect = 'SELECT '||COALESCE(inStr,'CAST(NULL AS FLOAT)')
||' FROM SYSIBM.SYSDUMMY1';
PREPARE PrepSelect FROM CalcSelect;
OPEN C1;
FETCH C1 INTO Result;
CLOSE C1;
END!
EXAMPLE OF USE:
SELECT deptno
, mgrno
, INT(C.Result) AS INT_Result
FROM Department D
, TABLE( Calculate(mgrno||'*10+5' ) ) C;
My SP final:
CREATE PROCEDURE TAB_SEL_ANYPAGE(TNAME CHAR(20),PREDICATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER,RESULT VARCHAR(2000))
MODIFIES SQL DATA
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN NOT ATOMIC
DECLARE SQL_CURS1 CHAR(200) ;
DECLARE STMT_CURS1 CHAR(200) ;
DECLARE CURS2 CURSOR WITH RETURN FOR STMT_CURS1 ;
SET SQL_CURS1 = 'SELECT * FROM '||TNAME ||
'WHERE ' || PREDICATE || ' ' || CHAR(N * (PAGE_NO - 1))
|| CASE WHEN N 0 THEN 'FETCH FIRST '|| CHAR(N) ||' ROWS ONLY'
WHEN N = 0 THEN ' '
ELSE
RAISE_ERROR ('77000', 'INVALID FETCH, VALID N - INTEGER 0')
END ;
PREPARE STMT_CURS1 FROM SQL_CURS1 ;
OPEN CURS2 ;
FETCH CURS2 INTO Result;
CLOSE CURS2;
END
DB20000I The SQL command completed successfully.
My UDF final:
CREATE FUNCTION SEL_ANYPAGE_SP (TNAME CHAR(20),PREDICATE VARCHAR(1000),N
INTEGER,PAGE_NO INTEGER)
RETURNS TABLE (RESULT VARCHAR(2000))
MODIFIES SQL DATA
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE RESULT VARCHAR(2000);
CALL TAB_SEL_ANYPAGE (TNAME,PREDICATE,N,PAGE_NO,RESULT);
RETURN VALUES RESULT;
END!
DB20000I The SQL command completed successfully.
But when i test it is not working:
select 1 FROM SYSIBM.SYSDUMMY1,
TABLE(SEL_ANYPAGE_SP('QUERY_LOG','INVOICE#' ,12,2)) TP;
SQL0440N No authorized routine named "SEL_ANYPAGE_SP" of type "FUNCTION "
having compatible argument.
Is is possible in DB2 to retrieve a table fetched in SP thru UDF??
Thank's In advance Leny G.
P.S I learned alot on this board and very happy that i find it.
Serge Rielau wrote:
Quote:
>Hmm...
>--#SET TERMINATOR !
>vs
>--#SET TERMINATOR!
>
>I think DB2 didn't recognize your terminator directive.
>
>Chances are you actually got two errors because DB2 thinks you gave it
>two statements. CREATE ...; and RETURN...;
>
>Cheers
>Serge
>
|
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums...bm-db2/200807/1
