SQL query works in SQL Server, fails in Excel (Microsoft Query)

Question

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:

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

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

I typically find MS query to be quite limiting so I typically will simplify what I feed to MS Query by making a view. If what I'm trying to do is too complex for a view I'll make a VBA UDF that returns an array. — Dean MacGregor
– Dean MacGregor, Commented Feb 1, 2013 at 14:05

Ansari · Accepted Answer · 2012-09-12 07:37:53Z

4

You need to put testQuery and maxDate inside single quotations

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

edited Sep 12, 2012 at 7:37

answered Sep 12, 2012 at 7:35

Ansari

691 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohammad Anini · Accepted Answer · 2014-11-02 09:55:19Z

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

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

Collectives™ on Stack Overflow

SQL query works in SQL Server, fails in Excel (Microsoft Query)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related