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.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,c.CURR              
'            --,c.UPRICE  
)            
union            
select '2' 'CompID',CURR from OPENQUERY              
(server-name2,b.SafeStock              
from database2.dbo.INV23010 a,database2.dbo.SYS02090 b              
where a.MatlID=b.MatlID              
) a         
left join (        
select a.PN,UPRICE from database2..PURPRICE a,MAX(EFFIN) ''EFFIN'' from database2..PURPRICE where ISNULL(APPDATE,PN from database2.dbo.REQUEST where ISNULL(CLOSED,-1)<>-1              
group by a.MatlID,c.CURR            
'              
)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!