I am new to Functions and I need to convert an existing Oracle Function to DB2 . I got an error in exception part .
Oracle Query:
CREATE OR REPLACE Function ETLUAT.FUNC_GET_CLAIM_STATUS(In i_claim_no Varchar(4000))
Return varchar(4000) Is
tempReasonid varchar(50);
tempReasontxt varchar(5000);
txtreason varchar(4000);
tempStatus varchar(1);
tempUpdateNo int;
tempOutPut varchar(2000);
tempCnt int;
tempPayStatus int;
tempPayStatusChar varchar(25);
begin
Select A.TXT_CLAIM_STATUS, a.num_update_no into tempStatus, tempUpdateNo From ETLUAT.PRESTG_GC_CLAIM_INFO A
where A.Num_Update_No =(Select Max(Num_Update_No) From ETLUAT.PRESTG_GC_CLAIM_INFO G Where G.Num_Claim_No = i_claim_no)
and a.num_claim_no = i_claim_no;
if tempStatus = 'N' then tempOutPut := 'Notification';
elseif tempStatus = 'I' then
begin
select count(1) into tempCnt from ETLUAT.PRESTG_GC_CLAIM_INFO where TXT_CLAIM_STATUS = 'I'
AND NUM_CLAIM_NO = i_claim_no and NUM_UPDATE_NO >(select max(NUM_UPDATE_NO) From ETLUAT.PRESTG_GC_CLAIM_INFO
where TXT_CLAIM_STATUS = 'N' AND NUM_CLAIM_NO = i_claim_no);
if tempCnt = 1 then tempOutPut := 'Registration';
elseif tempCnt > 1 then tempOutPut := 'Processing';
else tempOutPut := 'Others';
end if;
exception when others then tempOutPut := 'Others';
end;
return tempOutPut;
end;
DB2 :
CREATE OR REPLACE FUNCTION ETLUAT.FUNC_GET_CLAIM_STATUS(IN i_claim_no Varchar(4000))
RETURNS varchar(4000)
BEGIN
DECLARE tempReasonid varchar(50);
DECLARE tempReasontxt varchar(5000);
DECLARE txtreason varchar(4000);
DECLARE tempStatus varchar(1);
DECLARE tempUpdateNo int;
DECLARE tempOutPut varchar(2000);
DECLARE tempCnt int;
DECLARE tempPayStatus int;
DECLARE tempPayStatusChar varchar(25);
Select A.TXT_CLAIM_STATUS, a.num_update_no into tempStatus, tempUpdateNo From ETLUAT.PRESTG_GC_CLAIM_INFO A
where A.Num_Update_No =(Select Max(Num_Update_No) From ETLUAT.PRESTG_GC_CLAIM_INFO G Where G.Num_Claim_No = i_claim_no) and a.num_claim_no = i_claim_no;
if tempStatus = 'N' then SET tempOutPut = 'Notification';
elseif tempStatus = 'I' then
begin
select count(1) into tempCnt from ETLUAT.PRESTG_GC_CLAIM_INFO where TXT_CLAIM_STATUS = 'I'
AND NUM_CLAIM_NO = i_claim_no and NUM_UPDATE_NO >(select max(NUM_UPDATE_NO) From ETLUAT.PRESTG_GC_CLAIM_INFO
where TXT_CLAIM_STATUS = 'N' AND NUM_CLAIM_NO = i_claim_no);
if tempCnt = 1 then SET tempOutPut = 'Registration';
elseif tempCnt > 1 then SET tempOutPut = 'Processing';
else SET tempOutPut = 'Others';
end if;
exception when others then SET tempOutPut = 'Others';
end;
return tempOutPut;
end;
------------------------------
kiruthika kumar
------------------------------
#Db2