OLE DB / ODBC错误:一个或多个必需的参数没有给出的值

Question

当我单击PowerPivot 2013中的查询编辑器中时，我会遇到以下错误。当我验证我仍然得到的输入时：" sql语句是有效的"。<<<<<<<<<<<<<</p>

ole db或ODBC错误：一个或多个必需的参数没有给出的值。处理表" SAP BXL"时发生错误。当前操作被取消，因为交易中的另一个操作失败了。

任何想法可能出了什么问题？这是SQL语句：

   SELECT 'A' As "region", 
          [Sheet1$].[Cost Ctr] As "cost center",
          [Sheet1$].[Cost Elem#] AS "cost element",
          [Sheet1$].[Doc# Date] AS "document date",
          sum([Sheet1$].[Val#in rep#cur#]) AS "val" 
     FROM [Sheet1$]
 GROUP BY region, [Sheet1$].[Cost Ctr], [Sheet1$].[Cost Elem#], [Sheet1$].[Doc# Date]
UNION ALL
   SELECT 'B' As "region",
          [Sheet1$].[Cost Center]  As "cost center",
          [Sheet1$].[Cost Element] AS "cost element",
          [Sheet1$].[Document Date] AS "document date",
          sum([Sheet1$].[Val/COArea Crcy]) as "val"
     FROM `C:a.xlsx`.[Sheet1$]
 GROUP BY region, 
          [Sheet1$].[Cost Center], 
          [Sheet1$].[Cost Element], 
          [Sheet1$].[Document Date]

非常感谢。

编辑：我找到了分辨率。我可以通过"区域"参数删除该组，然后它似乎有效。我认为人们总是需要按所有非聚集列进行分组？

Answer 1

您在SELECT子句中定义的结果列名称中不存在GROUP BY子句中。SQL的语法令人困惑，因为查询的最后一步是您编写的第一件事。您应该将其解释为

FROM `C:a.xlsx`.[Sheet1$]
GROUP BY region, 
    [Sheet1$].[Cost Center], 
    [Sheet1$].[Cost Element], 
    [Sheet1$].[Document Date]
SELECT 'B' As "region",
    [Sheet1$].[Cost Center]  As "cost center",
    [Sheet1$].[Cost Element] AS "cost element",
    [Sheet1$].[Document Date] AS "document date",
    sum([Sheet1$].[Val/COArea Crcy]) as "val"

在这里，很明显region尚不存在。

FROM `C:a.xlsx`.[Sheet1$]
GROUP BY
    [Sheet1$].[Cost Center], 
    [Sheet1$].[Cost Element], 
    [Sheet1$].[Document Date]
SELECT 'B' As "region",
    [Sheet1$].[Cost Center]  As "cost center",
    [Sheet1$].[Cost Element] AS "cost element",
    [Sheet1$].[Document Date] AS "document date",
    sum([Sheet1$].[Val/COArea Crcy]) as "val"

在这里，很明显，可以在没有任何汇总功能的情况下确定 'B'（常数），因为您不使用任何不属于GROUP BY子句的列。根本不使用任何列只是不使用任何不属于GROUP BY子句的列的特殊情况。：）

您为解决问题所做的工作正是您应该做的。