Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: joining 2 tables...

    Hi

    I'd like to join 2 tables in the same db but in different schemas. Kindly advise?

    Someone suggested using a 'Join all' but am not sure of the syntax and the internet is not helping much.

    I've attached a file containing the queries called 'joining 2 queries'.

    Regards
    Shajju
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    Hi

    I'd like to join 2 tables in the same db but in different schemas. Kindly advise?

    Someone suggested using a 'Join all' but am not sure of the syntax and the internet is not helping much.

    I've attached a file containing the queries called 'joining 2 queries'.

    Regards
    Shajju
    Tables or queries? The requirement is totally unclear - it is possible to "join" queries in many ways, but, as you did not specify any details, I refuse to guess them.

    First query returns columns MSC, DATETIME, TOTAL_ATTACHED, OMHED, NAHED, INTER
    Second query returns columns datetime, OBJECTID, sum(total_attached)
    Which columns shall be included in final query and what shall be their relationship with the output of given queries? Or it does not matter and you will be satisfied with any output?

  3. #3
    Join Date
    Aug 2008
    Posts
    464

    Joining 2 tables

    The output of the first query gives columns:
    MSC, DATETIME, TOTAL_ATTACHED, OMHED, NAHED, INTER

    The output of the second query gives 3 rows under the columns OBJECTID, sum(total_attached). The 3 rows being for OM_SUB, NW-SUB and INT_SUB.

    After joining them, I'd like to add OM_SUB to OMHED, NW-SUB to NAHED and INT_SUB to INTER. Don't mind what the columns are called in the output.

    And also add sum(total_attached) to TOTAL_ATTACHED. I hope I've explained better this time.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    The output of the first query gives columns:
    MSC, DATETIME, TOTAL_ATTACHED, OMHED, NAHED, INTER

    The output of the second query gives 3 rows under the columns OBJECTID, sum(total_attached). The 3 rows being for OM_SUB, NW-SUB and INT_SUB.

    After joining them, I'd like to add OM_SUB to OMHED, NW-SUB to NAHED and INT_SUB to INTER. Don't mind what the columns are called in the output.

    And also add sum(total_attached) to TOTAL_ATTACHED. I hope I've explained better this time.
    Firstly, the second query contains TIMESTAMP column in the attached code, so I will count with it and match it with the first query.
    Secondly, the resultset of the second query is unsuitable; it should be transformed to one row for each TIMESTAMP. The technique is called pivoting and described in many places and looks like this:
    Code:
    select DATETIME,
      sum(total_attached) total_attached,
      sum(decode( OBJECTID, 'OM_SUB', total_attached)) OMSUB,
      sum(decode( OBJECTID, 'NW-SUB', total_attached)) NWSUB,
      sum(decode( OBJECTID, 'INT_SUB', total_attached)) INTSUB
    from ( <the inner query> )
    group by DATETIME
    (by the way, the OBJECTID literals do not match with the ones used in the attached query).
    You may pivot in the inner SELECT (do not use OBJECTID column at all); I will let it on you.

    Then, simply JOIN the queries on TIMESTAMP.
    Code:
    SELECT t1.MSC, t1.DATETIME,
      t1.total_attached + t2.total_attached total_attached,
      t1.omhed + t2.omsub om,
      t1.nahed + t2.nwsub na,
      t1.inter + t2.intsub int
    FROM (<first query>) t1,
      (<second query>) t2
    WHERE t1.datetime = t2.datetime;
    (supposing you want to add figures of the second query to all rows in the first query with the same DATETIME column).
    In case that there are no matching rows in one of the tables and you want to include row(s) from the second one, you need to use OUTER JOIN. For its syntax, look into the documentation, available e.g. online on http://tahiti.oracle.com/.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Hi

    Thanks very much for the sample query. Didn't know about pivoting.

    So to join the format of the data needs to be the same for both queries. So I take it output of the first query needed to be transformed into the format of the ouput of the second query?

    Can you help me with the syntax please?

    If it helps, I've explained what I'm trying to achieve again just in-case in the attachment to this reply.
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    Hi

    Thanks very much for the sample query. Didn't know about pivoting.

    So to join the format of the data needs to be the same for both queries. So I take it output of the first query needed to be transformed into the format of the ouput of the second query?

    Can you help me with the sql please?

    If it helps, I've explained what I'm trying to achieve again just in-case in the attachment to this reply.

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Missed the attachment...
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Firstly, please post code into the post, it is really tiring to switch between your post and attachment. If you do not know, how to format it properly, have a look into Guidance and resources for posters (the "Sticky" - first post in the list). Alternatively use '#' ("Wrap [CODE] tags around selected text") button above to format selected text as code.

    Quote Originally Posted by flyboy
    the resultset of the second query is unsuitable; it should be transformed to one row for each TIMESTAMP
    Quote Originally Posted by shajju
    So I take it output of the first query needed to be transformed into the format of the ouput of the second query
    No. Sorry, I cannot state it clearer.

    Here is the code from your attachment with proposed PIVOT recommendation:
    Query 1
    Code:
    Select datetime, 
      sum(OM_ATT) OMHED, sum(NA_ATT) NAHED, sum(INTERNATIONAL) INTER, 
      sum(TOTAL_ATTACHED)TOTAL_ATTACHED
    from (
      Select *
      from (
        SELECT  MSC,MIN(DATETIME) DATETIME,
          MAX(TOTAL_ATT) TOTAL_ATTACHED, MAX(OM_ATT) OMHED, MAX(NA_ATT) NAHED,
         (MAX(TOTAL_ATT) - MAX(OM_ATT) - MAX(NA_ATT)) INTER 
        FROM SCHEMA1.TABLE1
        WHERE DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600 
          AND ((MSC = 'MSC1') OR (MSC = 'MSC11') OR (MSC = 'MSC12') OR (MSC = 'MSC13') OR (MSC = 'MSC2') OR (MSC = 'MSC6')) 
        GROUP BY TRUNC(DATETIME,'HH') ,MSC
      )
      group by datetime
    )  -- this parenthesis was missing
    
    OUTPUT
    			OMHED		NAHED			INTER				TOTAL_ATTACHED
    8/31/2009 5:00:00 PM	470066		22223			11945				504934
    Query 2
    Code:
    select datetime, -- OBJECTID, here you shall PIVOT
      sum(total_attached) total_attached,
      sum(decode( OBJECTID, 'OM-SUB', total_attached)) OMSUB,
      sum(decode( OBJECTID, 'NW_SUB', total_attached)) NWSUB,
      sum(decode( OBJECTID, 'INT_SUB', total_attached)) INTSUB
    from (
      SELECT TRUNC(DATETIME,'HH') datetime,msc,
        decode(OBJECTID,4969,'OM-SUB',4968,'NW_SUB',4967,'NW_SUB','INT_SUB') OBJECTID,
        max(NHLRREGAST) as total_attached
      FROM schema2.table2
      WHERE  DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600
      group by TRUNC(DATETIME,'HH'),msc, decode(OBJECTID,4969,'OM-SUB',4968,'NW_SUB',4967,'NW_SUB','INT_SUB')
      )
    group by datetime--, OBJECTID again, PIVOT
    
    OUTPUT -- will be then different then this
    DATETIME		OBJECTID	SUM(TOTAL_ATTACHED)
    8/31/2009 1:00:00 PM	NW_SUB		6879
    8/31/2009 1:00:00 PM	OM-SUB		699786
    8/31/2009 1:00:00 PM	INT_SUB		2465
    If you do not understand it, please search. There are many examples online, e.g. here: http://www.orafaq.com/wiki/PIVOT
    Quote Originally Posted by shajju
    I need to create a new report containing the SUM of:
    OM_ATTACHED and OM-SUB
    NAWRAS_ATTACHED and NW_SUB
    INTERNATIONAL_INROAMER and INT_SUB
    Just curious: you really want to sum figures for different DATETIME? If so, exclude the join condition; but do not complain for wrong result when both queries will return more than one row.

  9. #9
    Join Date
    Aug 2008
    Posts
    464

    Joining 2 tables

    Hi

    Thanks for the advice. Got the output of the second query after pivoting. However, I couldn't join the two queries as you suggested.

    Could you please help?

  10. #10
    Join Date
    Aug 2008
    Posts
    464
    Joined them Thanks very much for your help.

Posting Permissions

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