Uderc programming article aggregator sites

Advertisement

how to problem solve adf issue in the mx340 model

Advertisement

the missing thing in your example is the fact, that if only one value is selected, the parameter has exact this value like BOSTON. If you choose more than one value, the parameter includes the *'*, so that it looks like *'BOSTON','NEW YORK'*. So you need to check in the package, if there's a *,* in the parameter or not. If yes there's more than one value, if not it's only one value or it's null.

So change your package to (you need to expand your variables)

create or replace package bip_departments_2_parameters

as

p_dep_2_param varchar2(1000);

p_loc_1_param varchar2(1000);

p_where_clause varchar2(1000);

function beforereporttrigger

return boolean;

end bip_departments_2_parameters;

create or replace package body bip_departments_2_parameters

as

function beforereporttrigger

return boolean

is

l_return boolean := true;

begin

p_where_clause := ' ';

if p_dep_2_param is not null then

if instr(p_dep_2_param,',')>0 then

p_where_clause := 'WHERE DNAME in ('||p_dep_2_param||')';

else

p_where_clause := 'WHERE DNAME = '''||p_dep_2_param||'''';

end if;

if p_loc_1_param is not null then

if instr(p_loc_1_param,',')>0 then

p_where_clause := p_where_clause || ' AND LOC IN ('||p_loc_1_param||')';

else

p_where_clause := p_where_clause || ' AND LOC = '''||p_loc_1_param||'''';

end if;

end if;

else

if p_loc_1_param is not null then

if instr(p_loc_1_param,',')>0 then

p_where_clause := p_where_clause || 'WHERE LOC in ('||p_loc_1_param||')';

else

p_where_clause := p_where_clause || 'WHERE LOC = '''||p_loc_1_param||'''';

end if;

end if;

end if;

return (l_return);

end beforereporttrigger;

end bip_departments_2_parameters;

I've written a similar example at http://www.oracle.com/global/de/community/bip/tipps/Dynamische_Queries/index.html ... but it's in german.

Regards

Rainer