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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
momi_sabagCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.