Skip to Content

This blog and below code has been inspired by this post: Extract data from SAP table / query by Takahiro Kubo.


My other motivations was, that apparently there is still a lot of people fascinated by the RFC_READ_TABLE. Despite the fact that it is not the right way of getting information out of SAP R/3. I believe the below way will inspire those people to switch their minds.

To identify a query within the SQ01 system you’ll need a UserGroup and QueryName. I believe you are familiar with those terms.

For the SQVI query geting the UserGroup is not that obvious, but we can either extract it from the report name.. From menu QuickView->Additional functions->Display report name, then user group is in the middle of the name, starting with SYSTQV and has 12 characters.

Clipboard01.png

Or you get the user’s user group via How to find and execute or work with other users quick views (or quick views created with old user-id)?

Specifying input for the selection screen is slightly more complicate (not that much) therefore I recommend to read Takahiro’s code or browse  through SCN. For the below example we will call a prepared variant by name.

When creating a variant, select SAP List Viewer as output. Choosing MS Excel may lead to an exception, when calling through RFC.

1. First we invoke the RSAQ_REMOTE_QUERY_CALL with the given parameters. If we call via RFC we want DATA_TO_MEMORY = ‘X’ otherwise output tables (LDATA) are not filled. For EXTERNAL_PRESENTATION you may choose out of below values. I test the ‘Z’ only.

‘S’ convert except for dates

‘X’ convert

‘E’ convert curr

‘Z’ convert curr except for dates


static DataTable FetchQuery(string dest, string user_group, string query, string variant) {
    RfcDestination _dest = RfcDestinationManager.GetDestination(dest);
    IRfcFunction fn = _dest.Repository.CreateFunction("RSAQ_REMOTE_QUERY_CALL");
    fn.SetValue("USERGROUP", user_group);
    fn.SetValue("QUERY", query);
    fn.SetValue("VARIANT", variant);
    fn.SetValue("DATA_TO_MEMORY", "X");
    fn.SetValue("EXTERNAL_PRESENTATION", "Z");
    fn.Invoke(_dest);





2. We store the result into a DataTable, where column names are the query’s field names (LISTDESC-FNAME). Takahiro’s way 🙂


   DataTable result = new DataTable();
    IRfcTable listdesc = fn.GetTable("LISTDESC");
    for (int i = 0; i <= listdesc.Count - 1; i++) {
        listdesc.CurrentIndex = i;
        DataColumn column = new DataColumn(listdesc.GetString("FNAME"));
        if (result.Columns.Contains(column.ColumnName)) {
            column.ColumnName = column.ColumnName + "_" + listdesc.GetString("FPOS");
            // for duplicate type
        }
        column.Caption = listdesc.GetString("FDESC");
        result.Columns.Add(column);
    }





3. We need to parse the output data and store them to our table’s rows. The format of LDATA is

LEN:FIELD_DATA,LEN:FIELD_DATA,LEN:FIELD_DATA;LEN:FIELD_DATA,LEN:FIELD_DATA,LEN:FIELD_DATA;

where each field content is prefixed with a number determining it’s length in characters. ‘,’ is a field separator, ‘;’ is a row separator and ‘:’ sparates field length from the data itself. In a first step we concatenate all the LDATA lines into one single string (ldatastr). Those who admit, that this way is not a memory efficient, may implement a read-ahead buffer, but since the RAM memory got cheap, I do not care anymore.


    //read data
    IRfcTable ldata = fn.GetTable("LDATA");
    string ldatastr = "";
    for (int i = 0; i <= ldata.Count - 1; i++) {
        ldata.CurrentIndex = i;
        ldatastr += ldata.GetString("LINE");
    }





4. We read the LDATA content the old telegraph (Electrical telegraph – Wikipedia, the free encyclopedia) way. I.e. read field lenght, check, there is ‘:’, read the field data, check there is ‘,’ or ‘;’ and so on ….


    int pos = 0;
    int col = 0;
    DataRow row = result.NewRow();
    for (; ; ) {
        // read field length
        if (ldatastr.Length - pos < 3) {
            break;
        }
        int fl = int.Parse(ldatastr.Substring(pos, 3));
        pos += 3;
        // assert about ':' separator
        if (ldatastr[pos] != ':') {
            throw new Exception(string.Format("No ':' at {0}", pos));
        }
        pos++;
        // read the field data
        if (ldatastr.Length - pos < fl) {
            throw new Exception(string.Format("Not enough data to read {0} long field at {1}", fl, pos));
        }
        string data = ldatastr.Substring(pos, fl);
        row[col] = data;
        pos += fl;
        switch (ldatastr[pos]) {
            case ',': // continue with the next field
                col++;
                break;
            case ';': // continue with the next row
                col = 0;
                result.Rows.Add(row);
                row = result.NewRow();
                break;
            default: // nobody would expect that
                throw new Exception(string.Format("Unexpected EOF at {0}", pos));
        }
        pos++;
    }
    return result;
}





