Excel的参数化查询 [英] Parametizing query for Excel
问题描述
我设法获得以下查询以与Excel一起使用:
I've managed to get the following query to work with Excel:
SELECT me.id ,me.merchant_num ,me.merchant_nm,
CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END AS production_mode,
Max(CASE WHEN tt.bank_txt = 'IBA' THEN tt.transaction_dt END) AS last_IBA_transaction_dt,
convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), trans_live)), 9, 1)) AS is_live
FROM Data.dbo.merchant_t me
LEFT JOIN Data.dbo.transaction_t AS tt
ON tt.merchant_id = me.id
where tt.transaction_dt >= ?
and tt.transaction_dt <= ?
and tt.trans_status = ?
GROUP BY me.id,me.merchant_num,me.merchant_nm, me.status
此查询是如此接近.我需要做的就是添加和删除下面的查询注释中所示的行:
This query is so close. All I need to do is add and remove the lines as indicated in the query comments below:
SELECT me.id ,me.merchant_num ,me.merchant_nm,
Count(CASE WHEN tt.transaction_dt >= ?
AND tt.transaction_dt <= ?
THEN tt.id end) AS num_transactions -- ADD THIS COUNT(CASE) STATEMENT
CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END AS production_mode,
Max(CASE WHEN tt.bank_txt = 'IBA' THEN tt.transaction_dt END) AS last_IBA_transaction_dt,
convert(bit, Substring(Max(convert(CHAR(8), tt.transaction_dt, 112) + convert(CHAR(1), trans_live)), 9, 1)) AS is_live
FROM Data.dbo.merchant_t me
LEFT JOIN Data.dbo.transaction_t AS tt
ON tt.merchant_id = me.id
where tt.transaction_dt >= ? -- REMOVE THIS LINE
and tt.transaction_dt <= ? -- REMOVE THIS LINE
and tt.trans_status = ?
GROUP BY me.id,me.merchant_num,me.merchant_nm, me.status
如果我将日期保留在以下行中，则该查询可用于Excel:
The query works with Excel if I leave the dates in the following line:
Count(CASE WHEN tt.transaction_dt >= '2020-04-01' AND tt.transaction_dt <= '2020-04-30' THEN tt.id end) AS num_transactions
而不是将其参数化为
Count(CASE WHEN tt.transaction_dt >= ? AND tt.transaction_dt <= ? THEN tt.id end) AS num_transactions
如果我将它们参数化，则会出现错误:
If I parameterize them, I get the error:
"Syntax error or access violation"
很明显，我需要将这些日期参数化以某种方式起作用.
Obviously, I need those dates parameterized somehow that works.
我有一个SQL Server查询，如下所示:
I have a SQL server query as follows:
WITH CTE_Merchants AS
(
SELECT
me.id, me.merchant_num, me.merchant_nm,
COUNT(tt.id) as num_transactions,
CASE
WHEN me.status = 'A' THEN 'Yes'
ELSE 'No'
END AS production_mode
FROM
merchant_t me
LEFT OUTER JOIN
transaction_t tt ON tt.merchant_id = me.id
AND tt.transaction_dt BETWEEN '2020-04-01' AND '2020-04-30' -- [PARAMETIZE BOTH DATES]
WHERE
me.status = 'T' -- [PARAMETIZE]
GROUP BY
me.id, me.merchant_num, me.merchant_nm, me.status
)
SELECT
CTE_Merchants.id,
CTE_Merchants.merchant_num,
CTE_Merchants.merchant_nm,
CTE_Merchants.num_transactions,
CTE_Merchants.production_mode,
A1.is_live,
A2.last_IBA_transaction_dt
FROM
CTE_Merchants
OUTER APPLY
(SELECT TOP 1 transaction_t.trans_live AS is_live
FROM transaction_t
WHERE transaction_t.merchant_id = CTE_Merchants.id
ORDER BY transaction_dt DESC) AS A1
OUTER APPLY
(SELECT TOP 1 transaction_t.transaction_dt AS last_IBA_transaction_dt
FROM transaction_t
WHERE transaction_t.merchant_id = CTE_Merchants.id
AND transaction_t.bank_txt = 'IBA'
ORDER BY transaction_dt DESC) AS A2;
我想在Excel电子表格中使用查询，并且需要对查询注释中指示的变量进行参数化.
I want to use the query in an Excel spreadsheet and I need to parametrize the variables indicated within the query comments.
以下是Excel数据连接对话框:
The following is the Excel Data connection dialogue:
问题是:当我尝试参数化变量时，例如改变
Problem is: when I try to parametrize the variables, e.g. change
AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'
到
AND tt.transaction_dt BETWEEN ? and ?
我得到一个错误
无效的参数号
之后
无效的描述符索引
Invalid Descriptor Index
如果我将BETWEEN语句更改为
The same thing happens if I change the BETWEEN statement to
AND (tt.transaction_dt >= ? and tt.transaction_dt <= ?)
另外，我的参数"按钮显示为灰色.
Also, my "Parameters" button is greyed out.
我怀疑它与查询本身有关，也许是因为参数埋在子查询中.我对此还不够了解.无论如何，任何有关如何使它正常工作的建议.我听说您可以创建一个视图并查询该视图，但目前我对SQL和Excel的了解有限.
I suspect its something to do with the query itself, maybe because the parameters are buried in subqueries. I don't know enough about it. Any suggestions on how to get this working, anyway that works. I've heard that you can create a view and query the view, but I'm at the limit of my knowledge of SQL and Excel at this point.
我已经尝试过此解决方案，但未成功:来自Excel的SQL子查询参数
I have already tried this solution without success: SQL Sub-query parameters from Excel
我只是从以下问题中尝试了behonji的建议: 如何向无法在Excel中以图形方式显示的外部数据查询中添加参数?
I just tried behonji's suggestions from the following question: How to add parameters to an external data query in Excel which can't be displayed graphically?
在这里，我成功地在Excel中创建了参数化查询，如下所示:
Here I successfully created a parametized query in Excel, as follows:
SELECT merchant_t.id
FROM XXX.dbo.merchant_t merchant_t
where start_dt = ?
and create_dt = ?
and status = ?
然后，我将该查询替换为我的实际查询，并将其参数化如下:
Then, I substituted that query with my actual query, which I parameterized as follows:
WITH
CTE_Merchants
AS
(
SELECT distinct
me.id, me.merchant_num, me.merchant_nm
,count(tt.id) as num_transactions
,CASE WHEN me.status = 'A' THEN 'Yes' ELSE 'No' END as production_mode
FROM
merchant_t me
LEFT OUTER JOIN transaction_t tt
ON tt.merchant_id = me.id
AND (tt.transaction_dt >= ? and tt.transaction_dt <= ?)
WHERE me.status = ?
GROUP BY me.id, me.merchant_num, me.merchant_nm, me.status
)
SELECT
CTE_Merchants.id
,CTE_Merchants.merchant_num
,CTE_Merchants.merchant_nm
,CTE_Merchants.num_transactions
,CTE_Merchants.production_mode
,A1.is_live
,A2.last_IBA_transaction_dt
FROM
CTE_Merchants
OUTER APPLY
(
select top 1
transaction_t.trans_live AS is_live
from transaction_t
where
transaction_t.merchant_id = CTE_Merchants.id
order by transaction_dt desc
) AS A1
OUTER APPLY
(
select top 1
transaction_t.transaction_dt AS last_IBA_transaction_dt
from transaction_t
where
transaction_t.merchant_id = CTE_Merchants.id
and transaction_t.bank_txt = 'IBA'
order by transaction_dt desc
) AS A2
;
我得到与以前完全相同的错误.
I get exactly the same errors as before.
推荐答案
我对Excel以及如何从Excel运行查询一无所知，但作为最后的选择，您可以尝试将复杂的查询包装到存储过程或表中值函数，然后从Excel调用它.像下面这样.
I know nothing about Excel and how to run queries from Excel, but as a last resort you can try to wrap your complex query into a stored procedure or a table-valued function and call it from Excel. Something like below.
此外，即使您在技术上可以直接将复杂的查询直接输入到Excel电子表格中，也要为数据库有一个明确定义的接口(以过程/函数的形式).如果需要，它将使维护代码和配置权限变得更加容易. (您可以授予Excel用户权限，使其仅执行此存储过程，而不能执行其他任何操作，以使他们不会与数据库混淆.)
Besides, it is good to have a clearly defined interface to a DB (in a form of a procedure/function), even if you technically could put a complex query into the Excel spreadsheet directly. It will make it easier to maintain your code and configure permissions if needed. (You can give your Excel user rights to execute just this stored procedure and nothing else, so that they could not mess with the database.)
存储过程
CREATE PROCEDURE [dbo].[ReadMerchants]
@ParamStartDate date,
@ParamEndDate date,
@ParamStatus nvarchar(10)
AS
BEGIN
SET NOCOUNT ON;
-- Your query using the stored procedure parameters
SELECT
...
;
END
这就是您从Excel调用它的方式.
This is how you call it from Excel.
EXEC [dbo].[ReadMerchants]
@ParamStartDate = ?,
@ParamEndDate = ?,
@ParamStatus = ?
表值函数
CREATE FUNCTION [dbo].[GetMerchants]
(
@ParamStartDate date,
@ParamEndDate date,
@ParamStatus nvarchar(10)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT
...
)
这就是您从Excel调用它的方式.
This is how you call it from Excel.
SELECT * FROM [dbo].[GetMerchants](?, ?, ?)
(显然，最好显式列出所有列，而不是键入*.)
(Obviously, it is better to explicitly list all columns, rather than type *.)
如果存储过程和表值函数都可以使用Excel，则我个人将使用存储过程-如有必要，您可以在其中添加任何类型的复杂逻辑.功能受到更多限制.
If both stored procedure and table-valued function work with Excel, I'd personally use stored procedure - you can put any kind of complex logic into it if necessary. Functions are more limited.
这篇关于Excel的参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
