Multipart identifier error in Excel 2007 MS Query but not in SQL Server 2008

Question

I have the following SQL code

SELECT pd1.Meter,
       pd1.BasicPool,
       pd1.RateClass,
       pd1.Flowdate,
       (SELECT upOrDownContract
        FROM   PipelineData pd
        WHERE  pd.id = pd1.sibling) AS DnK,
       match.Volume,
       (SELECT Name
        FROM   Pipeline P
        WHERE  P.id = ISNULL(pd2.pipelineID, t.PipelineId)) AS Pipeline,
       (SELECT Name
        FROM   Client C
        WHERE  C.id = t.ClientId)                           AS CounterParty
FROM   MatchingHistoryBothSides match
       LEFT JOIN PipelineData pd1
              ON match.type1 = 'PipelineDataVO'
                 AND match.id1 = pd1.ID
       LEFT JOIN PipelineData pd2
              ON match.type2 = 'PipelineDataVO'
                 AND match.id2 = pd2.ID
       LEFT JOIN TransactionDailyVolume dtv
              ON match.type2 = 'TransactionDailyVolumeVO'
                 AND match.id2 = dtv.ID
       LEFT JOIN [Transaction] t
              ON dtv.TransactionID = t.ID
WHERE  match.type1 = 'PipelineDataVO'
       AND ( match.type2 = 'PipelineDataVO'
              OR match.type2 = 'TransactionDailyVolumeVO' )
       AND pd1.flowDate BETWEEN ? AND ?
       AND pd1.LDCid = 75
       AND pd1.direction = 'Receipt'

It works fine in SQL Sever 2008 but gives [Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "pd1.flowDate" could not be bound in MS Query of Excel 2007. Can anyone explain where this code is going wrong?

Try `select top 1 Flowdate from PipelineData` in Excel. Can you also please post the error message in ful. — Stoleg, May 24 '13 at 12:09
@Stoleg I posted the complete error message and where do suggest me to add `Top 1` in the above code? — Ram, May 24 '13 at 15:51
Run my code instead of yours. It will show if the column exists / visible at all. case sensitivity is a good suggestion by @pnuts too. — Stoleg, May 24 '13 at 15:54
@pnuts for case senstive I change both occurances to pd1.flowDate and it didn't work — Ram, May 24 '13 at 16:07
I found this [Microsoft Support Article](http://support.microsoft.com/kb/964009). I think it is the same case here too. What do you both think? — Ram, May 24 '13 at 16:11
I tried that too and it didn't work. It should not be a case as they both are from 2 different tables — Ram, May 24 '13 at 17:19
But that is for some Financial software by Microsoft not for Excel so I don't know if the issue is resolved in Excel 2007. Mine is Excel 2007 SP3 — Ram, May 24 '13 at 18:49

score 1 · Accepted Answer · answered May 30 '13 at 17:47

Sub queries in Select are not allowed in MS Query. Hence on removal of the the sub queries and making them as joins will work in MS Query. The following code works in MS Query

SELECT pd1.Meter,
       pd1.BasicPool,
       pd1.RateClass,
       pd1.FlowDate,
       pd.upOrDownContract AS dnk,
       match.Volume,
       p.Name              AS pipeline,
       c.Name              AS counterparty
FROM   Matchinghistorybothsides match
       LEFT JOIN Pipelinedata pd1
              ON match.type1 = 'PipelineDataVO'
                 AND match.id1 = pd1.ID
       LEFT JOIN Pipelinedata pd2
              ON match.type2 = 'PipelineDataVO'
                 AND match.id2 = pd2.ID
       LEFT JOIN Transactiondailyvolume dtv
              ON match.type2 = 'TransactionDailyVolumeVO'
                 AND match.id2 = dtv.ID
       LEFT JOIN [Transaction] t
              ON dtv.TransactionID = t.ID
       LEFT JOIN Client c
              ON c.id = t.ClientId
       LEFT JOIN Pipelinedata pd
              ON pd.id = pd1.sibling
       LEFT JOIN Pipeline p
              ON p.id = COALESCE(pd2.PipelineId, t.PipelineId)
WHERE  match.type1 = 'PipelineDataVO'
       AND ( match.type2 = 'PipelineDataVO'
              OR match.type2 = 'TransactionDailyVolumeVO' )
       AND pd1.FlowDate BETWEEN ? AND ?
       AND pd1.LDCid = 75
       AND pd1.direction = 'Receipt'

Multipart identifier error in Excel 2007 MS Query but not in SQL Server 2008

1 Answers1

Linked