Calling a Stored Procedure in a UDF

Hi,

  I'm trying to do the following ( calling a stored procedure) in a function, but it is throwing an error. The called stored procedure returns multiple rows.
  Is this possible? Please help.

CREATE FUNCTION CHESSIE.F_REM (VI_CLIENT_ID INTEGER,AD_START_DT DATE,AD_END_DT DATE  )
------------------------------------------------------------------------
------------------------------------------------------------------------
 RETURNS TABLE(CLIENT_ID INTEGER,REMOVAL_DT DATE,RETURN_DT DATE)
LANGUAGE SQL
MODIFIES SQL DATA
EXTERNAL ACTION
NOT DETERMINISTIC

BEGIN ATOMIC
     RETURN (CALL CHESSIE.SP_REM (IN VI_CLIENT_ID INTEGER,IN AD_START_DT DATE, IN AD_END_DT DATE));
EN

Open in new window

pvsbandiAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
if you could take that entire logic, and convert it into one big select statement (and bypass the temp table) you could just use that select statement in the function
0
 
momi_sabagCommented:
it can't be done
what exactly does you stored procedure do?
why not doing it in the function?
0
 
pvsbandiAuthor Commented:
My Procedure returns a result set. Here is the code of my stored procedure.
 Can i turn my procedure into a function? Please advise.
CREATE PROCEDURE CHESSIE.SP_REM (IN VI_CLIENT_ID INTEGER,IN AD_START_DT DATE, IN AD_END_DT DATE  )
------------------------------------------------------------------------
------------------------------------------------------------------------


P1: BEGIN
--DECLARE VI_CLIENT_ID INTEGER;
DECLARE VD_REMOVAL_DT DATE;
DECLARE VD_REMOVAL_DT_OLD DATE;
DECLARE VD_RETURN_DT DATE;
DECLARE VD_RETURN_DT_OLD DATE;
DECLARE VN_CLNT_CNT INTEGER;
DECLARE VN_CLNT_STG_CNT INTEGER DEFAULT 0;
DECLARE VN_REM_STG_CNT INTEGER DEFAULT 0;
DECLARE VN_RET_STG_CNT INTEGER DEFAULT 0;
DECLARE VN_REM_CNT INTEGER DEFAULT 0;
DECLARE VS_CIS_ID VARCHAR(10);

DECLARE REM_CUR CURSOR WITH HOLD FOR
SELECT  REMOVAL_DT,RETURN_DT FROM TB_REMOVAL
WHERE CLIENT_ID = VI_CLIENT_ID
AND DELETE_SW = 'N'
ORDER BY REMOVAL_ID;

DECLARE CLNT_CUR CURSOR WITH HOLD FOR
SELECT DISTINCT AF.CLIENT_ID,TC.CIS_CLIENT_ID FROM TB_REMOVAL AF
JOIN TB_CLIENT TC ON AF.CLIENT_ID = TC.CLIENT_ID AND TC.CIS_CLIENT_ID IS NOT NULL
WHERE TC.CLIENT_ID = VI_CLIENT_ID
order by af.client_id;

DECLARE GLOBAL TEMPORARY TABLE REMOVALS(V_ID INTEGER GENERATED ALWAYS AS IDENTITY
                                       ,CLIENT_ID INTEGER
                                       ,REMOVAL_DT DATE
                                       ,RETURN_DT DATE
                                       )
 WITH REPLACE;

--DELETE FROM TB_AFCARS_CARES_IN;
--COMMIT;

SET VN_CLNT_CNT = (SELECT COUNT(DISTINCT AF.CLIENT_ID) FROM TB_REMOVAL AF
JOIN TB_CLIENT TC ON AF.CLIENT_ID = TC.CLIENT_ID AND TC.CIS_CLIENT_ID IS NOT NULL
AND AF.CLIENT_ID = VI_CLIENT_ID)   ;


