OraFAQ Forum: Reports & Discoverer » problem with lexical parameter

Home » Developer & Programmer » Reports & Discoverer » problem with lexical parameter

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

problem with lexical parameter [message #339158]

Wed, 06 August 2008 23:30

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

Report message to a moderator

Re: problem with lexical parameter [message #339172 is a reply to message #339158]

Thu, 07 August 2008 00:07

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

change :FUNDCD:= '' with :FUNDCD:= '1=1' to get all data.

Report message to a moderator

Re: problem with lexical parameter [message #339174 is a reply to message #339158]

Thu, 07 August 2008 00:09

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

Report message to a moderator

Re: problem with lexical parameter [message #339187 is a reply to message #339174]

Thu, 07 August 2008 00:43

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

Report message to a moderator

Re: problem with lexical parameter [message #339191 is a reply to message #339187]

Thu, 07 August 2008 01:03

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

strange. Try to omit that lexical parameters, can you see your expected data?

Report message to a moderator

Re: problem with lexical parameter [message #339195 is a reply to message #339191]

Thu, 07 August 2008 01:13

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

Report message to a moderator

Re: problem with lexical parameter [message #339215 is a reply to message #339195]

Thu, 07 August 2008 01:51

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

Report message to a moderator

Re: problem with lexical parameter [message #339270 is a reply to message #339215]

Thu, 07 August 2008 03:38

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

Report message to a moderator

Re: problem with lexical parameter [message #339292 is a reply to message #339270]

Thu, 07 August 2008 04:07

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

Report message to a moderator

Re: problem with lexical parameter [message #339325 is a reply to message #339292]

Thu, 07 August 2008 05:11

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

Report message to a moderator

Re: problem with lexical parameter [message #339333 is a reply to message #339325]

Thu, 07 August 2008 05:22

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

Report message to a moderator

Re: problem with lexical parameter [message #339353 is a reply to message #339333]

Thu, 07 August 2008 06:06

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

Report message to a moderator

Re: problem with lexical parameter [message #339582 is a reply to message #339353]

Thu, 07 August 2008 21:12

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

Report message to a moderator

Re: problem with lexical parameter [message #339648 is a reply to message #339353]

Fri, 08 August 2008 01:17

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Here's another one: did you try to "Compile All" (Ctrl + Shift + K)?

Report message to a moderator

Re: problem with lexical parameter [message #339718 is a reply to message #339648]

Fri, 08 August 2008 03:36

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

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

Report message to a moderator

Previous Topic:	caling procedure in report
Next Topic:	WebLayout not running properly

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 22:24:50 CDT 2024