Home » Developer & Programmer » Reports & Discoverer » Lexcial Parameter inside PLSQL (in Formula Column)
Lexcial Parameter inside PLSQL (in Formula Column) [message #448779] Thu, 25 March 2010 03:17 Go to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello,

I am using &p to have a parameter that is going to be changed dynamically(lexical parameter). I had main query and i want to have a formula column which gets the value from the procedure. the procedure should take the parameters that i used in the main query (i mean the lexical parameters i used in the main query). but it is not possible to used lexical parameters in pl/sql. Thanks.

Karthik
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #448799 is a reply to message #448779] Thu, 25 March 2010 04:33 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you provide some more information? How does lexical parameter look like? Where do you create it, and how? What code do you use in a formula column (and where should lexical parameter be used)?
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453077 is a reply to message #448799] Tue, 27 April 2010 02:05 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

hi,

as seen from one of the thread, i tried to use the lexi parameter in my reports 6i. this is my query

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

could any one rectify the query and the trigger to get the result
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453098 is a reply to message #453077] Tue, 27 April 2010 02:54 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
when i compile it says sql command is not ended properly

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

Compile? As far as I can see, code seems to be OK. I'd expect it to complain when you run the report.

How does "p_clsfn" look like? You know that IN requires brackets, such as
where mjh_cd in (1, 2, 3, 4)
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453119 is a reply to message #453098] Tue, 27 April 2010 04:05 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

hi,

thanks for the reply.

i did use it within brackets only, like the way you said. but if remove the where condition in the query and just include only &lex_clsfn it works, but for me it gives all the values from the table and it does not allows me to filter the values only for a particular year.
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453125 is a reply to message #453119] Tue, 27 April 2010 04:56 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Heh, didn't notice ...

This is part of your query:
where mjh_cd=:p_clsfn and fin_yr=:p_fin_yr
&lex_clsfn

This is lexical parameter's value:
:lex_clsfn := 'where mjh_cd in' || :p_clsfn;


It means that a complete WHERE clause looks like this:
where mjh_cd=:p_clsfn and fin_yr=:p_fin_yr
where mjh_cd in || :p_clsfn

You can't have 2 WHEREs! Lexical parameter should start with "and", not "where".
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453162 is a reply to message #453125] Tue, 27 April 2010 08:06 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

hi,

yes sir, i noticed it also, but i don't know how to accomodate both into the where query. could you pl guide me
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453163 is a reply to message #453162] Tue, 27 April 2010 08:19 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
lacchhii wrote on Tue, 27 April 2010 18:36
hi,

yes sir, i noticed it also, but i don't know how to accomodate both into the where query. could you pl guide me


 :lex_clsfn := ' mjh_cd in' || :p_clsfn;



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 &lex_clsfn  and fin_yr=:p_fin_yr
group by fin_yr,acc_mn,mjh_cd,smjh_cd,mih_cd
order by mjh_cd,smjh_cd,mih_cd


sriram Smile

[Updated on: Tue, 27 April 2010 08:25]

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453194 is a reply to message #453163] Tue, 27 April 2010 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 20824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Not really.

If "p_clsfn" parameter's value is NULL, lexical parameter will also be NULL and you'll have
WHERE AND fin_yr=:p_fin_yr
which is also invalid.

So, lexical parameter's value should be "AND ..." (instead of "WHERE ...", as it is now).


lacchhii
i don't know how to accomodate both into the where query. could you pl guide me

You must be kidding, aren't you?

If there was
where ...
where ...
how come you can't figure it out by yourself how to modify it to
where ...
and ...

Think!
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453203 is a reply to message #453194] Tue, 27 April 2010 20:21 Go to previous messageGo to next message
lacchhii
Messages: 136
Registered: May 2009
Location: bangalore
Senior Member

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 &lex_clsfn and fin_yr=:p_fin_yr
group by fin_yr,acc_mn,mjh_cd,smjh_cd,mih_cd
order by mjh_cd,smjh_cd,mih_cd


i did use where &lex_clsfn and fin_yr=:p_fin_yr, but it gives an error sql command not ended properly. it is accepting only one where condition and thats why i sought help from here
Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453209 is a reply to message #453203] Tue, 27 April 2010 22:16 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Did you read what little Foot suggested?

If you you this lexi in the where condition like
where &lex_clsfn and fin_yr=:p_fin_yr

from the &lex_clsfn validation trigger you may get
1) a value or
2) null

SO the basic idea is..

  if :p_clsfn is not null then
     :lex_clsfn := 'Your required condition';
  else
  :lex_clsfn := '1=1';
  end if;

Then your query will turn like this


if the value null then it returns
where 1=1 and fin_yr=:p_fin_yr


if the valus is not null then
where 'Your required condition' and fin_yr=:p_fin_yr;


sriram Smile
Previous Topic: PL/SQL query
Next Topic: I NEED TUTORIALs in learning Discoverer ??
Goto Forum:
  


Current Time: Sun Sep 25 00:56:06 CDT 2016

Total time taken to generate the page: 0.09431 seconds