Re: Using DECODE in stored function
Date: 1996/01/22
Message-ID: <4e0fp4$kb4_at_nntp.interaccess.com>
AMARENDRA B NETTEM <nettama_at_charlie.acc.iit.edu> wrote:
>for decode function, you have to use select statement.
>this might work:
>select decode(.........) into xxxxx
>from dual;
>hope this helps
>AMARENDRA >http://www.iit.edu/~nettama
create or replace package aa_stat as
function value
(p_column varchar2
,p_period_id number
,p_entity_id number
,p_rpt_ln_id number) return number;
--
pragma restrict_references(value,WNDS,WNPS);
--
function value
(p_column varchar2
,p_period_id number
,p_entity_id number
,p_rpt_def_id number
,p_rpt_def_ln_seq number) return number;
--
pragma restrict_references(value,WNDS,WNPS);
end aa_stat;
/
create or replace package body aa_stat as
function query
(p_column varchar2
,p_period_id number
,p_entity_id number
,p_rpt_ln_id number) return number is
l_column varchar2(30) := upper(p_column);
--
cursor c_ytd is
select
nvl(sum(y.ytd_act),0) ytd_act
,nvl(sum(y.t12_act),0) t12_act
,nvl(sum(y.ytd_pla),0) ytd_pla
from
mrs_ytd_amounts y
,mrs_rpt_ln_hier_denorm rlhd
,mrs_period p
where
y.tran_type_id < 3
and y.period_id = decode(p_column
,'LYR_YTD_ACT',p.lyr_period_id
, p.period_id
)
and p.period_id = p_period_id
and y.entity_id = p_entity_id
and y.rpt_ln_id = rlhd.rpt_ln_id
and rlhd.par_rpt_ln_id = p_rpt_ln_id
;
cursor c_mtd is
select
nvl(sum(m.jan_act),0) jan_act
,nvl(sum(m.feb_act),0) feb_act
,nvl(sum(m.mar_act),0) mar_act
,nvl(sum(m.apr_act),0) apr_act
,nvl(sum(m.may_act),0) may_act
,nvl(sum(m.jun_act),0) jun_act
,nvl(sum(m.jul_act),0) jul_act
,nvl(sum(m.aug_act),0) aug_act
,nvl(sum(m.sep_act),0) sep_act
,nvl(sum(m.oct_act),0) oct_act
,nvl(sum(m.nov_act),0) nov_act
,nvl(sum(m.dec_act),0) dec_act
from
mrs_mtd_amounts m
,mrs_rpt_ln_hier_denorm rlhd
where
m.tran_type_id < 3
and m.year_id = p_period_id
and m.entity_id = p_entity_id
and m.rpt_ln_id = rlhd.rpt_ln_id
and rlhd.par_rpt_ln_id = p_rpt_ln_id
;
begin
if l_column = 'LYR_YTD_ACT' then
for c1_rec in c_ytd loop
return(c1_rec.ytd_act);
end loop;
elsif l_column in ('YTD_ACT','T12_ACT','YTD_PLA') then
for c1_rec in c_ytd loop
if l_column = 'YTD_ACT' then
return(c1_rec.ytd_act);
elsif l_column = 'T12_ACT' then
return(c1_rec.t12_act);
elsif l_column = 'YTD_PLA' then
return(c1_rec.ytd_pla);
end if;
end loop;
else
for c1_rec in c_mtd loop
if l_column = 'JAN_ACT' then
return(c1_rec.jan_act);
elsif l_column = 'FEB_ACT' then
return(c1_rec.feb_act);
elsif l_column = 'MAR_ACT' then
return(c1_rec.mar_act);
elsif l_column = 'APR_ACT' then
return(c1_rec.apr_act);
elsif l_column = 'MAY_ACT' then
return(c1_rec.may_act);
elsif l_column = 'JUN_ACT' then
return(c1_rec.jun_act);
elsif l_column = 'JUL_ACT' then
return(c1_rec.jul_act);
elsif l_column = 'AUG_ACT' then
return(c1_rec.aug_act);
elsif l_column = 'SEP_ACT' then
return(c1_rec.sep_act);
elsif l_column = 'OCT_ACT' then
return(c1_rec.oct_act);
elsif l_column = 'NOV_ACT' then
return(c1_rec.nov_act);
elsif l_column = 'DEC_ACT' then
return(c1_rec.dec_act);
end if;
end loop;
end if;
--
return(to_number(null));
end query;
--
function compute
(p_column varchar2
,p_period_id number
,p_entity_id number
,p_rpt_ln_id number
,p_sum_grp char) return number is
begin
if p_sum_grp is null then
declare
rpt_ln_value number := 0;
sum_grp_value number := 0;
--
l_abs_fl boolean := mrs_rpt_ln_lib.abs_fl(p_rpt_ln_id);
--
cursor c1 is
select *
from
mrs_rpt_ln_alg_grp
where
rpt_ln_id = p_rpt_ln_id
order by
sum_grp_seq desc
;
begin
for c1_rec in c1 loop
sum_grp_value := compute(p_column
,p_period_id
,p_entity_id
,p_rpt_ln_id
,c1_rec.sum_grp);
--
if l_abs_fl then
sum_grp_value := abs(sum_grp_value);
end if;
--
if nvl(sum_grp_value,0) != 0 then
if nvl(c1_rec.sum_grp_oper,'x') in ('x',' ','+') then
rpt_ln_value := rpt_ln_value + sum_grp_value;
elsif c1_rec.sum_grp_oper = '-' then
rpt_ln_value := rpt_ln_value - sum_grp_value;
elsif c1_rec.sum_grp_oper = '*' then
rpt_ln_value := rpt_ln_value * sum_grp_value;
elsif c1_rec.sum_grp_oper = '/' then
rpt_ln_value := rpt_ln_value / sum_grp_value;
end if;
end if;
end loop;
--
return(rpt_ln_value);
end;
else
declare
return_value number;
sum_grp_value number := 0;
--
cursor c1 is
select *
from
mrs_rpt_ln_sum_grp
where
rpt_ln_id = p_rpt_ln_id
and sum_grp = p_sum_grp
;
begin
for c1_rec in c1 loop
if mrs_rpt_ln_lib.computed_stat(c1_rec.sum_grp_rpt_ln_id)
then
return_value := compute(p_column
,p_period_id
,p_entity_id
,c1_rec.sum_grp_rpt_ln_id
,null);
else
return_value := query(p_column
,p_period_id
,p_entity_id
,c1_rec.sum_grp_rpt_ln_id);
end if;
--
sum_grp_value := sum_grp_value + return_value;
end loop;
--
return(sum_grp_value);
end;
end if;
end compute;
--
function value
(p_column varchar2
,p_period_id number
,p_entity_id number
,p_rpt_ln_id number) return number is
begin
return(round(compute(p_column
,p_period_id
,p_entity_id
,p_rpt_ln_id
,null
)
,4
)
);
end value;
--
function value
(p_column varchar2
,p_period_id number
,p_entity_id number
,p_rpt_def_id number
,p_rpt_def_ln_seq number) return number is
l_value number := null;
--
cursor c1 is
select
rpt_ln_id
,rdl_amt_edt_chr_id
,rdl_amt_num_sign_id
from
mrs_rpt_def_ln
where
rpt_def_id = p_rpt_def_id
and rpt_def_ln_seq = p_rpt_def_ln_seq;
begin
for c1_rec in c1 loop
l_value := compute(p_column
,p_period_id
,p_entity_id
,c1_rec.rpt_ln_id
,null);
--
if c1_rec.rdl_amt_edt_chr_id in (5,6) then
l_value := l_value * 100;
end if;
--
if c1_rec.rdl_amt_num_sign_id = 1 then
l_value := abs(l_value);
elsif c1_rec.rdl_amt_num_sign_id = 2 then
l_value := l_value * -1;
end if;
end loop;
--
return(round(l_value,4));
end value;
end aa_stat;
/
Package created.
create or replace package body aa_stat as
*
ERROR at line 1:
ORA-01403: no data found
This error is being caused by the decode statement in cursor c_ytd. When I create this function as a stand-alone stored function I don't get the error and the cursor behaves correctly. I also don't get the error when I run the code as an anonymous PL/SQL block in SQL*Plus.
And as best I can recall, this worked fine in package functions in release 7.0.14 of the database for Netware. We use the DECODE statement in the WHERE clause all the time. Perfectly legal.
Mike Received on Mon Jan 22 1996 - 00:00:00 CET