带有 IN 子句的 Excel 参数化查询 [英] Parameterized query from Excel with IN clause

问题描述

我有一个从 Excel 2016 表到 IBM DB2 数据库的 MS Query 连接.我使用参数化查询并将参数值链接到 Excel 单元格.而奇异值"子句，例如 = <>喜欢 的工作，我无法让 IN 子句处理多个值，否则超级简单的查询.

I have a MS Query connection from Excel 2016 sheet to IBM DB2 database. I use parameterized query and link parameter values to Excel cell. While "singular value" clauses such as = < > like work, I can't get IN clause to work with multiple values but otherwise super simple query.

这是我如何生成参数值的简单演示数据集:

Here's a simple demo dataset how I produce the parameter values:

D 列公式是 =IF(C2>5,A2&",","")，它检查 C 列的值是否大于 5 并填充 ID如果 TRUE 则为 D 列.我期望使用辅助单元格来合并/合并列 D 中的所有值，我想将其用于 IN 子句作为值(尚未完成).

Column D formula is =IF(C2>5,A2&",","") which checks column C value for higher than 5 and populates ID in column D if TRUE. I'm expecting to use a helper cell to merge/concat all values in column D which I want to use for IN clause as value (yet to be completed).

如何在单元格中使用 "value1, value2, value3, ..." 来运行 IN 子句查询?这可能吗? IN 子句在链接单元格保持值 1 但是 1 时工作正常, 3 产生错误信息:错误的参数类型.Microsoft Excel 期望的值与所提供的值不同".

How can I use "value1, value2, value3, ..." in a cell to run an IN clause query? Is it possible at all? IN clause works fine when the linked cell holds value 1 however 1, 3 produces error message: "Bad parameter type. Microsoft Excel is expecting a different kind of value than what was provided".

为每个单元格使用单独的参数和 OR 子句在技术上看起来是可行的，但实际上数据集可以轻松达到 50-100 个值，这意味着参数设置量非常大.

Using a separate parameter and OR clause for each cell technically looks to work, however in practice the dataset can easily reach 50-100 values which means crazy amount of parameter setup.

查询本身很简单:

select * from PRODUCTS a
where a.prod_ID in (1,3)

或作为 Excel MS Query 替代方案:

or as Excel MS Query alternative:

select * from PRODUCTS a
where a.prod_ID in ?

推荐答案

在这里为未来的谷歌员工发布.所以这个解决方案对我来说有点开箱即用——尽管仍然需要改进.

posting this here for future googlers. So the solution was a bit out of the box for me - although still needs enhancement.

它检查 M1:M4 范围内的工作表更改事件，然后根据 B1 中准备好的语法填充 IN 子句查询命令文本.对我来说真的是开箱即​​用！

It checks for worksheet change events in range M1:M4 and then populates IN-clause query command text according to the prepared syntax in B1. Really out of the box for me!

不幸的是，它在这里只处理一个 Listobject (1)，虽然我有不止一个应该得到相同的处理 - 但是，很棒的东西.

Unfortunately it only handles a single Listobject (1) here, although I have more than one which should get the same treatment - but nevertheless, great stuff.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim INvaluesCell As Range
    Dim SQLin As String, parts As Variant
    Dim i As Long, p1 As Long, p2 As Long
    Dim qt As QueryTable

    Set INvaluesCell = Range("B1")

    If Not Intersect(Target, Range(INvaluesCell, "M1:M4")) Is Nothing Then

        SQLin = ""
        parts = Split(INvaluesCell.Value, ",")
        For i = 0 To UBound(parts)
            SQLin = SQLin & "'" & parts(i) & "',"
        Next
        SQLin = " IN (" & Left(SQLin, Len(SQLin) - 1) & ")"

        Set qt = Me.ListObjects(1).QueryTable

        p1 = InStr(1, qt.CommandText, " IN (", vbTextCompare)
        If p1 > 0 Then
            p2 = InStr(p1, qt.CommandText, ")") + 1
            qt.CommandText = Left(qt.CommandText, p1 - 1) & SQLin & Mid(qt.CommandText, p2)
        End If

    End If

End Sub

这篇关于带有 IN 子句的 Excel 参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！