Joining Two Queries That Contain Groupings and Aggregate Functions

  • I have two queries both of which contain group by and sums that I am trying to join. How do I go about accomplishing that?

    The first query actually selects from an Oracle database using the adlinked server that I set up. The second query is straight SQL. I want to join the results of these two queries on the PolicyNum and SectionDescription fields.

    The first query:

    Select Z.PolicyNum, Y.SectionDescription, Z.NameOfInsured, Z.PolEffective, Z.SICCode, Z.FilingState, Left(Z.Tax_PostalCode,5) As 'Tax_PostalCode', Sum(Z.GrossAmount) As 'GrossAmount' From OpenQuery

    (

    DIAS,

    '

    Select

    B.PolicyNum, B.NameOfInsured, A.LineCode, A.GrossAmount, B.POLEFFECTIVE, B.SICCODE, B.FILINGSTATE, B.TAX_POSTALCODE

    From

    POL.POLICYTRAN A

    Join POL.POLICY B

    On A.POLICYNUM = B.POLICYNUM And A.CUSTNUM = B.CUSTNUM And A.LEVEL1ORG = B.LEVEL1ORG

    Where

    A.POLICYNUM Like ''2%'' And

    A.TRANCODE In (''prem'', ''Adj'', ''OEP'', ''RNF'', ''REI'', ''ERP'', ''APEN'', ''CANC'', ''PCAN'', ''CCAN'', ''FCAN'', ''NBPR'', ''NCOV'', ''REWR'', ''RNBU'', ''RNWL'', ''RPEN'') And

    A.TRANTYPE In (''i'', ''j'', ''d'') And

    A.IPPAYEE In (20029, 2002) And

    A.FUTUREFLAG = ''n'' And

    A.INPUTCODE Not In (''fr'', ''dx'', ''da'', ''dt'', ''pa'', ''fn'', ''sl'', ''cr'', ''cc'', ''np'', ''ls'', ''cp'', ''cs'', ''ci'', ''ca'') And

    to_date(B.POLEFFECTIVE) > ''31-DEC-2002''

    Order By B.PolicyNum, A.LineCode'

    ) As Z

    Join LineCodes Y

    On Z.LineCode = Y.LineCode

    Where Z.GrossAmount <> '.00'

    Group By Z.PolicyNum, Y.SectionDescription, Z.NameOfInsured, Z.PolEffective, Z.SICCode, Z.FilingState, Z.Tax_PostalCode

    The second query:

    Select PolicyNumber, Sum(IndemnityPayment) As 'IndemnityPayment', Sum(FeePayment) As 'FeePayment', Sum(IndemnityReserve) As 'IndemnityReserve', Sum(GrossReserve) As 'GrossReserve', SectionDescription

    From ACEClaimsData

    Group By PolicyNumber, SectionDescription

    Order By PolicyNumber, SectionDescription

  • Hi

    Why dont u store the resultset that you get from the linked server query into a temp table and then use the temp table in the SQL query to do your joins. 

    There would be better ways to do this though

     

    "Keep Trying"

  • If you are using SS2005, I have found Common Table Expressions useful for this type of problem. For example, make the second query a CTE.

    Then you can join it in the first query using the PolicyNum and SectionDescription.

    Check out this reference.

    http://www.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp

  • The simple answer is to just use your existing queries as derived table subqueries.  You have to remove the ORDER BY clauses, but otherwise it is simply:

    SELECT ...

    FROM (

        { query 1 }

    ) q1

    { INNER | LEFT | RIGHT | CROSS | FULL } JOIN (

       { query 2 }

    ) q2 [ ON ... ]

    That's the simplest answer in theory, in practice you may find the temp table approach works better.  When pulling data from a linked server you can't use indexes on the remote system to speed up the join to local data.  Depending on the number of rows involved, you may see a large performance improvement by saving the OPENQUERY results in a temp table with an index before joining anything.

    Also, in the first query it looks like you could move the "WHERE GrossAmount <> '.00'" and GROUP BY into the OPENQUERY.  This might reduce the number of rows you need to transfer and speed it up quite a bit.

  • Another Approch:

    First do Ur Ad hoc query on. Then why not U create a View for Oracle data query using OPENDATASOURCE. Then U can use that view anywhere in your program just like another views.

    BHUDEV PRAKASH

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply