Excel 2013高级查询-具有动态参数的SQL查询 [英] Excel 2013 power query - SQL query with a dynamic parameter

问题描述

但是，从网络上看，我找不到一个简单的示例(！)，该示例将显示如何使用并拥有一个SQL查询，该查询将在WHERE中使用动态参数值.

我要寻找的是最终用户将能够更改电子表格中的列(ParameterTable?)中的值，并使用此新值作为参数来刷新数据.

也许一个例子最能说明我的追求.

我将在excel中创建一个表，该表具有两列，分别名为ParameterName和ParameterValue.这些将具有值PARM1和1 (很抱歉，但是我似乎无法在此处发布图片)

查询可能像这样，因此如果1 = 1检索当前日期 (这是oracle，但对于大多数其他数据库应该相同)

从双重位置1 = 1中选择SYSDATE;

我应该如何更改上述内容，以便它可以使用ParamaterName和ParameterValue，即最右边的"1"可以动态更改为任何值.如果该值不是"1"，则不会返回任何结果.

预先感谢

解决方案

我还有另一种将参数传递到Power Query中的方法.我确定它可以正常工作.

第1步:在excel中创建一个名为"Infodesk"的表，也可以根据需要命名该表.我的"Infodesk"表包含脚本"列，我将SQL脚本放置在此列中.

第2步:创建一个空白的Power Query，单击Advance Editor，然后输入以下代码

let
    Source = Sql.Database("192.168.23.81", "dbo.name", [Query= Excel.CurrentWorkbook(){[Name="Infodesk"]}[Content][Script]{0}])

in
    Source

然后运行查询.代码[Script] {0}]将从第一行获取数据，您可以在该列中放置多个脚本，然后更改{0} >> {1}或{2} ...以从其他行获取数据. /p>

为了更改SQL脚本中的任何参数，您可以使用excel函数= CONCATENATE()将多个excel单元组合成一个完整的SQL.

however I have looked from the web I cant find a simple(!) example that would show how to use and have a SQL query that would use in the WHERE a dynamic parameter value.

What I'm looking for is that the end user would be able to change a value in the column in the spreadsheet (ParameterTable?) and refresh the data using this new value as a parameter.

Perhaps an example describes best what I'm after.

I would have a table in excel that would have two columns, named ParameterName and ParameterValue. These would then have values PARM1 and 1 (Sorry but appears that I cant post an image here)

The the query could be like this, so if 1=1 retrieve the current date (this is oracle but should be the same for most other databases)

SELECT SYSDATE FROM DUAL WHERE 1 = 1;

How should I change the above so that it would use the ParamaterName and ParameterValue, i.e. that the rightmost "1" could be changed dynamically to any value. If the value would be anything else than "1" no results would be returned.

Thanks in advance

解决方案

I have another way to pass parameter into Power Query. I make sure it works.

Step 1: Create a table named "Infodesk" in excel, or you can name it as you wish. My table "Infodesk" contains column "Script" where I place the SQL script in this column.

Step 2: Create a blank Power Query, click on Advance Editor, and input following code

let
    Source = Sql.Database("192.168.23.81", "dbo.name", [Query= Excel.CurrentWorkbook(){[Name="Infodesk"]}[Content][Script]{0}])

in
    Source

And then, run your query. The code [Script]{0}] will get data from 1st row, you can place multiple script in the column, then change {0} >> {1} or {2} ... to get data from other rows.

In order to change any parameter in SQL script, you may use the excel function =CONCATENATE() to combine multiple excel cells into a completed SQL.

这篇关于Excel 2013高级查询-具有动态参数的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！