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
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 cCompanyCode CHAR(10);
DEFINE irecUpdate 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

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)
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;


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