Db2

 View Only

Oracle Function to DB2 Function

  • 1.  Oracle Function to DB2 Function

    Posted Thu January 05, 2023 01:36 PM

    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