How to use RFC_READ_TABLE with multiple options / where clauses?

[jedo-tm]

I am using PyRFC / RFC_READ_TABLE and have encountered a challenge when trying to pass multiple conditions. I have created options as a list of dicts

from pyrfc import Connection
MaxRows=50
FromRow=0
table = 'MARD'
fields = ['MATNR','WERKS','LGORT','LABST']
con = Connection(ashost=ASHOST, sysnr=SYSNR, client=CLIENT, user=USER, passwd=PASSWD)
options = [{'TEXT': "MATNR LIKE 'xxx%'"}, {'TEXT': "WEKRS LIKE 'yyy%'"}]
con.call("RFC_READ_TABLE", QUERY_TABLE=table, DELIMITER='|', FIELDS = Fields, \
OPTIONS=options, ROWCOUNT = MaxRows, ROWSKIPS=FromRow)

But i then receive

src/pyrfc/_pyrfc.pyx in pyrfc.pyrfc.Connection.call()
``
src/pyrfc/_pyrfc.pyx in pyrfc.pyrfc.Connection._error()
`
ABAPRuntimeError: RFC_ABAP_RUNTIME_FAILURE (rc=3): key=SAPSQL_WHERE_PARENTHESES, message=A condition specified dynamically has an unexpected format. [MSG: class=, type=, number=, v1-4:=;;;]`

Does anyone have a hint :) ?

[amarvin]

RFC_READ_TABLE accepts a list of dict, as you defined, but then it concatenates them together. So you need to be sure they combine correctly. Try something like:

# instead of this:
options = [{'TEXT': "MATNR LIKE 'xxx%'"}, {'TEXT': "WEKRS LIKE 'yyy%'"}]
# do this:
options = [{'TEXT': "MATNR LIKE 'xxx%'"}, {'TEXT': "AND WEKRS LIKE 'yyy%'"}]

In your way, RFC_READ_TABLE will think that your desired options string is MATNR LIKE 'xxx%'WEKRS LIKE 'yyy%'.

Note that RFC_READ_TABLE limits each TEXT value in options to max 72-characters, so I normally construct my desired options as a stingle long string and then split it into 72-character chunks like:

import textwrap

desired_string = "MATNR LIKE 'xxx%' AND WEKRS LIKE 'yyy%' AND (MATNR = 000000... OR MATNR = ...) AND ..."
options = [
    {"TEXT": substring }
    for substring in textwrap.wrap(desired_string , 72)
]

[amarvin]

You can include parenthesis so that the SAP database knows clearly what rows you want. Try:

options = [
  {'TEXT': "(MATNR eq 'xxx'"},
  {'TEXT': "OR MATNR eq 'yyy')"},
  {'TEXT': "AND WERKS eq 'zzz'"},
]

[chandra-a]

RFC_READ_TABLE accepts a list of dict, as you defined, but then it concatenates them together. So you need to be sure they combine correctly. Try something like:

# instead of this:
options = [{'TEXT': "MATNR LIKE 'xxx%'"}, {'TEXT': "WEKRS LIKE 'yyy%'"}]
# do this:
options = [{'TEXT': "MATNR LIKE 'xxx%'"}, {'TEXT': "AND WEKRS LIKE 'yyy%'"}]

In your way, RFC_READ_TABLE will think that your desired options string is MATNR LIKE 'xxx%'WEKRS LIKE 'yyy%'.

Note that RFC_READ_TABLE limits each TEXT value in options to max 75-characters, so I normally construct my desired options as a single long string and then split it into 75-character chunks.

Hi, currently i am facing this problem. the TEXT value i am passing is a tuple which can be quite long in some cases. Please can you tell me how i can split it into 75 character chunks ? Does OPTIONS really combine all these chunks to form the intended long string? i dont want to loop to call the FM several times to get the output. Thx

[amarvin]

@chandra-a, good question. Yes, OPTIONS does combine these into a long query string. I'll update my answer above with code to split a long string into 72-character pieces for OPTIONS. I did this at my previous company, so I no longer have access to the code that I wrote then which I used for years, but I'll try to write something that should work without being able to test it.

[amarvin]

I saw that in https://answers.sap.com/answers/4063392/view.html the OPTIONS text length limit is 72-characters, not 75-characters as I originally wrote. I've modified my answer and comments to reflect that.

