Home » Developer & Programmer » Reports & Discoverer » problem with lexical parameter
problem with lexical parameter [message #339158] Wed, 06 August 2008 23:30 Go to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Hi every one

I have problem with lexical reference

I have select statment as bellow
SELECT DISTINCT A.BRNCD BRNCD1
              ,A.FUNDCD FUNDCD1
FROM LN01COMMON A, LN01MAST B
WHERE A.BRNCD >= :V_FROMBR
AND A.BRNCD <= :V_TOBR
AND A.BRNCD = B.BRNCD
AND A.MODCD = B.MODCD
AND B.ACSTS IN ('A','R','L','2','V')
&FUND_CD
&BUMI


AS user parameter I have
FUND_CD and BUMI -->lexical parameter
v_fundcd and v_bumi --> user input

ON After parameter trigger
 IF :V_BUMI = 'ALL' THEN 
     :BUMI := '';
  ELSE
     :BUMI := 'AND GET_OTHDATA.GET_CHAR(B.CIFKEY,''CB'',''BUMI'') ='||''''||:V_BUMI||'''';
  END IF;

  IF :V_FUNDCD = 'ALL' THEN 
     :FUNDCD:= '';
  ELSE 
     :FUNDCD:= 'AND A.FUNDCD = '||''''||:V_FUNDCD||'''';
  END IF;



the problem is when user enter ALL for V_FUNDCD it dosent show any data on server but at report builder it shows me the data.
I guess IF :V_FUNDCD = 'ALL' THEN has a problem.unlike v_bumi
:FUNDCD:= '';
which i use the same method.

Thanx in advance
Re: problem with lexical parameter [message #339172 is a reply to message #339158] Thu, 07 August 2008 00:07 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

change :FUNDCD:= '' with :FUNDCD:= '1=1' to get all data.
Re: problem with lexical parameter [message #339174 is a reply to message #339158] Thu, 07 August 2008 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to change lexical parameters' values - instead of
:BUMI := '';
make it
:BUMI := ' and 1 = 1';

The same goes for "fundcd".

Also, in order to know what happens, create two fields in Paper Layout Editor; their sources should be lexical parameters. Doing so, you'll actually SEE how these parameters look like (i.e. you wouldn't have to guess).
Re: problem with lexical parameter [message #339187 is a reply to message #339174] Thu, 07 August 2008 00:43 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Hi,

As you said i change the value to ' and 1 = 1' insted of ''.
however,i cant see the data on created report.
In addition, I created two fields in Paper Layout Editor and their sources are lexical parameters.
When report is created with passing 'ALL' for both parameters ,I can see there are two values and 1=1.but still not able to see the data on server unlike report builder.

Any solution ?Sad

Thanx and regards
Re: problem with lexical parameter [message #339191 is a reply to message #339187] Thu, 07 August 2008 01:03 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

strange. Try to omit that lexical parameters, can you see your expected data?
Re: problem with lexical parameter [message #339195 is a reply to message #339191] Thu, 07 August 2008 01:13 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
do ya mean i only delete lexicals from select statement?

I did..same thing..still can see and 1=1 ..but not data Confused
still can see data on report builder
Re: problem with lexical parameter [message #339215 is a reply to message #339195] Thu, 07 August 2008 01:51 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But of course that you see "and 1 = 1"! You did not delete a lexical parameter, you have just removed it from a query!

Quote:
still can see data on report builder

Do you connect to the same schema while running the report from Reports Builder and when it is on a server? Did you put the report into the right directory (i.e. perhaps you are still executing the "old" report which doesn't have "and 1 = 1")?
Re: problem with lexical parameter [message #339270 is a reply to message #339215] Thu, 07 August 2008 03:38 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Dear Littlefoot,

every time i run the report on the server i put some kind of sign on paper layout or message that shows me i use the latesr version.

They are conected to the same schema.. Shocked

I really dont know what else i can do Sad
Re: problem with lexical parameter [message #339292 is a reply to message #339270] Thu, 07 August 2008 04:07 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Hi again,

I change the method abit.Insted of 2 lexical parameters i created one as &VAR and in the after parameter trigger i have
 
 IF :V_BUMI <> 'ALL' THEN 
       :VAR := ' AND GET_OTHDATA.GET_CHAR(B.CIFKEY,''CB'',''BUMI'')='||''''||:V_BUMI||'''';  
  END IF;
 
  IF :V_FUNDCD <>'ALL' THEN 
     :VAR := :VAR||' '|| 'AND A.FUNDCD = '||''''||:V_FUNDCD||'''';
  END IF;

  IF :VAR IS NULL THEN
  	:VAR := ' and 1 = 1';
  END IF;


when i run a report on server and pas in 'ALL' for both parameters
the value of var will be printed on created report which is ' and 1 = 1' but no data Shocked
Re: problem with lexical parameter [message #339325 is a reply to message #339292] Thu, 07 August 2008 05:11 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
every time i run the report on the server i put some kind of sign on paper layout or message that shows me i use the latest version.
That really is a good idea.

So the situation is as follows:
  • when you run the report in Reports Builder, you enter "ALL" as both parameter values, and everything works fine (i.e. you see the data)
  • when the same report is run on the server, there's no data at all
Who tests the report on server? Is it you, or one of your users? Is the problem, perhaps, in letter case (for example, "all" instead of "ALL")?
Re: problem with lexical parameter [message #339333 is a reply to message #339325] Thu, 07 August 2008 05:22 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Hi,

Yes.You got it right Smile

I am the one running on the server.the user complained from the same problem so i am trying to fix it.
when user enter the parameter,we enforce user to input uppercase letters.Thus,he dosnt have chance to input 'all'.

Thanx and regards
Re: problem with lexical parameter [message #339353 is a reply to message #339333] Thu, 07 August 2008 06:06 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, it just doesn't make any sense ... the same query (report), run from two different places, connected to the same database retrieves different results. Amazing! Unfortunately, I've got nothing new to say at the moment, but will return if I get another idea.
Re: problem with lexical parameter [message #339582 is a reply to message #339353] Thu, 07 August 2008 21:12 Go to previous messageGo to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

What are those table, created in-haouse? or is it bought by package such as tables of oracle apps? Check also the grants of the table.

Hope this help.
Wency
Re: problem with lexical parameter [message #339648 is a reply to message #339353] Fri, 08 August 2008 01:17 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's another one: did you try to "Compile All" (Ctrl + Shift + K)?
Re: problem with lexical parameter [message #339718 is a reply to message #339648] Fri, 08 August 2008 03:36 Go to previous messageGo to next message
sara110
Messages: 17
Registered: October 2007
Junior Member
Hi,

Yes .I have compiled all.Should'nt I?
The tables are created in-haouse.They dont have any grants.

Regards,

Sara

[Updated on: Fri, 08 August 2008 03:43]

Report message to a moderator

Re: problem with lexical parameter [message #339721 is a reply to message #339718] Fri, 08 August 2008 04:00 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Sara
I have compiled all.Shouldn't I?

That's fine. Some people just compile incremental (Ctrl + K), and compiling "all" helps. Sometimes. Not in your case. Unfortunately.
Previous Topic: caling procedure in report
Next Topic: WebLayout not running properly
Goto Forum:
  


Current Time: Wed Dec 07 16:36:52 CST 2016

Total time taken to generate the page: 0.09585 seconds