CODEWITHSUNDEEP
sqlsql-serverexcelexcel-2010
ke3pup
ke3pup

Reputation: 1895

SQL query works in SQL Server, fails in 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:

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

Upvotes: 3

Views: 2356

Answers (2)

Mohammad Anini
Mohammad Anini

Reputation: 5230

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

Upvotes: 2

Ansari
Ansari

Reputation: 69

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

Upvotes: 4

Related Questions