dynamic where clause in reports

Hello all,
I have a report whoch has about 7 parameters passed by user so I need change my query accordingly whatever they pass as parameters to the reports .My where clause for the report's main query needs to be changed dynamically according to the paramenters they pass.

So how cna I dynamically change my where clause for a report?

Please let me know.

thanks
Saritha

U can do following:

1. create parameter (for example ws --> char(200) )
2. in AFTER_PARAMETER_FORM trigger u may build WHERE string

for example:
begin
if :parameter.CC is null then
:ws := 'cc_field is null';
.....
end;

3. in data model u can use:
select .... from ... where &ws

I know I did the same way.

Its giving me error " missing expression" when I am saying

select ......from.....where &p_whereclause;

P_whereclasue is a user created parameter whose value gets changed according to what user pass as parameters.

Try:
a) print :p_whereclause using any ways.
(for examlpe select :p_whereclause from dual; in data model)
b) :p_whereclause := '1=1';

select ......from.....where &p_whereclause;

because: " missing expression" seems like sintax error in where.

use the concept of LEXICAL PARAMETERS.
i think it must be
select * From table &where
&where = where a=b

Hi Sarita,
The easiest way is use UNION then you don't need any dynamic solution.

For example if you want to run this report by deptno 10 then enter 10 else 20 or 30 depend how you want.

select ename, sal from emp
where :deptno = 10
UNION
select ename, sal from emp
where :deptno = 20
UNION
select ename, sal from emp
where :deptno = 30

aph

hi,

if u r using a procedure then u can use the execute immediate stuff.
if iam wrong correct me
thnx,

I can use lexical parameter for dynamic where clause but
according to parameters passed to the report there are many many dynamic where clauses so my question is , Is there anyway in which we can avoid checking many conditions and setting dynamic where clause.

For ex: I have about parameters which are passed to a report out of 6 the users can pass :parameter1 OR :parameter2 or :parameter1 AND :parameter2 ....so on

Do I have to check all those possible conditions to set my dynamic whereclause or is there any way to do that.

Thanks
Saritha

Oracle Report always accept ALL LIST OF PARAMETERS.
u can use default value for parameters for solve ur problems.

I am talking about the user parameters not system parameters. I ahve about 6 different parameter in Prameter form where the user can pass any combination of parameters.
for 6 parameters there are many possible combinations. My report query is based on the parameters they pass to the report.
Do I have to check all the possible combinations in my after_parameter_trigger and assign my where clause dynamically. Is there any other way to do that?

Thanks
Saritha

I am talking about all parameters user and system.
User can pass some (not all) user parameters, but Report accept ALL PARAMETERS.
------------------------------------------
In user parameter property palette type for each parameter default
(initial) value. then in after_parameter_form trigger u can check this
(default) value with value in :USER_PARAMETER and make desigion,
what u can do in &WHERE lexical variable.

This is way in ur situation.
Methods how u can write ur code in after_parameter_form trigger may be copmpletely defferent.

Sarita,
Please check post No. 6
Thanks

I am doing same thig.
You just constuct sql stament , storing in variable in PL/SQL
e.g.
sql = 'select x, y,z from tab1 where'

if p1 = 'xyz' then
p1cond = 'x = '''|| p1 ||'''
elsif
......

endif;

if p2 = 'p2x' then
p2cond= 'z = '''|| p2 ||'''
end if;

where_clause = p1 || ' and ' || p2

sql_stmt = sql || where_caluse

thanks for the help. I understood to set the dyanamic where clause but for some reasons it is giving me "Invalid column name Ora-00904 . This happens only when I enter value for particular parameter.
Any idea?


Thanks
saritha