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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy