I want to write scalar function (Return String), This Function work with dynamic scripts and use NickName.
Ок.
This is Procedure, what get SQL QWERY and after dynamic work Return STRING.
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE PROCEDURE TEMP.RETURN_STR
- (IN IN_STR VARCHAR(32000),
- OUT OUT_STR VARCHAR(32000)
- )
- LANGUAGE SQL
- NOT DETERMINISTIC
- CALLED ON NULL INPUT
- NO EXTERNAL ACTION
- OLD SAVEPOINT LEVEL
- READS SQL DATA
- INHERIT SPECIAL REGISTERS
- BEGIN
- DECLARE c1 CURSOR FOR s1;
- PREPARE s1 FROM IN_STR;
- OPEN c1;
- FETCH c1 INTO OUT_STR;
- CLOSE c1;
- RETURN;
- END;
FORM.FUND - it is NickName
*/
Expand|Select|Wrap|Line Numbers
- CREATE OR REPLACE FUNCTION TEMP.TESTSC ()
- RETURNS VARCHAR(32000)
- LANGUAGE SQL
- NOT DETERMINISTIC
- READS SQL DATA
- STATIC DISPATCH
- CALLED ON NULL INPUT
- NO EXTERNAL ACTION
- INHERIT SPECIAL REGISTERS
- BEGIN ATOMIC
- DECLARE STR_RESULT VARCHAR(32000);
- DECLARE STR_EXEC VARCHAR(32000);
- SET STR_EXEC = 'select MAX(ID_PERSON) from FORM.FUND where ID_PERSON_FUND_PARENT <50';
- CALL TEMP.RETURN_STR (STR_EXEC,STR_RESULT);
- RETURN cast(STR_RESULT as VARCHAR(32000));
- END;
- select TEMP.TESTSC() from SYSIBM.SYSDUMMY1;
Lookup Error - DB2 Database Error: ERROR [55047] [IBM][DB2/NT64] SQL20136N Routine "TEMP.RETURN_STR" (specific name "SQL130322110434800") attempted to access a federated object.
Please, Help Me!