December 6, 2006 at 10:58 pm
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
December 6, 2006 at 11:54 pm
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"
December 8, 2006 at 7:16 am
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
December 8, 2006 at 8:29 am
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.
December 11, 2006 at 5:11 am
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