All Forums Extensibility
Anantha 4 posts Joined 03/14
11 Sep 2014
Return a Concatenated string from User defined Funtion through Cursor

Hi,
 
   I want to get the list of employee names when i pass in the deptno as the input parameter in the user defined function. Below is the function Logic currently i have. I am getting the syntax error to use the for loop inside the case statement. Please correct me if am wrong in using the same and suggest any alternatives solution if possible.
 
CREATE FUNCTION SCHEMA_NAME.GET_EMP_NAMES (EMP_ID INTEGER)
  RETURNS VARCHAR
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  COLLATION INVOKER
  INLINE TYPE 1
 
  RETURN
   CASE WHEN (EMP_ID IS NULL) OR (CHARACTER_LENGTH(EMP_ID) = 0)
        THEN
        NULL
       WHEN (EMP_ID IS NOT NULL)
            THEN
             BEGIN
         DECLARE VAR_EMP_NM VARCHAR(1000); 
      FOR EMPCursor AS C_EMPLOYEES CURSOR FOR
      SELECT ENAME
      FROM SCHEMA_NAME.TEST_EMP WHERE EMPNO = :EMP_ID ORDER BY ENAME
      DO
      SET VAR_EMP_NM = VAR_EMP_NM||' , '||EMPCursor.ENAME;
      END FOR;
     END;             
         ELSE 0
   END; 
 
Thanks !
Anantha
 
 

anantha
Tags:
dnoeth 4628 posts Joined 11/04
11 Sep 2014

Hi Anantha,
you can't do that in a SQL UDF.
Be glad, performance would be horrible :)
 

Dieter

ToddAWalter 316 posts Joined 10/11
11 Sep 2014

There are a number of threads on this forum which talk about using recursive query to implement a pivot or list_agg operation which appears to be what is desired here.

Anantha 4 posts Joined 03/14
12 Sep 2014

Thanks dnoeth for the info.
@ToddAWalter, Thanks for the suggestion, i have created the recursive view to fetch the employee names in the required format. 
Now I get an error that we cant use a select statement inside the SQL UDF. I am working on Version 14.
The current code simplifies to the below.
Also when i use the binding variable passed as the parameter to the funciton in the query , the error :EMP_ID cannot be used in the UDF is shown.
CREATE FUNCTION SCHEMA_NAME.GET_EMP_NAMES (EMP_ID INTEGER)
  RETURNS VARCHAR
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  COLLATION INVOKER
  INLINE TYPE 1
 
  RETURN
   CASE WHEN (EMP_ID IS NULL) OR (CHARACTER_LENGTH(EMP_ID) = 0)
        THEN
        NULL
       WHEN (EMP_ID IS NOT NULL)
            THEN
             SELECT R.EMP_NAMES
    FROM    SCHEMA_NAME.EMP_RECURSIVE r
    WHERE R.EMP_ID = :EMP_ID           
         ELSE 0
   END;
I went through the postings in the forum on what is allowed inside the SQL UDF, found only expressions and sql are allowed.
As an Alternative i tried to eliminate the creation of the function and create a view by using the recursive view created i.e.
   CREATE RECURSIVE VIEW EMP_DETAILS (EMP_DETAIL1,EMP_DETAIL2,EMP_DETAIL3,EMP_DETAILS)
   AS
   (SELECT M.EMP_DETAIL1,
     M.EMP_DETAIL2,
     M.M.EMP_DETAIL3
     R.EMP_NAMES
 FROM EMP_TABLE_DET M
 INNER JOIN EMP_RECURSIVE_VIEW R
 ON M.EMP_NUM = R.EMP_NUM)
This results in the error that a view cannot be created from the recursive view.
Any suggestions please ..?
Thanks,
--Anantha

anantha

Anantha 4 posts Joined 03/14
28 Sep 2014

Hi All,
         Finally as an Alternative solution to the above problem .. I have created a procedure which can concatenate the values and populate a table on the execution of the procedure.

REPLACE PROCEDURE SCHEMA_NAME.TEST(OUT ERROR_LIST VARCHAR(200))

BEGIN

 

DECLARE L_CONCATENATED_LIST VARCHAR(500);

SET L_CONCATENATED_LIST = '';

 

 

FOR EMPCURSOR AS CURSOR C_EMP FOR

SELECT Y.* FROM EMP_DETAILS

DO

BEGIN

 

L_CONCATENATED_LIST = L_CONCATENATED_LIST||','||EMPCURSOR.EMP_NAME;

 

END;

 

INSERT INTO EMP_DETAILS1 VALUES (L_CONCATENATED_LIST);

 

END;

 

Thanks !

--Anantha

 

anantha

You must sign in to leave a comment.