OraFAQ Forum: Reports & Discoverer » Lexcial Parameter inside PLSQL (in Formula Column)

Home » Developer & Programmer » Reports & Discoverer » Lexcial Parameter inside PLSQL (in Formula Column)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lexcial Parameter inside PLSQL (in Formula Column) [message #448779]

Thu, 25 March 2010 03:17

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

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #448799 is a reply to message #448779]

Thu, 25 March 2010 04:33

Littlefoot
Messages: 21808
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)?

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453077 is a reply to message #448799]

Tue, 27 April 2010 02:05

lacchhii
Messages: 151
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

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453098 is a reply to message #453077]

Tue, 27 April 2010 02:54

Littlefoot
Messages: 21808
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)

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453119 is a reply to message #453098]

Tue, 27 April 2010 04:05

lacchhii
Messages: 151
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.

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453125 is a reply to message #453119]

Tue, 27 April 2010 04:56

Littlefoot
Messages: 21808
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".

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453162 is a reply to message #453125]

Tue, 27 April 2010 08:06

lacchhii
Messages: 151
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

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453163 is a reply to message #453162]

Tue, 27 April 2010 08:19

ramoradba
Messages: 2456
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

[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

Littlefoot
Messages: 21808
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!

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453203 is a reply to message #453194]

Tue, 27 April 2010 20:21

lacchhii
Messages: 151
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

Report message to a moderator

Re: Lexcial Parameter inside PLSQL (in Formula Column) [message #453209 is a reply to message #453203]

Tue, 27 April 2010 22:16

ramoradba
Messages: 2456
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

Report message to a moderator

Previous Topic:	PL/SQL query
Next Topic:	I NEED TUTORIALs in learning Discoverer ??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 10:30:10 CDT 2024