dynamic where clause in reports

[saritha]

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

[Shestakov]

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 arameter.CC is null then
:ws := 'cc_field is null';
.....
end;

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

[saritha]

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.

[Shestakov]

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

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

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

[tatireddis]

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

[aph]

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

[balajiyes]

hi,

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

[saritha]

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 arameter1 OR arameter2 or arameter1 AND arameter2 ....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

[Shestakov]

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

[saritha]

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