Return a Concatenated string from User defined Funtion through Cursor

Extensibility

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

Tags (2)
4 REPLIES
N/A

Re: Return a Concatenated string from User defined Funtion through Cursor

Hi Anantha,

you can't do that in a SQL UDF.

Be glad, performance would be horrible :)

Teradata Employee

Re: Return a Concatenated string from User defined Funtion through Cursor

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.

Re: Return a Concatenated string from User defined Funtion through Cursor

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

Re: Return a Concatenated string from User defined Funtion through Cursor

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