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;

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 ??