Microsoft SQL：找不到列“dbo”或用户定义的函数或聚合

I am new to SQL and trying to use Power Query to pull Great Plains data into Excel directly using a UDF that I found here to pass parameters from the workbook to the query. Here is the UDF:

我是SQL的新手，并尝试使用Power Query直接使用我在此处找到的UDF将Great Plains数据拉入Excel，以将参数从工作簿传递到查询。这是UDF：

(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

and here is the SQL query:

这是SQL查询：

Declare @accrualDate Date
set @accrualDate = dbo.fnGetParameter("Accrual Date")
Declare @lookback Date
set @lookback = dbo.fnGetParameter("Lookback for Automated Accruals")
Declare @lookbackBOP Date
set @lookbackBOP = dbo.fnGetParameter("Lookback for Manual Accruals - BOP")
Declare @lookbackEOP Date
set @lookbackEOP = dbo.fnGetParameter("Lookback for Manual Accruals - EOP")

SELECT
[Open Year], 
[History Year],
[Period ID], 
[TRX Date],
[Account Number], 
[Account Description], 
[Journal Entry],
[description],
[reference],
[Originating Posted Date],
Sum([Debit Amount]) AS [Debit Amount], 
Sum([Credit Amount]) AS [Credit Amount] ,

Segment1, 
Segment2, 
Segment3, 
Segment4, 
Segment5, 
Segment6, 
Segment7

FROM ['dbName'].[dbo].[AccountTransactions]
WHERE Segment2 BETWEEN 6000 AND 6999
and [description] = 'Purchases'
and [TRX Date] BETWEEN @lookback AND @accrualDate
GROUP BY [Open Year], [History Year], [Period ID], [TRX Date], [Account Number], [Account Description], [Journal Entry],[description],[reference], [Originating Posted Date], Segment1, Segment2, Segment3, Segment4, Segment5, Segment6, Segment7

I have run the UDF by itself, and have confirmed that it returns the expected values from my "Parameters" table. However, when running the query I get the following error:

我已经自己运行了UDF，并确认它从我的“参数”表中返回了预期的值。但是，在运行查询时，我收到以下错误：

DataSource.Error: Microsoft SQL: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnGetParameter", or the name is ambiguous. Details: DataSourceKind=SQL DataSourcePath=dcb-gp15-sql.us.medsolutions.com;MED01 Message=Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnGetParameter", or the name is ambiguous. Number=4121 Class=16

DataSource.Error：Microsoft SQL：找不到列“dbo”或用户定义的函数或聚合“dbo.fnGetParameter”，或者名称不明确。详细信息：DataSourceKind = SQL DataSourcePath = dcb-gp15-sql.us.medsolutions.com; MED01 Message =无法找到列“dbo”或用户定义的函数或聚合“dbo.fnGetParameter”，或者名称不明确。 Number = 4121 Class = 16

Any help would be greatly appreciated.

任何帮助将不胜感激。

1 个解决方案

#1

The custom "M" function fnGetParameter is only defined when running mashups in Power Query, and isn't sent to the server.

自定义“M”函数fnGetParameter仅在Power Query中运行mashup时定义，并且不会发送到服务器。

If you specify a native SQL query from Power Query, that's literally what we send to the server. Power Query doesn't give you a way to define custom UDF on the server.

如果您从Power Query指定本机SQL查询，那么这就是我们发送给服务器的内容。 Power Query无法为您提供在服务器上定义自定义UDF的方法。

Instead you could import the AccountTransactions table into Power Query, and use "M" transformations to transform the table. I'd recommend using the UI to generate the row filters and summation aggregations, and then customize the "M" to use your customer filter.

相反，您可以将AccountTransactions表导入Power Query，并使用“M”转换来转换表。我建议使用UI生成行过滤器和求和聚合，然后自定义“M”以使用客户过滤器。

e.g. one of your steps would be something like:

例如你的一个步骤是这样的：

FilteredRows3 = Table.SelectRows(
    FilteredRows2,
    each
        fnGetParameter("Lookback for Automated Accruals") < [TRX Date] 
      and 
        [TRX Date] < fnGetParameter("Accrual Date")
)

#1