SQL: not exist with openquery not working as expected
I do have an oracle 8 database from which I want to fetch data to SQL Server 2005. The following statement works fine, if the table on SQL Server 2005 is empty. If I run with let's say one entry missing, it does not work. Please let me know, if any additional information would be usefull!
SELECT wdmsoracle.NO
FROM (Select * from OPENQUERY(msch, 'select
actu.NO
, actu.ANNOSEQUENT
, replace(replace(dm.descript, ''Generated by modification request '', ''Modification request''), ''Pseudo ECR for WDMS-SAP interface'', ''New Drawing'')
, actu.APPRDATE
, actu.MADEDATE
, actu.MAINDRW
, actu.DESIGNGRP
, actu.ITEMID
, actu.ISSUE
, actu.DESCRIPT
, actu.DESCRIPT2
, dr.PRODUCTCODE
, mpaths.webpath as asdasd
, vpaths.webpath
FROM
WDDRAWACTU actu
, wddraw dr
, wddvfilesiss vfiles
, wddvpaths vpaths
, wddmfiles mfiles
, wddmpaths mpaths
, wdanno dm
WHERE actu.apprdate >= to_date(''01-01-05'',''dd-mm-yy'')
and actu.itemid = dr.itemid (+)
and actu.issue = d开发者_运维问答r.issue (+)
and actu.annosequent = dm.sequent (+)
and actu.itemid = vfiles.itemid (+)
and actu.issue = vfiles.issue (+)
and vfiles.pathid = vpaths.pathid
and actu.annosequent = mfiles.sequent (+)
and mfiles.pathid = mpaths.pathid')) as wdmsoracle
Where NOT EXISTS (
SELECT wdmsoracle.NO
FROM [DesignMessage_Workflow].[dbo].[WDMS] wdms, (Select * from OPENQUERY(msch, 'select
actu.NO
FROM
WDDRAWACTU actu
WHERE actu.apprdate >= to_date(''01-01-05'',''dd-mm-yy'')')) as wdmsoracle
WHERE wdms.NO = wdmsoracle.NO)
with the help of this website I was able to solve it on my own:
SELECT wdmsoracle.NO
FROM (Select * from OPENQUERY(msch, 'select
actu.NO
, actu.ANNOSEQUENT
, replace(replace(dm.descript, ''Generated by modification request '', ''Modification request''), ''Pseudo ECR for WDMS-SAP interface'', ''New Drawing'')
, actu.APPRDATE
, actu.MADEDATE
, actu.MAINDRW
, actu.DESIGNGRP
, actu.ITEMID
, actu.ISSUE
, actu.DESCRIPT
, actu.DESCRIPT2
, dr.PRODUCTCODE
, mpaths.webpath as asdasd
, vpaths.webpath
FROM
WDDRAWACTU actu
, wddraw dr
, wddvfilesiss vfiles
, wddvpaths vpaths
, wddmfiles mfiles
, wddmpaths mpaths
, wdanno dm
WHERE actu.apprdate >= to_date(''01-01-05'',''dd-mm-yy'')
and actu.itemid = dr.itemid (+)
and actu.issue = dr.issue (+)
and actu.annosequent = dm.sequent (+)
and actu.itemid = vfiles.itemid (+)
and actu.issue = vfiles.issue (+)
and vfiles.pathid = vpaths.pathid
and actu.annosequent = mfiles.sequent (+)
and mfiles.pathid = mpaths.pathid')) as wdmsoracle
Where NOT EXISTS (
SELECT wdms.NO
FROM [DesignMessage_Workflow].[dbo].[WDMS] wdms
WHERE wdms.NO = wdmsoracle.NO)
精彩评论