dynamic where clause in reports
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: dynamic where clause in reports

  1. #1
    Join Date
    Jun 2001
    Posts
    109
    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

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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




  3. #3
    Join Date
    Jun 2001
    Posts
    109
    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.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  5. #5
    Join Date
    Nov 2001
    Posts
    13
    use the concept of LEXICAL PARAMETERS.
    i think it must be
    select * From table &where
    &where = where a=b
    Srinivas Reddy Tatireddy

  6. #6
    Join Date
    Jul 2001
    Posts
    334
    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

  7. #7
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi,

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

  8. #8
    Join Date
    Jun 2001
    Posts
    109
    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

  9. #9
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Oracle Report always accept ALL LIST OF PARAMETERS.
    u can use default value for parameters for solve ur problems.

  10. #10
    Join Date
    Jun 2001
    Posts
    109
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width