DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Three quries one report

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    I have to create a report with Three quries,
    Q1, Q2, and Q3

    all three quries use same columns and same tables but different WHARE clauses

    And now this is what I want,

    When user runs the report Parameter forms comes up and asks the user for input, If user selects 1 report will run Q1, and if user selects 2 reports will run Q2 and so on.

    How do I accomplish that.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    u should create parameter (user parameter) for example Rep_number (type - number) and include this parameter in Parameter Form.

    in your queries Q1,2,3 u should white like this:

    Q1 -> where ...... and Rep_number = 1
    Q2 -> where ...... and Rep_number = 2
    Q3 -> where ...... and Rep_number = 3

    and more - u can't link your query - its shoul be independent and place pictures of queries on Data Model one under another.

  3. #3
    Join Date
    Dec 2001
    Posts
    19
    I pulled this off of the Oracle Metalink. If you customize your call to the report (Query 1, Query2, .....) and the AFTERPARAMFORM Trigger, this should work.

    Good Luck.

    Here is the link in case you have access:

    http://metalink.oracle.com/metalink/...T&p_id=37787.1

    This is achieved by employing the use of a lexical parameter in your query and a bind parameter to accept the user's input at runtime.

    In the report outlined in this article the user can either specify a particular employee's name (ENAME) to run the report on or choose not to specify an ENAME and run the report for ALL EMPLOYEES.

    Follow the steps outlined below to develop the report:

    1. Creating the parameters.

    Create a user parameter called P_ENAME
    (Datatype: character, Width: 20)

    /* This will be used to accept the users runtime input */

    Create a user parameter P_WHERECLAUSE
    (Datatype: character, Width: 200, Initial Value: where 1=1)

    /* This is the lexical parameter that will be populated in the AFTERPARAMFORM
    trigger. It has an initial value of 'where 1=1' in order to allow the report
    QUERY to parse.*/

    2. Create the following query in the Data Model that includes the lexical
    parameter.

    Select * from emp
    &P_WHERECLAUSE

    3. Perform a Default Layout. Choose Tabular Style.

    4. Perform a Default Parameter Form Layout from the
    Tools ->Default Parameter Form.. menu.

    Deselect the P_WHERECLAUSE parameter.
    Then choose OK to accept that.

    5. Code the following AFTER PARAMETER FORM trigger.

    function AfterPForm return boolean is
    begin

    /* if the user has actually specified an ename */

    if _ename is not null then

    /* Assign a where clause that includes the users input parameter to the
    lexical _whereclause, and change the input parameter to upper case */

    _whereclause := 'where ename = upper(_ename)';
    end if;
    return(true);
    end;

    6.Run report

    KASH

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    in my mind exists more simple way for this report:

    1. u create only 1 query with 3 queries and UNION
    2. each select in union in WHERE constraction has:

    select .....
    where :P_NB_REPORT = 1 and
    ( ... your where for 1 case ...)
    UNION
    select .....
    where :P_NB_REPORT = 2 and
    ( ... your where for 2 case ...)
    UNION
    select .....
    where :P_NB_REPORT = 2 and
    ( ... your where for 2 case ...)

    and thats it.

    good luck.


  5. #5
    Join Date
    Jul 2001
    Posts
    334
    Hi, I have almost the same problem that in the original post ‘irehman’ having (every thing same except the WHERE clause), But in my case I have 4 different quires, same tables, same where clause but column are different.

    I have to develop this report in D2K I am little confuse that how I should handle this. Is there any way to handle multi query d2k report? As I said consider exactly the original posting of 'irehman'.

    I am used to develop single query report this is first time I have new challenge. Please help to resolve this issue.

    E.g.

    Q1: select ENAME col from emp
    Where job = ‘ANALYST’
    AND option = 1

    Q2: select SAL col from emp
    Where job = ‘ANALYST’;
    AND option = 2

    Q3: select HIREDATE col from emp
    Where job = ‘ANALYST’;
    AND option = 3

    Q4: select EMPNO col from emp
    Where job = ‘ANALYST’;
    AND option = 4

    Do I have to create 4 SQL separate queries under data model; do I have to link each other or what else I can do? User will be entering option (1 or 2 or 3 or 4) from the parameter run time.

    Thanks in advance.

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    for aph:

    U have little bit another report, because datatypes in each query different.

    u can use, for example -

    select ENAME col from emp Where job = ‘ANALYST’
    AND option = 1
    union
    select to_char(SAL, '99999.99') col from emp Where job = ‘ANALYST’;
    AND option = 2
    union
    select to_char(HIREDATE,'mm/dd/rrrr') col from emp Where job = ‘ANALYST’
    AND option = 3
    union
    select to_char(EMPNO) col from emp Where job = ‘ANALYST’
    AND option = 4

  7. #7
    Join Date
    Jul 2001
    Posts
    334
    Hi Shestakov,

    Thanks for this solution, but I have to run one query at a time, depends what value user pass from the parameter it could be 1, 2, 3 or 4. The question is in this union solution how we can define pt_no, I mean how the query will understand that option 2 will execute. Just give me little more detail so that I can start build query based on union.

    Thanks in advance.
    aph

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    1 step
    - in DataModel->UserParameters u should create user parameter:
    for examp. NB_OF_REPORT (type number)
    2 step (depend from your startup procedure :
    -- if u use parameter form :
    --- create parameter form, in parameter form create field (with any names) and link this field with :NB_OF_REPORT parameter
    (in this case had better for u - set type of field in parameter form - static list and type values (1,2,3,4) )
    -- if u use RUN_PRODUCT(...) u should add new parameter in paramer_list
    NB_OF_REPORT as text parameter and set needed value
    -- if u use command line then :
    runrep usr/pwd@db .... NB_OF_REPORT=2 (for example)

    3. little change query:

    select ENAME col from emp Where job = ‘ANALYST’
    AND :NB_OF_REPORT = 1
    union
    select to_char(SAL, '99999.99') col from emp Where job = ‘ANALYST’;
    AND :NB_OF_REPORT = 2
    union
    select to_char(HIREDATE,'mm/dd/rrrr') col from emp Where job = ‘ANALYST’
    AND :NB_OF_REPORT = 3
    union
    select to_char(EMPNO) col from emp Where job = ‘ANALYST’
    AND :NB_OF_REPORT = 4

    lood luck.



  9. #9
    Join Date
    Jul 2001
    Posts
    334
    Hi Shestakov,

    According to your suggestion I have tried to create union query but I am getting this error. Actually the requirement of the report is similar to the below example, I mean I have to use group functions.

    select deptno, sum(sal) from emp
    where _opt_no = 1
    UNION
    select deptno, sum(comm) from emp
    where _opt_no = 2
    group by deptno
    order by deptno

    SQL> /
    select deptno, sum(sal) from emp
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    Is there any other way to get it done, Or do I have to change the query to make it correct.

    Regards,
    aph

  10. #10
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    select deptno, sum(sal) from emp
    where a_opt_no = 1
    group by deptno
    UNION
    select deptno, sum(comm) from emp
    where a_opt_no = 2
    group by deptno
    order by deptno
    /

    because GROUP BY relates for each select in UNION.

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