Parameterized query from Excel with IN clause

Question

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:

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).

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".

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.

Query itself is something as simple as:

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

or as Excel MS Query alternative:

select * from PRODUCTS a
where a.prod_ID in ?

Answer 1

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

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!

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

Answer 2

This:

select * from PRODUCTS a
where a.prod_ID in ?

Will not ever work.

What would work, is passing the string into a function that can return a set of rows.

select * 
from PRODUCTS a
where a.prod_ID in (select int(column_value)
                    from table ( split(?,',')) tbl
                   )

split() is a user defined table function, taken from IBM's developer works article: How to split a string into a set of rows (anti-LISTAGG)

CREATE OR REPLACE FUNCTION SPLIT(text VARCHAR(32000), split VARCHAR(10))
RETURNS TABLE(column_values VARCHAR(60))
RETURN WITH rec(rn, column_value, pos) AS
        (VALUES (1, VARCHAR(SUBSTR(text, 1, 
                                   DECODE(INSTR(text, split, 1), 
                                          0, 
                                          LENGTH(text), 
                                          INSTR(text, split, 1) - 1)),
                            255),  
                    INSTR(text, split, 1) + LENGTH(split))
         UNION ALL
         SELECT rn + 1, VARCHAR(SUBSTR(text, pos, 
                                       DECODE(INSTR(text, split, pos), 
                                              0, 
                                              LENGTH(text) - pos + 1,  
                                              INSTR(text, split, pos) - pos)), 
                                255),  
                        INSTR(text, split, pos) + LENGTH(split)
          FROM rec WHERE rn < 30000 AND pos > LENGTH(split))
      SELECT column_value FROM rec;

Depending on your platform and version of Db2, there may be some built in options.

On recent versions for instance, you could pass in an XML or JSON string with the values and use XMLTABLE() or JSON_TABLE() to split the string into rows.