SQL查询在SQL Server中工作，在Excel中失败(Microsoft query)

I have the following query which works as intended :

我有以下查询，如预期效果:

SELECT
        SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
        SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
        SERVICE_HISTORY.Comments
FROM
        DEBA_US.dbo.SERVICE_HISTORY
JOIN 
        (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS maxDate, CUSTOMER.AccNo
         FROM DEBA_US.dbo.CUSTOMER
         INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                     INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON CUSTOMER.ROW_PK = CAR.ROW_PK
         WHERE
             CUSTOMER.AccNo LIKE 'CUS-1234'
             AND CAR.DateSubmitted IS NULL
         GROUP BY
             CUSTOMER.AccNo) AS testQuery ON testQuery.maxDate = SERVICE_HISTORY.CreatedDate

The query is to gives me the latest (max) service history date for a given customer.

该查询将向我提供给定客户的最新(最大)服务历史日期。

When I execute the query in SQL Server, it works perfectly fine, but when I put the same query into EXCEL 2010 (Microsoft Query) it give me the error:

当我在SQL Server中执行查询时，它运行得非常好，但是当我将相同的查询放入EXCEL 2010 (Microsoft查询)时，它会给我错误:

No Column name was specified for Column 1 of 'testQuery'
Invalid column name 'maxDate'
Statement could not be prepared

没有为“testQuery”无效列名“maxDate”语句的第1列指定列名

I'm not able to fix the query to get pass the error. Can someone please tell me why Excel isn't working with the above query? Thanks

我不能修改查询以获得通过错误。有人能告诉我为什么Excel不能处理上述查询吗?谢谢

2 个解决方案

#1

You need to put testQuery and maxDate inside single quotations

您需要将testQuery和maxDate放在单引号中

SELECT
    SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
    SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
    SERVICE_HISTORY.Comments
FROM
    DEBA_US.dbo.SERVICE_HISTORY
JOIN 
    (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS 'maxDate', CUSTOMER.AccNo
     FROM DEBA_US.dbo.CUSTOMER
     INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                 INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON     CUSTOMER.ROW_PK = CAR.ROW_PK
     WHERE
         CUSTOMER.AccNo LIKE 'CUS-1234'
         AND CAR.DateSubmitted IS NULL
     GROUP BY
         CUSTOMER.AccNo) AS 'testQuery' ON testQuery.maxDate =  SERVICE_HISTORY.CreatedDate

#2

The only thing you need to do is to add square brackets around the maxDate like following:

您只需在maxDate周围添加方括号，如下所示:

SELECT
        SERVICE_HISTORY.ServiceMode, SERVICE_HISTORY.CreatedDate,
        SERVICE_HISTORY.CreatedBy, SERVICE_HISTORY.Branch,
        SERVICE_HISTORY.Comments
FROM
        DEBA_US.dbo.SERVICE_HISTORY
JOIN 
        (SELECT MAX(SERVICE_HISTORY.CreatedDate) AS [maxDate], CUSTOMER.AccNo
         FROM DEBA_US.dbo.CUSTOMER
         INNER JOIN (DEBA_US.dbo.SERVICE_HISTORY
                     INNER JOIN DEBA_US.dbo.CAR ON SERVICE_HISTORY.ROW_PK = CAR.ROW_PK) ON CUSTOMER.ROW_PK = CAR.ROW_PK
         WHERE
             CUSTOMER.AccNo LIKE 'CUS-1234'
             AND CAR.DateSubmitted IS NULL
         GROUP BY
             CUSTOMER.AccNo) AS testQuery ON testQuery.maxDate = SERVICE_HISTORY.CreatedDate

#1