Results 1 to 3 of 3
  1. #1
    Join Date
    May 2009
    Posts
    12

    Unanswered: Problem with for loop in function

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •