Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201

    Unanswered: Proceedure issue

    The below proceedure is haveing a problem which I cannot resolve.

    Using my data
    THe external for loop returns 1 row.
    The 2nd loop returns 14 rows
    the 3rd loop returns 1 row.

    I would expect the insert statment to run 14 times.
    However it only runs once.
    ends internal loop
    goes back to 2nd loop
    and out to external loop.

    any help would be appreciated

    procedure update_rs_assignment(
    p_user_id in varchar2 := null)
    is

    i number;
    v_user_id varchar2(30);
    v_referral_uid number;
    x number;
    v_workflow_step varchar2(10);
    v_action varchar2(5);
    v_next_resp varchar2(30);
    v_result varchar2(100);
    v_comment varchar2(1000);
    v_fcm Varchar2(30);
    begin

    for lk in (select a.user_id
    from apexim.hum_referral_users a
    WHERE a.user_role = 'RS'
    and a.user_id = nvl(p_user_id,a.user_id))loop

    select 30 - count(*) into i
    from apexim.hum_referral_workflow wf
    where wf.responsible_userid = lk.user_id
    and wf.current_rec_ind = 'Y';

    for lx in (select referral_uid From
    (select a.referral_uid from apexim.hum_referral_workflow a
    join apexim.hum_referral_users_state b
    on a.oper_state = b.oper_state
    and current_rec_ind = 'Y'
    join apexim.grh_state s
    on b.oper_state = s.state
    where sysdate between s.eff_date and s.obs_date
    and s.ref_member_cnt/s.ttl_member_cnt <= s.ref_threshold
    and a.process_step_cd = 'SM'
    and b.user_id = lk.user_id
    order by a.ref_rank desc)
    where rownum <= i) loop

    v_referral_uid := lx.referral_uid ;
    v_user_id := lk.user_id;
    v_workflow_step := 'RS';

    for cl in
    (select * from apexim.hum_referral_workflow a
    where a.referral_uid = lx.referral_uid)
    loop

    v_comment := null;
    v_result := null;

    select max(referral_uid) + 1 into x from apexim.hum_referral_workflow;

    insert into apexim.hum_referral_workflow
    values
    (x,
    cl.id_cardid,
    cl.member_id,
    cl.oper_state,
    cl.referral_source,
    v_user_id,
    'Y',
    v_workflow_step,
    v_result,
    v_comment,
    'SYSTEM',
    sysdate,
    cl.referral_uid,
    cl.member_name,
    cl.member_demo,
    cl.ref_rank,
    cl.asmt_id,
    cl.sm_userid,
    cl.fcm_userid);
    commit;

    update apexim.hum_referral_workflow a
    set a.current_rec_ind = 'N',
    a.process_step_result = 'REFA',
    a.process_step_comment = 'System Completed',
    a.last_update_userid = 'System',
    a.action_timestamp = sysdate
    where a.referral_uid = cl.referral_uid;

    commit;

    end loop;

    end loop;

    end loop;
    end update_rs_assignment;

  2. #2
    Join Date
    Dec 2007
    Posts
    253
    Please supply create table and insert scripts so that we can emulate your issue

  3. #3
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    See attached
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Cheers for that. At first pass,
    Code:
    select a.referral_uid 
                          from  hum_referral_workflow a
                          join  hum_referral_users_state b
                          on a.oper_state = b.oper_state
                          and current_rec_ind = 'Y'
                          join  grh_state s
                          on b.oper_state = s.state
                          where sysdate between s.eff_date and s.obs_date
                          and s.ref_member_cnt/s.ttl_member_cnt <= s.ref_threshold
                          and a.process_step_cd = 'SM'
                          and b.user_id = 'RBACKMANN'
                          order by a.ref_rank desc
    returns no rows. I would guess that this'd be one of the issues. Go back over your cursors and double check the returns on them.

  5. #5
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Pablolee

    Sorry I sent an incomplete set of data I sent just a sample

    add this
    insert into APEXIM.HUM_REFERRAL_USERS_STATE (USER_STATE_UID, USER_ID, OPER_STATE)
    values (2, 'RBACKMANN', 'SC');

    And the internal select will return rows.

  6. #6
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Found a solution

    changed the middle loop to

    for lx in (select a.referral_uid from
    (select * from apexim.hum_referral_workflow order by ref_rank desc) a
    join apexim.hum_referral_users_state b
    on a.oper_state = b.oper_state
    and current_rec_ind = 'Y'
    join apexim.grh_state s
    on b.oper_state = s.state
    where sysdate between s.eff_date and s.obs_date
    and s.ref_member_cnt/s.ttl_member_cnt <= s.ref_threshold
    and a.process_step_cd = 'SM'
    and b.user_id = lk.user_id
    and rownum <= i)
    loop

    ODD

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    Hi sorry I didn't get back to you, it was bed time for me (early start in the morning). I'm glad that you got it sorted. Can I ask a favour, in future, if you are posting code, can you enclose that code in code tags:
    [code] your code here [/code]
    Which makes it look like
    Code:
    select col1, col2
    from taba
    where colb = 21
    Also, some kind of case convention and formatting can really help others read and understand your code.
    Cheers
    Jim

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just a note, it is normally NOT a good idea to commit within the loop, do it outside the loop. It slows down processing and can lead to snapshot too old errors. Perform the commit outside the last end loop.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Dec 2007
    Posts
    253
    Didn't even SEE that, cheers Bill

  10. #10
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Thank to both of you

    I will format my Posts as you suggest.
    And I had moved the commit outside of the loop

    However were either of you able to resolve why the error occurred in the first place.
    The only difference in the solution and the Previous code was the user of a sub select ordering by a rank,selected the result and then selected x amount of rows.
    Where The original selected the result set, ranked and then selected x amount of rows.

    Each of the select produced the properly ranked values although they may different records of the same rank.

    however the solution continued to loop where the original ended after one insert loop.

    This is not important to continue my process because I have found another way to "skin this cat" however I am just curious if this may be a bug or my coding or logic were incorrect.

Posting Permissions

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