Home » Developer & Programmer » Reports & Discoverer » One parameter with a "IN" clause
One parameter with a "IN" clause [message #433306] Tue, 01 December 2009 06:52 Go to next message
randritsy
Messages: 20
Registered: June 2007
Location: Dago
Junior Member
hi,
I have a query like this
select sum(A), id_a from t1
where id_a in ('10','11','12','13')
group by id_a;
And i would like to create a parameter, one parameter that can include this in clauses above. this "In" is variable, it's may be ('10','11') or ('10','11','12','13','14','15').
As i need that the user will type the condition in bracket or another solution. but is it possible to create a one user parameter for using this condition above.
Re: One parameter with a "IN" clause [message #433312 is a reply to message #433306] Tue, 01 December 2009 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to use a lexical parameter
Re: One parameter with a "IN" clause [message #433362 is a reply to message #433312] Tue, 01 December 2009 23:10 Go to previous messageGo to next message
randritsy
Messages: 20
Registered: June 2007
Location: Dago
Junior Member
What do you mean about lexical parameter.
I have create a parameter that the user type for example ('10','11','12','13') but can't work. Help me please if someone have a solution
thanks
Re: One parameter with a "IN" clause [message #433392 is a reply to message #433362] Wed, 02 December 2009 01:26 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
randritsy wrote on Wed, 02 December 2009 10:40
What do you mean about lexical parameter.


Read about the lexical parameters in this.

Google what are lexical parameters!

Or just type Lexical references in your report builder help topics it will give so many no of examples ...

Good luck

sriram. Smile
Re: One parameter with a "IN" clause [message #433419 is a reply to message #433392] Wed, 02 December 2009 04:45 Go to previous messageGo to next message
randritsy
Messages: 20
Registered: June 2007
Location: Dago
Junior Member
I have already used this lexical reference but wiht a sql statement but not with report builder.
but i never used a lexical with a where clause. I've tried but don't work.
Can you help me how manipualte my statement above with lexical parameter.

I have done like this
"select sum(A), id_a from t1
&where_clause ---(instead of where id_a in ('10','11','12','13'))
group by id_a";
Re: One parameter with a "IN" clause [message #433420 is a reply to message #433306] Wed, 02 December 2009 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
What you've posted looks ok. What happened when you tried it? Did you get an error message?
Re: One parameter with a "IN" clause [message #433435 is a reply to message #433419] Wed, 02 December 2009 05:32 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
just type Lexical references in your report builder help topics it will give so many no of examples ...


sriram Smile
Re: One parameter with a "IN" clause [message #433437 is a reply to message #433420] Wed, 02 December 2009 05:37 Go to previous messageGo to next message
randritsy
Messages: 20
Registered: June 2007
Location: Dago
Junior Member
Yes it's work on SQL PLUS.
but don't work with oracle report builder.
Error ora-00936 missing expression ==> GROUP BY
Re: One parameter with a "IN" clause [message #433442 is a reply to message #433306] Wed, 02 December 2009 05:44 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
What exactly have you put in the report builder query (use code tags - see the orafaq forum guide if you're not sure how)?
What exactly have you put in the lexical parameter?
Re: One parameter with a "IN" clause [message #433641 is a reply to message #433306] Thu, 03 December 2009 07:42 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, here it is, a sample report based on Scott's schema.

Using a wizard, I've created a report whose query looks like this:
select deptno, ename, job, sal
from emp
&lex_deptno
Wizard has automatically created a lexical parameter called "lex_deptno".

Then I've manually created user parameter which will be used to enter "condition in bracket" (as you've put it) called "par_in_list", type is Character, length 200.

After Parameter Form trigger looks like this:
function AfterPForm return boolean is
begin
  if :par_in_list is not null then
     :lex_deptno := 'where deptno in ' || :par_in_list;
  end if;
  return (TRUE);
end;

Now run the report: enter parameter values:

./fa/7101/0/

The result is

./fa/7102/0/

That should be all, I guess.
Previous Topic: Can't get plus sign to show up in reports.
Next Topic: REP-0002
Goto Forum:
  


Current Time: Sun Dec 11 02:17:57 CST 2016

Total time taken to generate the page: 0.09196 seconds