SQLServer视图应用(32位Server连接64位Server)

SQLServer视图：

在工作中遇到另一个事业部向我要SQL视图的情况，便提供给他们。

CREATE view V_Test              as              select '1' 'CompID',MatlID,MatlDesc,SpecName,UomBase,AVLQty,MonAVGQty,SafeStock,QTYREQ,CURR from OPENQUERY              (server-name1,              '              select a.MatlID,a.MatlDesc,a.SpecName,a.UomBase,a.AVLQty,a.MonAVGQty,a.SafeStock,SUM(b.QTYREQ) ''QTYREQ'',c.CURR              from (              select a.MatlID,a.MatlDesc,a.SpecName,a.UomBase,a.AVLQty,a.OUTQty/90 ''MonAVGQty'',b.SafeStock              from database1.dbo.INV23010 a,database1.dbo.SYS02090 b              where a.MatlID=b.MatlID              ) a         left join (        select a.PN,CURR,UPRICE from database1..PURPRICE a,        (select PN,MAX(EFFIN) ''EFFIN'' from database1..PURPRICE where ISNULL(APPDATE,'''')<>'''' group by PN) b        where a.PN=b.PN and a.EFFIN=b.EFFIN and isnull(a.uprice,-1)<>-1        ) c on a.MatlID=c.PN    left join (select QTYREQ,PN from database1.dbo.REQUEST where ISNULL(CLOSED,'''')<>''E'' and PONO is null) b on a.MatlID=b.PN             where isnull(c.uprice,-1)<>-1               group by a.MatlID,a.MatlDesc,a.SpecName,a.UomBase,a.AVLQty,a.MonAVGQty,a.SafeStock,c.CURR              '            --,c.UPRICE  )            union            select '2' 'CompID',MatlID,MatlDesc,SpecName,UomBase,AVLQty,MonAVGQty,SafeStock,QTYREQ,CURR from OPENQUERY              (server-name2,              '              select a.MatlID,a.MatlDesc,a.SpecName,a.UomBase,a.AVLQty,a.MonAVGQty,a.SafeStock,SUM(b.QTYREQ) ''QTYREQ'',c.CURR              from (              select a.MatlID,a.MatlDesc,a.SpecName,a.UomBase,a.AVLQty,a.OUTQty/90 ''MonAVGQty'',b.SafeStock              from database2.dbo.INV23010 a,database2.dbo.SYS02090 b              where a.MatlID=b.MatlID              ) a         left join (        select a.PN,CURR,UPRICE from database2..PURPRICE a,        (select PN,MAX(EFFIN) ''EFFIN'' from database2..PURPRICE where ISNULL(APPDATE,'''')<>'''' group by PN) b        where a.PN=b.PN and a.EFFIN=b.EFFIN and isnull(a.uprice,-1)<>-1        ) c on a.MatlID=c.PN    left join (select QTYREQ,PN from database2.dbo.REQUEST where ISNULL(CLOSED,'''')<>''E'' and PONO is null) b on a.MatlID=b.PN             where isnull(c.uprice,-1)<>-1              group by a.MatlID,a.MatlDesc,a.SpecName,a.UomBase,a.AVLQty,a.MonAVGQty,a.SafeStock,c.CURR            '              )