在同一台服务器上连接多个数据库SQL Server

Question

我试图从位于同一台服务器上的两个数据库中进行查询。第一个数据库查询与JOIN和UNION子句有点复杂，但加入其他数据库非常简单。我在最后一行代码上一直收到错误 - SITE_ID_NUMBER。

我从来没有尝试过这一点 - 任何线索，我可能做错了什么？附加说明 - 数据库的JOIN子句由一个本地列组成 - 即“6041”和一个计算结果 - 我必须使用SUBSTRING并添加6以获得匹配值。

更新 - 一些变化之后，我居然遇到了错误：

Msg 8156, Level 16, State 1, Line 32
The column 'RECORD_DATE_TIME' was specified multiple times for 'P'.

谢谢。

SELECT P.[6] + SUBSTRING(A.SHIP_TO,6,3) AS 'STORE' , 
     S.SITE_ID_NUMBER, 
     S.DM, 
     S.AREA, 
     S.STORE_NAME, 
     S.LOCATION, 
     P.WHSE AS 'DIVISION_DESC', 
     P.ORDER_STATUS, 
     P.MATERIAL, 
     P.DESCRIPTION, 
     P.PO_NUMBER AS 'CUSTOMER_PO_NUMBER', 
     P.ORDER_QUANTITY AS'QTY', 
     P.RSHIP_DATE AS 'REQUESTED_SHIP_DATE', 
     S.TRANSIT_6040_6041 

FROM 

(SELECT * 

FROM PDX_SAP_USER.dbo.VW_ADIDAS_RETAIL_aRI A 
JOIN PDX_SAP_USER.dbo.VW_WB_DELIVERIES D 
ON A.DELIVERY_NUMBER = D.DELIVERY_NUMBER 

UNION ALL 

SELECT * 

FROM PDX_SAP_USER.dbo.VW_ADIDAS_RETAIL_aRO A 
JOIN PDX_SAP_USER.dbo.VW_WB_DELIVERIES D 
ON  A.DELIVERY_NUMBER = D.DELIVERY_NUMBER) P 

JOIN [ADI_USER.MAINTAINED].dbo.SiteDataAdiRbk S 

ON S.SITE_ID_NUMBER = P.STORE; 

Answer 1

Msg 8156, Level 16, State 1, Line 32 The column 'RECORD_DATE_TIME' was specified multiple times for 'P'. RECORD_DATE_TIME在您提供的查询中不存在。无论如何，错误很明显。我会告诉你一个简单的例子，重现你的错误。使用这个例子来修复你的代码。

CREATE TABLE [dbo].[t1](
    [id] [int] NULL, 
    [name] [nchar](10) NULL 
) ON [PRIMARY] 

如图所示加入表格以获得此错误。

select * 
from 
(
select * 
from t1 inner join t1 as t2 on t1.id = t2.id 
) as t 

Msg 8156, Level 16, State 1, Line 6 
The column 'id' was specified multiple times for 't'. 

Answer 2

在最外层，你有接近2表别名：

• ，P：这是结果从子查询中设置与联盟
• S：这对表的别名[ADI_USER.MAINTAINED] .dbo.SiteDataAdiRbk

您选择列表中的第一项是P. [6] + SUBSTRING（一个 .SHIP_TO，6,3）AS '商店'

你引用一个命名表或别名，这是不可访问。

Answer 3

谢谢大家的回复和好主意。在一些需要专注于其他项目之后，突然间，我的答案突然出现了，而且似乎到目前为止还在工作。尽管如此，我不会在那里注入想法。我的目标是加入一个在我们的工作区用户维护文件夹中创建的表格，在该文件夹中，我已经读取/写入了传递信息的数据库，并且通过这样做，因为我在下面工作 - 我会说我需要进一步挖掘帐户因为有两个可能的地点，但是这比我们现在的要好得多，甚至新的分析师都能够在报告网站上投放并发布。

SELECT AD.STORE, 
     S.DM, 
     S.AREA, 
     S.STORE_NAME, 
     S.LOCATION, 
     AD.DIVISION_DESC, 
     AD.ORDER_STATUS, 
     AD.MATERIAL, 
     AD.DESCRIPTION, 
     AD.CUSTOMER_PO_NUMBER, 
     AD.QTY, 
     AD.REQUESTED_SHIP_DATE, 
     S.TRANSIT_6040_6041, 
     AD.REQUESTED_SHIP_DATE + S.TRANSIT_6040_6041 AS'ESTIMATED_ESTIMATED_DELIVERY_DATE' 


FROM 

(

SELECT '6' + SUBSTRING(A.SHIP_TO,6,3) AS 'STORE', 
     '' AS 'DM', 
     '' AS 'AREA', 
     '' AS 'STORE_NAME', 
     '' AS 'LOCATION', 
     A.WHSE AS 'DIVISION_DESC', 
     A.ORDER_STATUS, 
     D.MATERIAL, 
     D.DESCRIPTION, 
     A.PO_NUMBER AS 'CUSTOMER_PO_NUMBER', 
     D.ORDER_QUANTITY AS'QTY', 
     A.RSHIP_DATE AS 'REQUESTED_SHIP_DATE', 
     '' AS 'TRANSIT', 
     '' AS 'ESTIMATED_DELIVERY_DATE' 


FROM VW_ADIDAS_RETAIL_aRI A 
JOIN VW_WB_DELIVERIES D 
ON A.DELIVERY_NUMBER = D.DELIVERY_NUMBER 

GROUP BY A.SHIP_TO, 
     A.WHSE, 
     A.ORDER_STATUS, 
     A.PO_NUMBER, 
     D.MATERIAL, 
     D.DESCRIPTION, 
     D.ORDER_QUANTITY, 
     A.RSHIP_DATE, 
     A.ASHIP_DATE 

HAVING A.ORDER_STATUS NOT IN ('shp') 

UNION ALL 

SELECT '6' + SUBSTRING(A.SHIP_TO,6,3) AS 'STORE', 
     '' AS 'DM', 
     '' AS 'AREA', 
     '' AS 'STORE_NAME', 
     '' AS 'LOCATION', 
     A.WHSE AS 'DIVISION_DESC', 
     A.ORDER_STATUS, 
     D.MATERIAL, 
     D.DESCRIPTION, 
     A.PO_NUMBER AS 'CUSTOMER_PO_NUMBER', 
     D.ORDER_QUANTITY AS'QTY', 
     A.RSHIP_DATE AS 'REQUESTED_SHIP_DATE', 
     '' AS 'TRANSIT', 
     '' AS 'ESTIMATED_DELIVERY_DATE' 

FROM VW_ADIDAS_RETAIL_aRO A 
JOIN VW_WB_DELIVERIES D 
ON  A.DELIVERY_NUMBER = D.DELIVERY_NUMBER 

GROUP BY A.SHIP_TO, 
     A.WHSE, 
     A.ORDER_STATUS, 
     A.PO_NUMBER, 
     D.MATERIAL, 
     D.DESCRIPTION, 
     D.ORDER_QUANTITY, 
     A.RSHIP_DATE, 
     A.ASHIP_DATE 

HAVING A.ORDER_STATUS NOT IN ('shp')) AD 

JOIN [ADI_USER_MAINTAINED].dbo.SiteDataAdiRbk S 

ON AD.STORE = S.SITE_ID_NUMBER;