I have a function that contains 'if'. 'for'. I want to create this function in Db2 (vesion 8) on mainframe. Please let me know the basic steps to do that. I have tried to execute it through SPOFY and QFM and got errors. It seems like there should be some specific way to create function in DB2 (V8) on mainframe (db2 z/os).
Please check the function below -
create function desc_func( v_rpt_id integer, v_desc_typ_cde varchar(1))
RETURNS VARCHAR(32672)
begin atomic
DECLARE v_desc VARCHAR(32672) default '';
for1: FOR tmp_row AS
(
SELECT
desc_seq_num,va rchar(evnt_desc _txt) evnt_desc_txt
FROM
t_flt_sfty_desc DES,t_flt_sfty_ rpt rptin1
WHERE DES.EVNT_DTE=rp tin1.EVNT_DTE
AND DES.EVNT_SEQ_NU M=rptin1.EVNT_S EQ_NUM
AND DES.RPT_EVNT_OR D_NUM = rptin1.RPT_EVNT _ORD_NUM
AND DES.RPTEE_TYP_C DE = rptin1.RPTEE_TY P_CDE
AND DES.View_typ_cd e = 'D'
AND desc_typ_cde = v_desc_typ_cde
AND rpt_id= v_rpt_id
ORDER BY
desc_seq_num
)
DO
IF desc_seq_num =1 THEN
SET v_desc = tmp_row.evnt_de sc_txt;
ELSE
SET v_desc = v_desc ||tmp_row.evnt_ desc_txt;
END IF;
END FOR for1;
return v_desc;
END
Please check the function below -
create function desc_func( v_rpt_id integer, v_desc_typ_cde varchar(1))
RETURNS VARCHAR(32672)
begin atomic
DECLARE v_desc VARCHAR(32672) default '';
for1: FOR tmp_row AS
(
SELECT
desc_seq_num,va rchar(evnt_desc _txt) evnt_desc_txt
FROM
t_flt_sfty_desc DES,t_flt_sfty_ rpt rptin1
WHERE DES.EVNT_DTE=rp tin1.EVNT_DTE
AND DES.EVNT_SEQ_NU M=rptin1.EVNT_S EQ_NUM
AND DES.RPT_EVNT_OR D_NUM = rptin1.RPT_EVNT _ORD_NUM
AND DES.RPTEE_TYP_C DE = rptin1.RPTEE_TY P_CDE
AND DES.View_typ_cd e = 'D'
AND desc_typ_cde = v_desc_typ_cde
AND rpt_id= v_rpt_id
ORDER BY
desc_seq_num
)
DO
IF desc_seq_num =1 THEN
SET v_desc = tmp_row.evnt_de sc_txt;
ELSE
SET v_desc = v_desc ||tmp_row.evnt_ desc_txt;
END IF;
END FOR for1;
return v_desc;
END