More than 1000 parameters in an SQL IN (VBA)

I'm making an oracle query from vba and ran into the limitation that there can't be more than 1000 entries in a SQL IN statement.

   LRow_Last_Product = Workbooks("test.xlsm").Worksheets("Check").Range("A" & Rows.Count).End(xlUp).Row
    
    For Each RngZelle In Workbooks("test.xlsm").Worksheets("Check").Range("A7:A" & LRow_Last_Product)
    
        If StrISINs = "" Then
            StrISINs = "'" & RngZelle.Value & "'"
        Else
            StrISINs = StrISINs & ", '" & RngZelle.Value & "'"
        End If
    Next RngZelle

'WHERE
StrSQL = StrSQL & "t.externref IN (" & StrISINs & ")"

I was thinking about creating a function that would generate a second IN with an OR if there are more than 1000 parameters. What is the most efficient way to solve this?

Thanks

Solution

As I already wrote, it's just a little bit string handling to create the (multiple) IN-Clauses. There are thousands of different ways to do this. Just for fun I came up with the following function.

It first calculates the number of In-Clauses needed and create an array of strings of that size.

Now it writes the data of your range in chunks of n (maxPerClause) into a (one-dimensional) array and uses the Join-function to combine that with a comma-separator (and optional the single quotes if you deal with strings)

Finally it uses the Join-function to join everything together.

Function CreateInClause(r As Range, fieldName As String, Optional asString As Boolean = True) As String
    Const maxPerClause = 10
    Dim clausesNeeded As Long
    clausesNeeded = ((r.Count - 1) \ maxPerClause) + 1
    
    Dim data As Variant
    data = r.Value2
    
    ReDim clauses(1 To clausesNeeded) As String
    Dim i As Long
    
    For i = 1 To clausesNeeded
        
        ' Calculate Start and number of elements for the next IN-Clause
        Dim startRow As Long, rowCount As Long
        startRow = (i - 1) * maxPerClause + 1
        If startRow + maxPerClause > r.Count Then
            rowCount = r.Count - startRow + 1
        Else
            rowCount = maxPerClause
        End If
        
        ' Copy the elemens of the next IN-Clause to 1-dimensional array
        ReDim elements(1 To rowCount)
        Dim j As Long
        For j = 1 To rowCount
            elements(j) = data(startRow + j - 1, 1)
        Next
        
        ' Create everything that will go inside the (...) of one IN-Clause
        If asString Then
            clauses(i) = "'" & Join(elements, "','") & "'"
        Else
            clauses(i) = Join(elements, ",")
        End If
    Next
    
    ' Now join the created IN-Clauses to one statement
    CreateInClause = fieldName & " IN (" & Join(clauses, ") OR " & fieldName & " IN (") & ")"
End Function

Just for test reasons I set the number of elements for every IN-Clause to 10 - once you are sure everything works you can set it to 1000.

Explanation:

Consider you have a Range with 26 cells, holding the values from 'A' to 'Z'.

Number of IN clauses = 3 ((26 - 1) \ 10) + 1) (The \ is the integer division operator).

The first iteration will write the values A to J into array elements. Join will combine them to the string 'A','B','C','D','E','F','G','H','I','J' and writes it into clauses(1). Same goes for 2nd and 3rd iteration, only that for the last iteration there are only 6 elements left.

When this is done, you have 3 elements in clauses:

'A','B','C','D','E','F','G','H','I','J'
'J','L','M','N','O','P','Q','R','S','T'
'U','V','W','X','Y','Z'

Now you join those together to create the final IN-Clauses. As the fieldname is needed within the clause, you need to pass it as parameter. You end up with

t.externref IN ('A','B','C','D','E','F','G','H','I','J') OR t.externref IN ('K','L','M','N','O','P','Q','R','S','T') OR t.externref IN ('U','V','W','X','Y','Z')

In your code, you can remove the for-loop and instead write

Dim r As Range
Set r = Workbooks("test.xlsm").Worksheets("Check").Range("A7:A" & LRow_Last_Product)
StrSQL = StrSQL & CreateInClause(r, "t.externref", true)