Results 1 to 2 of 2

Thread: For Each

  1. #1
    Join Date
    Mar 2003
    Location
    Bangalore
    Posts
    15

    Unanswered: For Each

    Can I have more than one select statement in a FOR EACH ? I want to know if I can use a delete, and more select statements in one FOR EACH loop.

    would this make any sense to anyone ? Please help coz I'm really new to this.

    CREATE PROCEDURE "informix".temp_regn()

    DEFINE var_region_code char(15);
    DEFINE store smallint;
    DEFINE filename char(15);
    DEFINE var_region char(30);

    SET LOCK MODE TO WAIT 5;

    BEGIN WORK;

    CREATE TEMP TABLE tmp_curr_stores ( store smallint ) with no log;
    CREATE TEMP TABLE tmp_region_stores ( store smallint ) with no log;

    FOREACH SELECT
    pdf_file_name,
    INTO filename,
    FROM "ezap".usa_pdfs;

    INSERT INTO tmp_curr_stores
    SELECT store_id
    INTO store
    FROM ezap_manifest_mstr
    WHERE emm_new_filename = filename;

    DELETE FROM tmp_pdfs
    WHERE store_id NOT IN ( SELECT emm_store_id from ezap_manifest_mstr );

    SELECT region_code, region
    INTO var_region_code,var_region
    FROM "ezap".ezap_pdf_regions
    where epr_new_filename = filename;

    INSERT INTO tmp_region_stores
    SELECT store_id
    INTO store
    FROM "informix".az_vstores, ezap_pdf_regions
    WHERE "informix".az_vstores || '.' || var_region_code =
    "informix".az_vstores || '.' || var_region

    DELETE FROM tmp_region_stores
    WHERE store IN ( SELECT store FROM tmp_curr_stores );

    INSERT INTO ezap_manifest_mstr( emm_store_id, emm_new_filename, emm_instruction_cd )
    select store, filename, 'NS'
    from tmp_region_stores

    UPDATE "ezap".ezap_manifest_mstr
    SET emm_store_id = storeid
    emm_new_filename = filename
    emm_instruction_cd = 'NS'

    END FOREACH;

    COMMIT WORK;

    END PROCEDURE;

  2. #2
    Join Date
    Oct 2005
    Posts
    6
    You can have nested FOREACH loops if thats what you mean, eg:-

    FOREACH SELECT columnList1 INTO variableList1 FROM table1

    FOREACH SELECT columnList2 INTO variableList2 FROM table2
    Do Somethings
    END FOREACH ;

    Do Some Other things ;

    END FOREACH;


    Is that what you meant ??

Posting Permissions

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