OPEN CLNT_CUR;
        WHILE VN_CLNT_CNT > 0 DO
                FETCH FROM CLNT_CUR INTO VI_CLIENT_ID,VS_CIS_ID;

                        SET VN_REM_CNT = (SELECT COUNT(*) FROM TB_REMOVAL WHERE CLIENT_ID = VI_CLIENT_ID AND DELETE_SW = 'N');
                        OPEN REM_CUR;
                        WHILE VN_REM_CNT > 0 DO

                        FETCH REM_CUR INTO VD_REMOVAL_DT,VD_RETURN_DT;
                                SET VN_CLNT_STG_CNT = (SELECT COUNT(*) FROM SESSION.REMOVALS WHERE CLIENT_ID = VI_CLIENT_ID);
                                SET VN_REM_STG_CNT = (SELECT COUNT(*)FROM SESSION.REMOVALS WHERE CLIENT_ID = VI_CLIENT_ID AND RETURN_DT = VD_REMOVAL_DT);
                                SET VN_RET_STG_CNT = (SELECT COUNT(*)FROM SESSION.REMOVALS WHERE CLIENT_ID = VI_CLIENT_ID AND REMOVAL_DT = VD_RETURN_DT);

                                IF VN_CLNT_STG_CNT = 0 THEN
                                       INSERT INTO SESSION.REMOVALS(CLIENT_ID,REMOVAL_DT,RETURN_DT)
                                       VALUES(VI_CLIENT_ID,VD_REMOVAL_DT,VD_RETURN_DT) ;
                                END IF;

                                IF VN_CLNT_STG_CNT >= 1 AND VN_REM_STG_CNT = 0 AND VN_REM_STG_CNT = 0  THEN
                                           INSERT INTO SESSION.REMOVALS(CLIENT_ID,REMOVAL_DT,RETURN_DT)
                                       VALUES(VI_CLIENT_ID,VD_REMOVAL_DT,VD_RETURN_DT) ;
                                END IF;

                                IF VN_REM_STG_CNT >= 1 AND VD_REMOVAL_DT = VD_RETURN_DT_OLD THEN
                                        UPDATE SESSION.REMOVALS
                                        SET RETURN_DT = VD_RETURN_DT
                                        WHERE CLIENT_ID = VI_CLIENT_ID
                                        AND V_ID = (SELECT V_ID FROM SESSION.REMOVALS
                                                  WHERE CLIENT_ID = VI_CLIENT_ID
                                                  ORDER  BY V_ID DESC
                                                  FETCH FIRST 1 ROW ONLY);
                                END IF;
                               SET VD_REMOVAL_DT_OLD = VD_REMOVAL_DT;
                               SET VD_RETURN_DT_OLD = VD_RETURN_DT;

                             SET VN_REM_CNT = VN_REM_CNT -1;

                         END WHILE;

                         CLOSE REM_CUR;
              SET VN_CLNT_CNT = VN_CLNT_CNT- 1;
              END WHILE;

        CLOSE CLNT_CUR;

P2: BEGIN

DECLARE CUR2 CURSOR WITH RETURN TO CLIENT FOR
SELECT DISTINCT CLIENT_ID,REMOVAL_DT,RETURN_DT
FROM SESSION.REMOVALS;
OPEN CUR2;

END P2;
END P1 

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
pvsbandiAuthor Commented:
I'm not able to convert it into a SQL statement. That's why i came up with these compound statements in a loop.

Can you help me convert it to a SQL?
0
 
pvsbandiAuthor Commented:
I have been able to create a table type function, returning client_id, removal_dt and return_dt.
 But it works when i pass a value at a time.

select * from table(f_rem(8756)) as removal;

But when i try to use like below, it is saying r.client_id is not valid in the context used.
How do i use the table type function to return result sets for all the client_id s?

select removal.*
from table(f_rem(r.client_id)) as removal,tb_rmt as r
where removal.client_id = r.client_id;
0
 
pvsbandiAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for pvsbandi's comment http:/Q_27494319.html#37288339

for the following reason:

I'll ask this additional logic challenge as a new question.
0
 
pvsbandiAuthor Commented:
please accept Momi's suggestion (ID: 37288103)..i hit mine by mistake
0
 
pvsbandiAuthor Commented:
I'll create a new question for my challenge.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.