5. If you need to dump the result into a TAB delimited format ….


   if (result != null) {
        using (StreamWriter writer = new StreamWriter("output.txt", false, Encoding.UTF8)) {
            string sline = "";
            for (int i = 0; i < result.Columns.Count; i++) {
                if (i > 0) {
                    sline += "\t";
                }
                sline += result.Columns[i].ToString();
            }
  writer.WriteLine(sline); // write the header
            foreach (DataRow r in result.Rows) {
                sline = "";
                for (int i = 0; i < r.ItemArray.Length; i++) {
                    if (i > 0) {
                        sline += "\t";
                    }
                    sline += r[i].ToString();
                }
                writer.WriteLine(sline); // write the row
            }
        }
    }





To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

  1. Damir Husejnagic

    Hi Hynek,

    Thank you for useful blog post. Is it possible to include parameters for query and how?

    I tried with structure but I always got an error:

    SAP.Middleware.Connector.RfcAbapException: NO_SELECTION.

    Is it possible to get some example how to do that?

    (0) 
    1. Hynek Petrak Post author

      Hi Damir,

      sure it is possible, but I wanted to keep the blog simple and straightforward.

      Also for my purposes, I’m more comfortable with prepared variants.

      For how to prepare selections you may examine Takahiros code (Invoke method):

      SAPExtractorDotNET/SAPQueryExtractor.vb at master · icoxfog417/SAPExtractorDotNET · GitHub

      In short, you may examine the selection parameters through RSAQ_REMOTE_QUERY_FIELDLIST and then put them into the SELECTION_TABLE as e.g. ‘SELNAME’:’SP$00005′,’KIND’:’S’,’LOW’:’119004′.

      (0) 
  2. Damir Husejnagic

    Hi Hynek,

    I have a question regarding parameters for query. Is it possible to send array into SELECTION_TABLE (something like IN operator in SQL)?

    I would like to send a list of confirmation numbers in query but I don’t know which operand to use.

    (0) 
  3. Tomasz Piwowarski

    Hi Hynek

    I have a problem with USERGROUP

    RSAQ_REMOTE_QUERY_CALL see only query which is created in Works area -> standard area (client-specific).

    I have many query in works area -> Global area (cross-client).

    Is there a chance to run a query form Global area (cross-client)?

    (0) 
  4. Pavel Smarda

    Hi Hynek,

    how fast is this parsing when using this algorithm in .NET or over some dll? I have the same one in VBA in Excel. I cannot use the other way because or our company policy. It’s too slow. 80.000 in output takes more than 40 minutes. But this result is when using splip functions with delimiters “;”. “,” and “:”. (I know the risc.) Your exact parsing algorithm is even slower.

    BTW, I added some lines in header parsing to resolve data types using “FTYP” in “LISTDESC” and INTTYPE (see SAPdatasheet.org).

    (0) 
    1. Hynek Petrak Post author

      Hi Pavel,

      seems to be a good reason to go for .NET connector. See my benchmark, including time required to execute the query in the SAP and fetch the data from the R/3 system:

      Query on price conditions:

      47 MB of data, 770000 records => runtime 2 minutes 20 seconds

      Query on Material master data:

      23 MB of data, 96000 records => runtime 3 minutes

      (0) 
      1. Hynek Petrak Post author

        I’ve made a further analysis and in my C# function 98% of the above time is spent on SAP side executing the query and only 2% parsing the result. I.e. the result is parsed in a second or so …

        (0) 
      2. Pavel Smarda

        Hi Hynek.

        Good idea but not useful because of the company policy. I found the problem elsewhere. I switched off automatic recalculation and screenupdating during the process of parsing. I always miss this functionality of the Excel.

        (0) 
  5. Pavel Smarda

    Hi Hynek.

    I have another two themes connected to this.

    1) You can fill VARIANT or you can fill input fields instead. But when I do so, I do not know how to check if I entered it well. Or I’m almost sure that I did it wrong a bit. Do you have any script for checking this before .Call?

    2) Do you have any return value about how much data will come during calling? Just because of estimation time calculation.

    (0) 

Leave a Reply