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