Hi all,
I having following fuction.But this function returning me null value every time.
I think the problem area is "for @n_cost1 as".
May i use this for loop like a cursor or may i use cursor in function.
If yes Please provide me the solution with code or any example.

create FUNCTION scf_OverviewForTrainingAdmin_UNASSIG_cost
(@plan varchar(20) ,
@manager varCHAR(20) ,
@currentUser varCHAR(20),
@organization varchar(25) ,
@javaLocale VARCHAR(25) )
RETURNS real
language sql
reads sql
data format:
begin atomic
declare @n_cost float;
declare @n_cost1 float;
set @n_cost =0.00;

for @n_cost1 as
(
select
coalesce(scf_course_cost(ot.id,@currentUser, @javaLocale),0) cost
from
scv_ext_plan_form pl
inner join scv_offering_request offreq on offreq.custom9=pl.id
inner join TPV_PUB_I18N_SMP_OFFERING_TEMP ot on offreq.offering_temp_id=ot.id
left outer join TPV_PUB_SMP_LOCATIONS loc on offreq.location_id=loc.id
inner join cmv_pub_smp_person per on offreq.req_creator_id=per.id
inner join tpv_pub_all_org cmp on per.company_id=cmp.id
inner join fgv_sys_list_of_val lov on cast(offreq.status as char)=cast(lov.name as char)
where
pl.locale_id = ot.locale_id
and ot.locale_id = lov.locale_id
and lov.locale_id = @javaLocale
and lov.list_id ='sysli000000000000163'
and offreq.status<> 102
and pl.id = @plan
and cmp.id=@organization
and offreq.learner_id is null
and coalesce(per.manager_id,'abc') = coalesce(@manager,'abc')

union all

select
coalesce(scf_course_cost(ot.id,@currentUser, @javaLocale),0) cost
from
scv_ext_plan_form pl
inner join scv_offering_request offreq on offreq.custom9=pl.id
inner join TPV_PUB_I18N_SMP_OFFERING_TEMP ot on offreq.offering_temp_id=ot.id
left outer join TPV_PUB_SMP_LOCATIONS loc on offreq.location_id=loc.id
inner join cmv_pub_smp_person per on offreq.req_creator_id=per.id
inner join tpv_pub_all_org cmp on per.company_id=cmp.id
inner join fgv_sys_list_of_val lov on cast(offreq.status as char)=cast(lov.name as char)
where
pl.locale_id = ot.locale_id
and ot.locale_id = lov.locale_id
and lov.locale_id = @javaLocale
and lov.list_id = 'sysli000000000000163'
and offreq.status<> 102
and pl.id = @plan
and cmp.id=@organization
and offreq.learner_id is null
and per.id= @manager
) do
set @n_cost = @n_cost1 + @n_cost;
end for;
return @n_cost;
end format!


Regards
Vishal

Try to change
"set @n_cost = @n_cost1 + @n_cost;"
to
"set @n_cost = cost + @n_cost;"

You should not name for-loop-name("for @n_cost1 as ...") same as SQL-variable("declare @n_cost1 float;").
It might be cause of your issue.

for loop would be not necessary.

Replace all of your function body(from "data format: begin atomic" to "end format!")

with

RETURN
(SELECT SUM(cost)
FROM (/* put here select-statement in your for loop */) q);

or

RETURN
(SELECT SUM(FLOAT(cost))
FROM (/* put here select-statement in your for loop */) q);

Note1: Replace ";" with "!", according to the statement termination character.
Note2: If you are using DB2 for LUW 9.5, "q" may be not necessary.