Excel 2007 MS Query中的多部分标识符错误，但SQL Server 2008中没有 [英] Multipart identifier error in Excel 2007 MS Query but not in SQL Server 2008

问题描述

我有以下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中可以正常工作，但在Excel 2007的MS Query中提供[Microsoft][ODBC SQL Server Driver][SQL Server]The multi-part identifier "pd1.flowDate" could not be bound.有人可以解释该代码出了什么问题吗?

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?

推荐答案

MS查询中不允许Select中的子查询.因此，在删除子查询并使其成为联接时，它将在MS Query中起作用.以下代码可在MS Query中使用

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'

这篇关于Excel 2007 MS Query中的多部分标识符错误，但SQL Server 2008中没有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！