SQL：不存在且 openquery 未按预期工作

Question

我确实有一个 oracle 8 数据库，我想从中获取数据到 SQL Server 2005。如果 SQL Server 2005 上的表为空，则以下语句可以正常工作。如果我运行时缺少一个条目，则它不起作用。如果任何其他信息有用，请告诉我！

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 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)

Answer 1

在这个网站的帮助下我能够自己解决这个问题：

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)