Passing multiple values to ABAP TEXT FILTER with VBA

Question

I've created a macro to read data from SAP Table using the BAPI BBP_RFC_READ_TABLE.

It successfully compiles and runs for a single parameter if I use the FILTER VIEW:

objOptTab(objOptTab.RowCount, "TEXT") = "EBELN EQ '12345'"

However, the goal of the code is to be able to pass an array of strings as the textfilter, therefore the ekknPurchaseOrders variable exists. As SAP uses SQL Queries and share a lot of syntax with general SQL language, I would assume I could pass below as a TEXT FILTER:

ekknPurchaseOrders = "('12345', '67890', '101112')"

This is why the below line is there in the first place:

"EBELN IN " & ekknPurchaseOrders'

Running the code/executing BAPI with EBELN IN ".. results in an SQL_PARSE error, because the syntax is invalid.

Any suggestions as to what syntax I may utilize to pass a collection of multiple values to the EBELN TEXT FILTER?

Take note that the TEXT FILTER syntax is changed from 'EQ' to 'IN' when the error occurs. Passing multiple values using ekknPurchaseOrders and not changing syntax to 'IN' returns the value for the first element in ekknPurchaseOrders.

    Public Const worksheetName As String = "EKKN"
    
    Public Sub GetEKPOtable()
    
        Dim funcs As SAPFunctions
        Dim ws As Worksheet
        
        Set ws = ThisWorkbook.Worksheets(worksheetName)       
             
        
        'LOG IN TO SAP
        Set sapConn = CreateSapFunctions()
        If sapConn Is Nothing Then
            Exit Sub
        End If
        
        'DECLEARE FUNCTION EXPORTS
        Set objRfcFunc = sapConn.Add("BBP_RFC_READ_TABLE")
        Set objQueryTab = objRfcFunc.Exports("QUERY_TABLE")
        Set objDelimiter = objRfcFunc.Exports("DELIMITER")
        Set objRowCount = objRfcFunc.Exports("ROWCOUNT")
        
        'SET VALUES
        objRowCount.Value = "99999999"
        
        Set objOptTab = objRfcFunc.Tables("OPTIONS")
        Set objFldTab = objRfcFunc.Tables("FIELDS")
        Set objDatTab = objRfcFunc.Tables("DATA")
        
        objQueryTab.Value = "EKPO"  'TABLE
        objDelimiter.Value = "|"    'DELIMITER
        objOptTab.FreeTable
        
        'OPTION FIELD SAP STATEMENT
        objOptTab.AppendRow
        objOptTab(objOptTab.RowCount, "TEXT") = "EBELN IN " & ekknPurchaseOrders 'FILTERS FOR VIEW
        objFldTab.FreeTable
    
    
        'OUTPUT FIELDS
        objFldTab.AppendRow
        objFldTab(objFldTab.RowCount, "FIELDNAME") = "EBELN"
        objFldTab.AppendRow
        objFldTab(objFldTab.RowCount, "FIELDNAME") = "EBELP"
        objFldTab.AppendRow
        objFldTab(objFldTab.RowCount, "FIELDNAME") = "EMATN"
        objFldTab.AppendRow
        objFldTab(objFldTab.RowCount, "FIELDNAME") = "ELIKZ"
    
    
        'IF CALL FAILES, EXIT
        If objRfcFunc.Call = False Then
            MsgBox objRfcFunc.Exception
        End If
        
        Dim objDatRec As Object
        Dim objFldRec As Object
        
        
        'WRITE RESULT TO WS
        Dim lo As ListObject
        Set lo = ws.ListObjects(outputTable)
        
        Call ClearTableData(lo)
        
        For Each objDatRec In objDatTab.Rows
            For Each objFldRec In objFldTab.Rows
                ws.Cells(objDatRec.Index + 8, objFldRec.Index + 7) = Mid(objDatRec("WA"), objFldRec("OFFSET") + 1, objFldRec("LENGTH"))
            Next
        Next
    
    End Sub

Suncatcher · Accepted Answer · 2021-10-21T11:36:14.040

1

Running the code/executing BAPI with EBELN IN ".. results in an SQL_PARSE error, because the syntax is invalid.

WHERE IN syntax perfectly works in RFC_READ_TABLE and BBP_RFC_READ_TABLE, your problem lies elsewhere.

Three moments to pay attention when troubleshooting your code:

OPTIONS parameter has 72char limitation for single line which you probably hit. Check your condition length and split it into several lines if needed.
OPTIONS lines do not "like" lack of spaces so do not spare the characters

instead of line

objOptTab(objOptTab.RowCount, "TEXT") = "EBELN IN ('050112','060201')

push this

objOptTab(objOptTab.RowCount, "TEXT") = " EBELN IN ( '050112', '060201' )

And don't forget to escape values in your ekknPurchaseOrders, every value should be enclosed in single quotes!

edited Oct 21 '21 at 11:36

answered Oct 21 '21 at 11:13

Suncatcher

10,355
10
52
90

Hi! Thank you for the post. I did not know that the OPTIONS parameter has a 72char limitation. This will amputate the project, as the parameters passed usually are between 50 and 200 characters. Are you familiar with any way to increase this length? Or can I append a new row with a new set of OPTIONS? – Havard Kleven Oct 21 '21 at 11:18
Your answer answers the question at hand, thus I'm accepting. It does not completely solve my problem , due to the OPTION character limit. If I do figure out a workaround in the future, I'll post it here. – Havard Kleven Oct 21 '21 at 11:20
`Or can I append a new row with a new set of OPTIONS?` of course. That what I was trying to tell you in in 1st pt. suggesting to split. Your `objOptTab` is a table that can have multiple lines – Suncatcher Oct 21 '21 at 11:34

Passing multiple values to ABAP TEXT FILTER with VBA

1 Answers1