| One parameter with a "IN" clause [message #433306] |
Tue, 01 December 2009 06:52  |
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  |
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 |
 |
Littlefoot
Messages: 21823
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
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:
The result is
That should be all, I guess.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