[blmksad]

I just want to access the prod data with ABAP on the QA test system, that is, I don't want to pull a table, I just want to provide a connection, how do I do this?
I would be very happy if you explain in some detail :)
Thanks ?

[blmksad]

I just want to access the prod data with ABAP on the QA test system, that is, I don't want to pull a table, I just want to provide a connection, how do I do this?
I would be very happy if you explain in some detail :)
Thanks ?

[CelianInfo]

OPTIONS are concatenated together as a long string
it doesn't matter how the strings are cut, as long as the length of each string doesn't exceed 72 caracters

[{"TEXT": substring } for substring in textwrap.wrap(filters , 72)] is supposed to work... BUT !

There is a prank...
if a numerical value inside a IN is cut mid-air like the following, it will crash
[{'TEXT': 'AUFPL IN (1206427738,1000009216,1000009053,1000021019,1000061593,1000072'}, {'TEXT': '1,1000087566)'}]

it won't crash if the cut is at the right place
[{'TEXT': 'AUFPL IN (1206427738,1000009216,1000009053,1000021019,1000061593,1000072'}, {'TEXT': ',1000087566)'}]
I don't know why, it's SAP.

So I made an algorithm that takes up to 72 caracters, but cut at last comma encountered

options = "AUFPL IN (1206427738,1000009216,1000009053,1000021019,1000061593,10000721,1000087566)"
clean_options = re.sub(r"\s+", " ", options.strip().replace("\n", " "))
options_wrap = []
current = 0

while (current + 1) < len(clean_options):

    substring = clean_options[current : current + 72]
    last_comma_index = substring.rfind(",")
    last_space_index = substring.rfind(" ")
    last_index = max(last_comma_index, last_space_index)

    # If we haven't reach the end of the substring
    # and we haven't found a space or comma
    if len(substring) != 0 and last_index != 0:

        options_wrap.append(substring[:last_index])
        current = current + last_index
    else:
        options_wrap.append(substring)
        current += 72

return [{"TEXT": substring} for substring in options_wrap]

(Code was updated after my next comment to not cut keywords in the middle)

Maybe it will help someone one day.

In my case I use it to extract SAP tables in cascade, just like a LEFT JOIN in SQL.

[CelianInfo]

For anyone that fall on this discussion I found out you can actually run subqueries inside RFC_READ_TABLE.

Inside the OPTIONS you can do for instance :
( VKBUR = '0104' AND AUART = 'ZRAS' ) OR ( VBELN IN ( SELECT KDAUF FROM AUFK WHERE WERKS = '0104' ) )

This options is run on VBAK. If you wonder why I can type my options as a single long string checkout my last answer.
Take note you HAVE to let a space at each parenthesis.

After testing how can it runs, I can say the subqueries are quick.

[CelianInfo]

Now, If you use a subquery on a table that share the same column name you can :

"( QMEL~VKBUR = '0104' AND QMEL~QMART = 'ZB' ) OR ( QMEL~QMNUM IN ( SELECT VBAK~QMNUM FROM VBAK WHERE ( VBAK~AUART = 'ZRAS' AND VBAK~VKBUR = '0104' ) ) )"

After looking for a bit I found out that cutting instructions in the middle cause issues too.

[{'TEXT': "( QMEL~VKBUR = '0104' AND QMEL~QMART = 'ZB' ) OR ( QMEL~QMNUM IN ( S"}, {'TEXT': "ELECT VBAK~QMNUM FROM VBAK WHERE ( VBAK~AUART = 'ZRAS' AND VBAK~VKBUR = "}, {'TEXT': "'0104' ) ) )"}]

this will fail as SELECT is cut in two. I will need to update my loop to cut at the right places.

[amarvin]

Nice find that OPTIONS supports subqueries! Do you know if the "FIELDS" can also support this like inner join to return fields from another table?

[CelianInfo]

I tried many syntax to play around the evaluation of the call, with no success. I don't think we well be able to join tables together.

Now you can use default SAP views as it joins the data for you.
If you want to see which view is constructed with your tables you can query DD26I. It have for each view the tables associated.

Here is an example with VIQMEL.
If you filter TABNAME for your tables, and filter the result to only keep the VIEWNAME where all the tables are found you will find the right view pretty quickly.