Excel 2007 MS Query中的多部分标识符错误，但SQL Server 2008中没有

Question

我有以下SQL代码

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'

它在SQL Sever 2008中工作正常，但[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "pd1.flowDate" could not be bound在Excel 2007中提供了MS
Query。有人可以解释该代码出了什么问题吗？

Answer 1

SelectMS Query中不允许子查询。因此，在删除子查询并使其成为联接后，即可在MS Query中使用。以下代码可在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'