Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    10

    Unanswered: query to generate report for 2 schemas - Urgent

    hi,
    This the query i used to generate activity report for a single schema

    select days.made
    , c.userid
    , nvl(g.contacts_imported,0) contacts_imported
    , nvl(dc.email_sent,0) email_sent
    , nvl(dc.campaign_sent,0) campaign_sent
    , u.total_users
    from days
    , (select companyid
    , trunc(made) made
    , sum(count) contacts_imported
    from groups
    where trunc(made) between to_date('09/01/2006','mm/dd/yyyy') and
    to_date('10/01/2006','mm/dd/yyyy')
    and format like '%Import%'
    and status like '%Completed%'
    group
    by companyid
    , trunc(made)) g
    , (select companyid
    , trunc(made) made
    , count(*) campaign_sent
    , sum(numbersent) email_sent
    from dm_campaign
    where trunc(made) between to_date('09/01/2006','mm/dd/yyyy') and
    to_date('10/01/2006','mm/dd/yyyy')
    group
    by companyid
    , trunc(made)) dc
    , (select s.clientid companyid
    , s.userid userid
    from client c
    , subscriber s
    where c.clientid = s.clientid
    and c.lastverisigntransactionid is not null
    and c.active = 1
    and s.userid not like 'qpt999%') c
    , (select made
    , sum(count) over(order by made) total_users
    from (select trunc(c.createdate) made
    , count(s.userid) count
    from client c
    , subscriber s
    where c.clientid = s.clientid
    and c.lastverisigntransactionid is not null
    and c.active = 1
    and s.userid not like 'qpt999%'
    group
    by c.createdate)) u
    where (c.companyid = g.companyid
    and c.companyid = dc.companyid
    and days.made = g.made
    and days.made = dc.made
    and days.made = u.made)
    order
    by days.made

    If i have to generate a query for 2 or more schemas how would i rewrite the query

    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    One option would be to log on as another user and run the same query.

    Or, you could GRANT SELECT (as it is a report, primarily designed to fetch data) from all users to the one which is going to run the report; create a view which would, with all other columns, have one to identify data owner which would then be passed as a parameter to your report.

  3. #3
    Join Date
    Sep 2006
    Posts
    10

    re

    Hi,
    Thanks for the reply.
    We have nearly 10 schemas in our database the requirement is to create a consolidate report activity for all the schemas at a give duration in a single report.

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    113
    Grant Select any , reference will be some of the privileges that u shud look for the user used to execute the queries....

    Basically the user should have the permission to select data from objects owned by other users

Posting Permissions

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