Excel参数化查询

Question

更新：

我设法让以下查询使用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

这个查询太接近了。我所需要做的就是添加和删除下面的查询注释中所示的行：

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：

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

如果我将它们参数化，就会得到错误：

"Syntax error or access violation"

很明显，我需要那些参数化的日期。

结束更新

我有一个SQL server查询如下：

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电子表格中使用该查询，并且需要将查询注释中指示的变量参数化。

以下是Excel数据连接对话框：

​

​

问题是:当我试图将变量参数化时，例如更改

AND tt.transaction_dt BETWEEN '2020-04-01' and '2020-04-30'

至

AND tt.transaction_dt BETWEEN ? and ?

我犯了个错误

无效参数号

紧接着是

无效描述符索引

如果我将中间语句更改为

AND (tt.transaction_dt >= ? and  tt.transaction_dt <= ?)

此外，我的“参数”按钮是灰色的。

我怀疑这与查询本身有关，可能是因为参数被隐藏在子查询中。我对这件事不太了解。任何关于如何让这件事起作用的建议，不管怎样，都是可行的。我听说您可以创建视图并查询视图，但目前我对SQL和Excel的了解有限。

我已经尝试过这个解决方案，但没有成功：SQL Sub-query parameters from Excel

编辑

我刚刚从以下问题中尝试了behonji的建议：How to add parameters to an external data query in Excel which can't be displayed graphically?

在这里，我成功地在Excel中创建了一个参数化查询，如下所示：

SELECT merchant_t.id
FROM XXX.dbo.merchant_t merchant_t
where start_dt = ?
and create_dt = ?
and status = ?

然后，我用实际的查询替换了该查询，并将其参数化如下：

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
;

我所犯的错误和以前完全一样。

Answer 1

我对Excel和如何从Excel运行查询一无所知，但作为最后手段，您可以尝试将复杂的查询打包到存储过程或表值函数中，然后从Excel调用它。就像下面这样。

此外，有一个与DB的明确定义的接口(以过程/函数的形式)是很好的，即使从技术上讲，您可以将复杂的查询直接放入Excel电子表格中。如果需要，它将使维护代码和配置权限变得更容易。(您可以授予Excel用户只执行此存储过程的权限，而不执行其他任何操作，这样他们就不会破坏数据库。)

存储过程

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中调用它的方式。

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中调用它的方式。

SELECT * FROM [dbo].[GetMerchants](?, ?, ?)

(显然，最好显式列出所有列，而不是输入*。)

如果存储过程和表值函数都与Excel一起工作，我会亲自使用存储过程--如果有必要，您可以将任何复杂的逻辑放入其中。功能比较有限。

Answer 2

请尝试像这样编辑您的查询

declare @dateFrom datetime = ?
declare @dateTo datetime = ?
declare @status varchar(20) = ?

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 >= @dateFrom and tt.transaction_dt <= @dateTo)
    WHERE me.status = @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
;