hi to all fellow forumers,

i had a problem using if else in store proc, my store proc is design to pump datas for some table... it will collect data from 1 view and pump it to physical table and we can call this table (application). this application table has PK for its application id, which it will cause a problem when the view is generating a redundant data, the solutions is :

if the application id from view is not exist yet in the application table
we will insert it
else
we will update it for the same id

here are the sample code :

the view is = v_int_application
table is = int_application

create procedure sp_pump_data()

DEFINE cMIDARefId CHAR(25);
DEFINE cCompanyCode CHAR(10);
DEFINE irecUpdate INT;
DEFINE ireNo INT;
DEFINE cApplicationId CHAR(10);
DEFINE cProjectId CHAR(10);
DEFINE pOfficer CHAR(100);
DEFINE dDirector CHAR(100);
DEFINE directorn CHAR(100);

LET ireNo=0;
LET irecUpdate =0;
-- Insert in int_application Table

foreach
select application_id,processing_officer,deputy_director, director into cApplicationId,pOfficer,dDirector,directorn
from informix.v_int_application v

if not exists(select appl_id from int_application where appl_id=cApplicationId)
Then
INSERT INTO int_application (appl_id, mida_ref_no, form_type, mida_received_dt, appl_status,cpny_regno,
incorp_dt,division_id, officer_name, deputy_name, director_name,
cpny_code, cpny_name,
corr_addr1, corr_addr2, corr_addr3,corr_postcode,corr_city, corr_state_code, corr_cntry_code,
reg_addr1, reg_addr2, reg_addr3, reg_postcode, reg_city,reg_state_code, reg_cntry_code,
contact_name, contact_designation, contact_phone,contact_fax, contact_email, transfer_dt, retrieve_status)

Select application_id, mida_ref_no, application_type, date_received, normal_status,company_reg_num,
incorporation_date, division_id, processing_officer,deputy_director, director,
company_code, company_name,
corr_addr1, corr_addr2, corr_addr3, corr_postcode, corr_city,corr_state, corr_country,
reg_addr1, reg_addr2, reg_addr3, reg_postcode, reg_city, reg_state,reg_country,
contact1_name,contact1_designation,contact1_phone, contact1_fax,contact1_email, date(current),"N" From v_int_application;

else

update int_application set (officer_name, deputy_name, director_name)
=(pOfficer,dDirector,directorn) where appl_id=cApplicationId;
end if
end foreach

the syntax all good, but the else condition went wrong or may b other things which is causing the error of violating pk constrain in int_application in other word, it not doing the update command, keep on inserting the data although already got if condition : only insert if not exist

i'm new in informix, plz help