Click here to Skip to main content
15,908,768 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

i am not able to return multiple value.

i wan to return tmpVar,tmpVar1,tmpVar2,tmpVar3 from my below mention function

can anybody help me out.

My code is as follows

CREATE OR REPLACE FUNCTION ESUPPORT.TRAINING_SCORE (train_id NUMBER) RETURN number IS
tmpVar NUMBER ;
tmpVar1 NUMBER ;
tmpVar2 NUMBER ;
tmpVar3 NUMBER ;

v_no_of_participant number :=0;
v_sum_of_responsce_prog_cont number :=0;
v_sum_of_responsce_prog_facu number :=0;
v_sum_of_responsce_prog_logi number :=0;
v_sum_of_responce_prog_lear number :=0;

BEGIN
select COUNT(*) into v_no_of_participant FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A1)+sum(B1)+sum(C1)+sum(D1) into v_sum_of_responsce_prog_cont FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A2)+sum(B2)+sum(C2)+sum(D2) into v_sum_of_responsce_prog_facu FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A1)+sum(B1)+sum(C1)+sum(D1) into v_sum_of_responsce_prog_logi FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
select sum(A1)+sum(B1)+sum(C1)+sum(D1) into v_sum_of_responce_prog_lear FROM ESUPPORTV_FB_TRAINING_FEEDBACK WHERE FB_TRAINING_ID = train_id;
tmpVar := v_sum_of_responsce_prog_cont/(4*v_no_of_participant);
tmpVar1 := v_sum_of_responsce_prog_facu/(4*v_no_of_participant);
tmpVar2 := v_sum_of_responsce_prog_logi/(4*v_no_of_participant);
tmpVar3 := v_sum_of_responce_prog_lear/(4*v_no_of_participant);

RETURN tmpVar;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END TRAINING_SCORE;

Thanking you
Posted
Updated 13-Nov-13 22:12pm
v2
Comments
bluesathish 14-Nov-13 5:44am    
Yes you can create procedure instead of function, by assigning your output to the refcursor variable and returning it as a output variable.

1 solution

Why not simply create 5 functions? One for getting the number of participants and 4 others to get the response_prog values. The queries or completely unrelated and do not need each others value to work. Splitting up into functions also leaves room for other implementations later on without the need for other specialized stored functions. For example, just showing the number of participants, or if someone is only interested in one of those 4 values.

Good luck!
 
Share this answer
 
Comments
Mahesh Devikar 14-Nov-13 5:23am    
Actual i have pasted part of code here.
This all four value is used in this function on later stage to perform further calculation.
So don't want to use separated function for getting separate value, even though i m interested in getting all value which is required in further programming.
E.F. Nijboer 14-Nov-13 13:41pm    
I understood your code and see you use all of the data. What I meant was that each of those calculations is isolated. The only variable all 4 use is v_no_of_participant. That's why my suggestion is to split the function up to get each value separately and do the calculation in your further programming. Otherwise you need to use create a stored procedure and create a union that will give the values all at once.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900