One parameter with a "IN" clause [message #433306] |
Tue, 01 December 2009 06:52 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #433419 is a reply to message #433392] |
Wed, 02 December 2009 04:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #433641 is a reply to message #433306] |
Thu, 03 December 2009 07:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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:
![/forum/fa/7101/0/](/forum/fa/7101/0/)
The result is
![/forum/fa/7102/0/](/forum/fa/7102/0/)
That should be all, I guess.
|
|
|