August 10, 2015 at 12:19 pm
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
Any help is greatly appreciated..
select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,
[DBServer1].[DB1].[dbo].[SAVINGS] AS S,
[DBServer1].[DB1].[dbo].[NAME] AS N
where (S.TYPE=10 or
S.TYPE=11) and
S.PARENTACCOUNT=A.ACCOUNTNUMBER and
N.PARENTACCOUNT=A.ACCOUNTNUMBER and
N.TYPE=0 and
A.ProcessDate>20150726 and
(A.OPENDATE > getdate()-14 and
a.OPENDATE < getdate()-7)
group by N.SSN, A.ACCOUNTNUMBER
) ARC
ON ARC.SSN=CM.ContactID
where cm.summary = 'OnBoarding'
August 10, 2015 at 12:59 pm
bherbert (8/10/2015)
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...Any help is greatly appreciated..
select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,
[DBServer1].[DB1].[dbo].[SAVINGS] AS S,
[DBServer1].[DB1].[dbo].[NAME] AS N
where (S.TYPE=10 or
S.TYPE=11) and
S.PARENTACCOUNT=A.ACCOUNTNUMBER and
N.PARENTACCOUNT=A.ACCOUNTNUMBER and
N.TYPE=0 and
A.ProcessDate>20150726 and
(A.OPENDATE > getdate()-14 and
a.OPENDATE < getdate()-7)
group by N.SSN, A.ACCOUNTNUMBER
) ARC
ON ARC.SSN=CM.ContactID
where cm.summary = 'OnBoarding'
Couple of things.
One, I'd rewrite the query for the linked server database using OPENQUERY and using SQL-92 standard joins instead of the older SQL-89 style joins you are using.
Second, a RIGHT OUTER JOIN checking for a null values CM.ContactID.
August 10, 2015 at 2:40 pm
based on Lynn's fine advice, this is how i'd look at it:
SELECT *
INTO #tmp
FROM OPENQUERY([DBServer1], 'SELECT
N.SSN,
A.ACCOUNTNUMBER
FROM [DB1].[dbo].[Account] AS A,
INNER JOIN [DB1].[dbo].[SAVINGS] AS S
ON S.PARENTACCOUNT=A.ACCOUNTNUMBER
INNER JOIN [DB1].[dbo].[NAME] AS N
ON N.PARENTACCOUNT=A.ACCOUNTNUMBER
WHERE (S.TYPE=10 or S.TYPE=11)
AND N.TYPE=0
AND A.ProcessDate>20150726
AND (A.OPENDATE > getdate()-14
AND a.OPENDATE < getdate()-7
)
GROUP by
N.SSN,
A.ACCOUNTNUMBER')
select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
LEFT join #tmp ARC
ON ARC.SSN=CM.ContactID
where cm.summary = 'OnBoarding'
AND ARC.SSN IS NULL
Lowell
August 11, 2015 at 6:33 am
Hi Lowell..
When I ran the modified query, I get the following error...
OLE DB provider "SQLNCLI11" for linked server "DBServer1" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'INNER'.
August 11, 2015 at 7:04 am
during my copy/paste/modify, i left an invalid trailing comma in the string for the command here:
FROM [DB1].[dbo].[Account] AS A,
just remove the comma, and the command is syntactically correct; hopeful it returns the same data you expect.
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply