join table

2005-03-21
2013-04-11
  • Hello.

    I use the pysap module since january and it's great !

    But I'm new to sap and I have a question :
    To query sap, I use the read_table function.
    Can I make a join between two tables directly ?

    For exemple :
    I have the customer's table (knvv) and the name of the
    customer is in another table (kna1).
    Can I make a join between the two tables directly
    from pysap ?

    Or must I make a query in sap and use it in pysap ?
    And how, if I do ?

    Thanks

     
    • Unfortunatelly, there is no direct support for joins in pysap. The read_table method is just a wrapper for rfc_read_table function defined in sap which supports reading from single table only. Besides it has limit on maximum record size (around 256 bytes, I beleive) -trying to read entire record from larger tables (kna1 included) will produce data_buffer_error.
      A quick workaround for joins in simpler cases is the following snippet (asuming sap_conn is your RfcConnection instance, fields1 is listing of field names to be read from knvv and field2 is listing of filed names from kna1):
      # warning: partialy tested code
      # get_fieldlist method returns a list of field declarations form sap, suitable to pass to the create_structure
      field_defs1 = conn.get_fieldlist('KNVV', *fields1)
      field_defs2 = conn.get_fieldlist('KNA1', *fields2)
      # create structure containing fields from both lists
      S1 = pysap.create_structure(field_defs1 + fields_defs2)
      # create internal table from resulting structure
      res = pysap.ItTable('res',  S1)
      tab_knvv = sap_conn.read_table('KNVV', fields=fields1, options=[...])
      for wa in tab_knvv:
      ....# create new line for table res
      ....wa1 = res.struc()
      ....# copy fields from knvv
      ....wa1.copy_corresp_from(wa)
      ....# read corresponding record from kna1
      ....tab_kna1 = conn.read_table('KNA1', options=["KUNNR = '%s'" % wa.kunnr.int_value], fields=fields2, max_rows=1)
      ....# copy field values from kna1 to table res
      ....wa1.copy_corresp_from(tab_kna1[0])
      ....res.append(wa1)

      res will then contain joined records from knvv and kna1. fields1 must contain key fields for the querry (kunnr), field names must not be duplicated between lists.
      This method is not the most effective perfomance-wise as it needs one database access per every line of master dataset (knvv in our example) - it should probably be avoided for larger datasets.

       
    • Thanks for your answer Klavdij.

      And for your example.

      Actually I have a class customers who I make two requests (one for knvv, one for kna1) and join them in python. My class is like a cache.

      But, the problem is the performance ! And if a made a
      request from statistics with a few lines, with my method,
      I read the entire table of curstomers.

      I will test your method. I think it's not perfect but better
      than mine !

      And I can mix your method with mine (a class object
      who cache the data and access to sap only if the data
      are not fetched yet.

      Thank you very much for your hard job and your answer.