Thread: Please help create a loop for embedded SQL

  1. #1
    Registered User
    Join Date
    Apr 2008

    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 = 171 or 173 grab the next pr.step record based on the existing where clause and place the 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.

    212          EXEC SQL SELECT DISTINCT              ms.mo_pre,,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_sn,
        215                  pr.sch_grp_cd_desc ,,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 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.sch_grp_cd,,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

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


    Can anyone turn that crap into code?

  3. #3
    Crazy Fool Perspective's Avatar
    Join Date
    Jan 2003
    You don't want records where = 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
    Katy, Texas
    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?

    Last edited by Dino; 04-24-2008 at 06:26 AM.
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  5. #5
    Registered User
    Join Date
    Apr 2008

    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!


    SELECT min(pr.step),, ms.cin                               
           from psr_coil_mstr ms,msd_mfg_to_mo_lot m,msd_proc_rte pr   
                 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 != 171 and != 173                                  
                 group by ms.cin,

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