Please help create a loop for embedded SQL

The code below selects almost all the records I need into an array. What I need to do is if pr.cc = 171 or 173 grab the next pr.step record based on the existing where clause and place the pr.cc value found here into next_step_cc[i] not the 171 or 173 (invalid for this program purpose). Please help me if you can. Thanks.

Code:

---

212          EXEC SQL SELECT DISTINCT              ms.mo_pre,ms.mo,ms.mo_lot,ms.cin,ms.last_step,
    213                  ms.last_step_cc,ms.last_step_cc_sn,ms.piw,ms.coil_wdth_in,
    214                  ms.coil_gage_in,ms.coil_wght_calc_lbs,pr.cc,pr.cc_sn,
    215                  pr.sch_grp_cd_desc ,pr.cc,pr.sch_grp_cd, ms.coat_cd
    216        into    :coil_array[i].mo_pre,:coil_array[i].mo,:coil_array[i].mo_lot,
    217                :coil_array[i].cin,:coil_array[i].last_step,:coil_array[i].last_step_cc,
    218                :coil_array[i].last_step_cc_sn,
    219                :coil_array[i].piw,:coil_array[i].coil_wdth_in,
    220                :coil_array[i].coil_gage_in ,:coil_array[i].coil_wght_calc_lbs,
    221                :next_step_cc[i], /* RRL v1.9 */
    222                :next_step_cc_sn[i],
    223                :grp_cd_desc[i],:cc[i],:next_sch_grp_cd[i],
    224                :coil_array[i].coat_cd
    225        from psr_coil_mstr ms,msd_mfg_to_mo_lot m,msd_proc_rte pr
    226        where ms.next_step=pr.step and m.mo=ms.mo and m.mo_lot=ms.mo_lot
    227            and pr.psu_key=m.psu_key
    228            and ms.next_step_cc=996
    229            and ms.defunct_flag != 'Y'
    230        order by  pr.cc,pr.sch_grp_cd,ms.mo,ms.mo_lot,ms.cin;
    231    EXEC SQL BEGIN;
    232        i++;
    233    EXEC SQL END;
    234    EXEC SQL INQUIRE_INGRES (:tmp_rcount=rowcount);
    235    EXEC SQL COMMIT;

---

I WANT LOWEST STEP NUMBER WHERE PR.CC != 171 AND PR.CC !=173
> CURRENT STEP NUMBER

Can anyone turn that crap into code?

You don't want records where pr.cc = 171 or 173? Why not just add that to your where clause?

Since you can't "jump ahead" to the next record and then "jump back" to resume processing, you would need two cursors to perform that kind of logic (or some state flags indicating to the subsequent select, to go back and edit the values, but then that could get messy if you have two or more consecutive 171 or 173 values)

I suspect the easiest solution would be to perform a second pass of the result data, after all the rows have been selected, and replace cc[i] with next_step_cc[i+1] if cc == 171 or cc == 173, as long as i+1 was not also a 171 or 173. If so, then you'll need some nesting indicator and determine the logic to be used in this case.

By the way, this smells like a table used to manage data aspects of the after market MSD (multiple static discharge) Ignition systems Coils. Is it?

Todd

Thanks always for helping me out with these problems. They are getting harder to explain. Anyway I used the SQL query below and it seems to work fine. Have a great day!

-Carl

Code:

---

SELECT min(pr.step), pr.cc, ms.cin                             
                                                               
      from psr_coil_mstr ms,msd_mfg_to_mo_lot m,msd_proc_rte pr 
                                                                 
            where m.mo=ms.mo                                   
            and m.mo_lot=ms.mo_lot                           
            and pr.psu_key=m.psu_key                     
            and ms.next_step_cc=996                           
            and ms.defunct_flag != 'Y'                   
            and pr.step>ms.next_step                                         
                                                                 
            and pr.cc != 171 and pr.cc != 173                                 

            group by ms.cin, pr.cc

---