hi,

I tried to use lexical parameter in one of my rdf report (report 6i)

select fin_yr,acc_mn,mjh_cd,smjh_cd,mih_cd,sum(nvl(CUR_AMT_P,0)-nvl(CUR_AMT_M,0)) Amount
from ta_clsf
where mjh_cd=:p_clsfn and fin_yr=:p_fin_yr
&lex_clsfn
group by fin_yr,acc_mn,mjh_cd,smjh_cd,mih_cd
order by mjh_cd,smjh_cd,mih_cd

Then created a user parameter manually and in validation trigger entered a code like this

function P_clsfnValidTrigger return boolean is
begin
if :p_clsfn is not null then
:lex_clsfn:='where mjh_cd in'||:p_clsfn;
end if;
return (TRUE);
end;

when i compile it says sql command is not ended properly

I am using this lexical parameter to select the values of mjh_cd (column name) like 8443,8050,8295,4201 for a particular year.

I did realise that where mjh_cd=:p_clsfn and fin_yr=:p_fin_yr
&lex_clsfn does gives two where command, but i need to use both the parameter to filter the mjh_cd for a particular year.

if i remove the fin_yr parameter it gives me the values for all the years

could any one suggest rectification to the query and the trigger to get the result

Hi there,
I have spotted something in your query below and the function you have created
select fin_yr,acc_mn,mjh_cd,smjh_cd,mih_cd,sum(nvl(CUR_AMT_P,0)-nvl(CUR_AMT_M,0)) Amount
from ta_clsf
where mjh_cd=:p_clsfn and fin_yr=:p_fin_yr
&lex_clsfn
-- if i replace the lex_clsfn value here it becomes
--where mjh_cd in XYZ
group by fin_yr,acc_mn,mjh_cd,smjh_cd,mih_cd
order by mjh_cd,smjh_cd,mih_cd

function P_clsfnValidTrigger return boolean is
begin
if :p_clsfn is not null then
:lex_clsfn:='where mjh_cd in'||:p_clsfn;
end if;
return (TRUE);
end;

So modify your function with this one
function P_clsfnValidTrigger return boolean is
begin
if :p_clsfn is not null then
:lex_clsfn:='and mjh_cd in'||:p_clsfn;
end if;
return (TRUE);
end;
Hope it works