Avatar of pvsbandi
pvsbandi
Flag for United States of America asked on

Using a SP in a DB2 Function

Hi,

  I have a stored procedure  SP1 which returns the following result set.

ID1                  CL_ID                Link_ID1
123                   ABC                    234
 333                  DEF                       --
444                   HIJ                      567

Now, i want to create a UDF with V_ID as a parameter.
This will call this stored Procedure SP1 and pass the V_ID parameter against the result set, to filter for the Link_ID1.

So, if i pass the V_ID as 444 inside the UDF, then it should check for the Link_ID1 in the stored procedure. Since there is no match, it should return 444.
 IF i pass 234 in the UDF, then it should look for the match of 234 and return ID1, which is 123.

Is there a way i can do it? Please advise.
DB2

Avatar of undefined
Last Comment
pvsbandi

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kent Olsen

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pvsbandi

ASKER
Sure.. how? Please advise.

  I want to be able to use the function in the select statements  to return the ID1 or the passed V_ID from the function.
momi_sabag

can you post your stored procedure code? (the one that generates that table)
maybe you can use only a function to get it all done, maybe a view will be enough
pvsbandi

ASKER
Hi,

 Please find the code attached.
SP.sql
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
mustaccio

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
pvsbandi

ASKER
Thanks. Can this procedure be altered to facilitate the creation of a UDF?
mustaccio

Like I said, you won't be able to process a cursor in the UDF, so the only way to alter your SP is to remove the OPEN statement, which is probably not what you want.

Can you explain in simple terms what you're trying to achieve in your stored procedure?
SOLUTION
Member_2_276102

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
pvsbandi

ASKER
Sorry for the delay..was out of town.
   and thanks for the advise! I have created a new procedure with the logic embedded in it.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.