Topic
  • No replies
SystemAdmin
SystemAdmin
5837 Posts

Pinned topic DB2 9.7 =>Function + NickName + Dynamic=> Problem

‏2013-03-30T12:54:57Z |
Good day! Sorry for my bad English !

I want to write scalar function (Return String).
This Function work with dynamic scripts and use NickName.

This SQL function must get SQL QWERY must work with this dynamic qwery and Return Result STRING:
--Prepare data:

create table TEMP.FAMILY(id int, name varchar(100)); insert into TEMP.FAMILY(id,name) VALUES(1,
'MOTHER'); insert into TEMP.FAMILY(id,name) VALUES(2,
'FATHER'); insert into TEMP.FAMILY(id,name) VALUES(3,
'BROTHER');

--call function

VALUES(TOOLS.JOIN(
'select name from TEMP.FAMILY',
';'))


Aim - it must be univarsal function, which transponent column with separator in row

  • Result = MOTHER;FATHER;BROTHER

That is real code function;


CREATE OR REPLACE FUNCTION TOOLS.JOIN ( STR_FROM VARCHAR(32000), SIMB    VARCHAR(100) ) RETURNS VARCHAR(32000) LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA INHERIT SPECIAL REGISTERS BEGIN DECLARE STR_EXEC   VARCHAR(32000); DECLARE STR_RESULT VARCHAR(32000); DECLARE SQLSTATE CHAR(5); DECLARE WITH_STR INTEGER ; DECLARE POS_SELECT INTEGER; DECLARE c1 CURSOR FOR s1 ; -- cursor -- exists or no with SET  WITH_STR = 

case when   locate(UPPER(
'with'),UPPER(STR_FROM)) >0  THEN 1  

else  0 end ;   IF(WITH_STR = 0) THEN -- operator with not exists it is simple variant set STR_EXEC  = 
'with  ttt(COL1) as (' || STR_FROM || 
' ) ' || 
' select ' || 
' listagg( ' || 
'CAST(COL1 as VARCHAR(32000)) ,' || 
''
'' || SIMB || 
''
'' ||  
') from ttt '; ELSE -- difine  position last select or tceles after reverse set POS_SELECT = length(STR_FROM, CODEUNITS16) - locate(UPPER(
'tceles'),TOOLS.REVERSE(UPPER(STR_FROM))) - 6 ; -- with exists in  dynamic construction set STR_EXEC  =  SUBSTRING(STR_FROM,1,POS_SELECT,CODEUNITS16) || 
', ttt(COL1) as ( ' || SUBSTRING(STR_FROM,POS_SELECT + 1 , locate(UPPER(
'tceles'),TOOLS.REVERSE(UPPER(STR_FROM))) + 6,  CODEUNITS16) || 
' )' || 
' select ' || 
' listagg( ' || 
'CAST(COL1 as VARCHAR(32000)) , ' || 
''
'' || SIMB || 
''
'' ||  
') from ttt '; END IF; -- execute in dynamic PREPARE s1 FROM  STR_EXEC ; OPEN c1; WHILE SQLSTATE = 
'00000' DO FETCH  c1  INTO STR_RESULT; END WHILE; CLOSE c1; -- 

return result String data RETURN cast(STR_RESULT as VARCHAR(32000)); END;


It is normal work with all dinamic script, but when in script use NickName i Get Error

Lookup Error - DB2 Database Error: ERROR 55047 IBMDB2/NT64 SQL20136N Routine "TEMP.RETURN_STR" (specific name "SQL130322110434800") attempted to access a federated object.

Please, Help Me!