Results 1 to 11 of 11

Thread: SQL - 9i

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: SQL - 9i

    Hi

    Can data be extracted from 3 tables in 3 different schemas in a single query?

    The result would be something like:


    Code:
    Datetime        CALLSfromtableA      CALLSfromtableB        CALLSfromtableC
    Regards
    Shajju

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Can data be extracted from 3 tables in 3 different schemas in a single query?
    Yes. It is basic database operation which is called join. As documented for 9iR2: http://download.oracle.com/docs/cd/B...s7.htm#2054014

    Of course, the caller has to have SELECT privilege granted on all 3 tables, as stated in SELECT statement prerequisites: http://download.oracle.com/docs/cd/B...3a.htm#2065648

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks. I did:

    Code:
    select to_char(a.DATETIME,'MM/YYYY') DATETIME
    
    SUM(AB) SUMAB
    ,SUM(CD) SUMCD
    
    FROM SCHEMA1.TABLE_A								SCHEMA2.TABLE_B		
    WHERE 
    to_char(a.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
    AND A.DATETIME=B.DATETIME
    group by to_char(a.DATETIME,'MM/YYYY')
    but the result was compounded.

    Note: There are no other columns in the two tables that I can join on.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Yes, as the join is applied before aggregation, these figures are multiplied by number of matching rows in the opposite table. If you want to aggregate first, rewrite the query to do so, e.g. by aggregating in subqueries:
    Code:
    select nvl(a.DATETIME,b.DATETIME) DATETIME, nvl(a.sumab,0) sumab, nvl(b.sumcd,0) sumcd
    FROM ( select to_char(a.DATETIME,'MM/YYYY') DATETIME, sum(AB) sumab
           from SCHEMA1.TABLE_A a
           WHERE to_char(a.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
           group by to_char(a.DATETIME,'MM/YYYY') ) a full join
         ( select to_char(a.DATETIME,'MM/YYYY') DATETIME, sum(CD) sumcd
           from SCHEMA2.TABLE_B b
           WHERE to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
           group by to_char(a.DATETIME,'MM/YYYY') ) b on a.datetime = b.datetime;
    By the way, if one table would not contain any row from that period, the SELECT would not return anything as the tables are INNER JOINed. You probably should use FULL JOIN to return the row with zero for missing data sum. Of course, if no table contained rows from given period, the result would be empty.

    Another option would be using UNION ALL and PIVOTing the result after. Here the result will be empty (no rows) only when no table contained data (equivalent of FULL JOIN in the query above). Something like this (also untested because of missing test case):
    Code:
    select x.DATETIME, SUM(x.sumAB) SUMAB, SUM(x.sumCD) SUMCD
    FROM ( select to_char(a.DATETIME,'MM/YYYY') DATETIME, sum(AB) sumab, 0 sumcd
           from SCHEMA1.TABLE_A a
           WHERE to_char(a.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
           group by to_char(a.DATETIME,'MM/YYYY')
           union all 
           select to_char(a.DATETIME,'MM/YYYY') DATETIME, 0 sumab, sum(CD) sumcd
           from SCHEMA2.TABLE_B b
           WHERE to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
           group by to_char(a.DATETIME,'MM/YYYY') ) x
    group by x.DATETIME;

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Thanks again.....I don't think FULL JOIN would work for me if zero will be returned if a corresponding value in any table is missing. I tried it and it was taking too long to return a result.

    And to be honest, I don't fullly understand your example for using UNION ALL.

    UNION ALL seems to be adding the SUMs from the two tables.

    I just want to add the entries of a column over a month from two tables and display them side by side.

    I really want to apply this to 3 tables in 3 schemas but for simiplicity sake asked about 2 tables in 2 schemas.

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    I tried the UNION ALL example and if I understood it correctly, it is returning the correct value, however, the value if the same for both SUMAB and SUMCD.

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Thanks again.....I don't think FULL JOIN would work for me if zero will be returned if a corresponding value in any table is missing. I tried it and it was taking too long to return a result.
    Without any quantification it is meaningless. It should not take much longer than your original query. As each subquery return at most one row, FULL JOIN should not be worse than INNER JOIN. If you would be satisfied with no rows when data from that period is missing in at least one table, use it.
    Of course, the slowness may be caused by FULL TABLE scans of both tables because of WHERE condition not allowing using index/partition pruning.
    Quote Originally Posted by shajju View Post
    I really want to apply this to 3 tables in 3 schemas but for simiplicity sake asked about 2 tables in 2 schemas.
    Then (if this would be a problem) you would benefit from using table containing data in all queried periods. You can generate it as described e.g. in this article: http://www.orafaq.com/wiki/Oracle_Ro...tor_Techniques
    Quote Originally Posted by shajju View Post
    I tried the UNION ALL example and if I understood it correctly, it is returning the correct value, however, the value if the same for both SUMAB and SUMCD.
    Are you sure you rewrote it correctly and the tables do not have same data?

  8. #8
    Join Date
    Aug 2008
    Posts
    464

    It works!

    Thanks a lot Flyboy

    The Union All works just fine.

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    Just one more question....

    If I want to run this query for the last 6 months, do I have to UNION ALL

    the query for:

    Code:
    to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
    to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -2), 'mm.yyyy')
    to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -3), 'mm.yyyy')
    to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -4), 'mm.yyyy')
    to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -51), 'mm.yyyy')
    to_char(b.datetime, 'mm.yyyy') = to_char(add_months(sysdate, -6), 'mm.yyyy')

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    If I want to run this query for the last 6 months, do I have to UNION ALL
    That would be one option. If you do not mind typing and hard work with maintaining the code...

    But, maybe it would be better to simply change the WHERE conditions to filter all 6 previous months (supposing, DATETIME has DATE data type):
    Code:
    WHERE b.datetime >= trunc( add_months(sysdate, -6), 'MM' )
      AND b.datetime < trunc( sysdate, 'MM' )

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    Many thanks again

Posting Permissions

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