SET SCHEMA TMISUSR;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";
CREATE FUNCTION "TMISUSR"."F_DEBTGETTRELIST"
("PI_I_YEAR" INTEGER,
"PI_S_TREAREA" VARCHAR(1024),
"PI_S_TRELEVEL" CHARACTER(1)
)
RETURNS VARCHAR(1024)
SPECIFIC "TMISUSR"."SQL160504163209908"
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
declare v_s_rst varchar(512) default ' AND 1=1 ';
--
if pi_s_TreArea<>'' then
set v_s_rst =
case
when pi_s_TreLevel='1'
then ' '
when pi_s_TreLevel='2'
then ' AND a.s_OfProvTreCodeDebt in ( '||pi_s_TreArea ||' ) '
when pi_s_TreLevel='3'
then
' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt and b.s_trecode in ( '
||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS
CHAR(4))||' '
when pi_s_TreLevel='4'
then
' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt AND a.s_OfCountyTreCodeDebt = b.s_OfCountyTreCodeDebt and b.s_trecode in ( '
||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS
CHAR(4))||' '
else
' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt AND a.s_OfCountyTreCodeDebt = b.s_OfCountyTreCodeDebt and a.s_oftrecode = b.s_trecode and b.s_trecode in ( '
||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS CHAR
(4))||' '
end;
--
end if;
--
return v_s_rst;
--
END;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","TMISUSR";
CREATE FUNCTION "TMISUSR"."F_DEBTGETTRELIST"
("PI_I_YEAR" INTEGER,
"PI_S_TREAREA" VARCHAR(1024),
"PI_S_TRELEVEL" CHARACTER(1)
)
RETURNS VARCHAR(1024)
SPECIFIC "TMISUSR"."SQL160504163209908"
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
declare v_s_rst varchar(512) default ' AND 1=1 ';
--
if pi_s_TreArea<>'' then
set v_s_rst =
case
when pi_s_TreLevel='1'
then ' '
when pi_s_TreLevel='2'
then ' AND a.s_OfProvTreCodeDebt in ( '||pi_s_TreArea ||' ) '
when pi_s_TreLevel='3'
then
' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt and b.s_trecode in ( '
||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS
CHAR(4))||' '
when pi_s_TreLevel='4'
then
' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt AND a.s_OfCountyTreCodeDebt = b.s_OfCountyTreCodeDebt and b.s_trecode in ( '
||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS
CHAR(4))||' '
else
' AND a.s_OfProvTreCodeDebt = b.s_OfProvTreCodeDebt AND a.s_OfCityTreCodeDebt = b.s_OfCityTreCodeDebt AND a.s_OfCountyTreCodeDebt = b.s_OfCountyTreCodeDebt and a.s_oftrecode = b.s_trecode and b.s_trecode in ( '
||PI_s_TreArea ||' ) '||' AND b.i_Year = '||CAST(pi_i_Year AS CHAR
(4))||' '
end;
--
end if;
--
return v_s_rst;
--
END;