Thread: Please help create a loop for embedded SQL

  1. #1
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    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;

  2. #2
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    I have the logic just need help translating it into SQL - I think

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

    Can anyone turn that crap into code?

  3. #3
    Crazy Fool Perspective's Avatar
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    2,640
    You don't want records where pr.cc = 171 or 173? Why not just add that to your where clause?

  4. #4
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Chappell Hill, Texas
    Posts
    2,332
    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
    Last edited by Dino; 04-24-2008 at 06:26 AM.
    Mainframe assembler programmer by trade. C coder when I can.

  5. #5
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Pretty darn close

    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

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 8
    Last Post: 12-01-2008, 10:09 AM
  2. Mutiliplcation Program that uses a (do, while, and for loop)?
    By Debbie Bremer in forum C++ Programming
    Replies: 4
    Last Post: 10-11-2008, 06:04 PM
  3. syntax question
    By cyph1e in forum C Programming
    Replies: 19
    Last Post: 03-31-2006, 12:59 AM
  4. when a while loop will stop ?
    By blue_gene in forum C Programming
    Replies: 13
    Last Post: 04-20-2004, 03:45 PM
  5. How to change recursive loop to non recursive loop
    By ooosawaddee3 in forum C Programming
    Replies: 1
    Last Post: 06-24-2002, 08:15 AM