使用IN子句从Excel中进行参数化查询 [英] Parameterized query from Excel with IN clause

问题描述

我有一个从Excel 2016工作表到IBM DB2数据库的MS Query连接.我使用参数化查询并将参数值链接到Excel单元格.虽然像= < > like这样的奇异值"子句可以工作，但我无法使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，如果TRUE，则在D列中填充ID.我期望使用一个辅助单元格来合并/合并我想用于IN子句的D列中的所有值(尚未完成).

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会产生错误消息: "Bad parameter type. Microsoft Excel is expecting a different kind of value than what was provided".

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 ?

推荐答案

在此处发布此信息，以供将来的google用户使用.因此，该解决方案对我来说有点开箱即用-尽管仍然需要增强.

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中准备好的语法填充子句中的查询命令文本.对我来说真的是开箱即​​用！

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屋！