Skip to Content
Technical Articles
Author's profile photo Jörg Krause

Use strings for DB selections

Today, while refactoring an old report, It came to create a dedicated method for a select to MARC in a dedicated db access class. The selection is done using a select range for material numbers.

Until now, I always proceeded like this:

  • search a ready-made ranges type for the needed data type (there is RANGE_T_MATNR)
    • if not found, declare a ranges type myself
  • use the type for passing the selection range.

Now, depending on standard SAP DEV objects (be it function modules, classes, or DDIC types) always brings the slight danger of code crashes when something changes in the standard system. I also made already bad experience when trying to bring some home-made basic programming tools from ERP to other systems like APO or SCM. Many DDIC objects do not exist there.

So I thought of reducing the dependencies. What about a “range of string”? Will it work in a selection? I wasn’t really sure, so I gave it a try:

First, I defined the interface of my method using generic standard tables for the ranges:

  methods read_plant_materials
    importing plant          type string
              material_range type standard table
    returning value(result)  type string_table.

Then, I call the method with

  method test.
    data material_range type range of string.
    material_range          =
      value #(
        ( sign = 'I' option = 'CP' low = `00000000000002*` ) ).
    data(result) = iut->read_plant_materials(
      plant = `0001`
      material_range = material_range ).
  endmethod.

As you see, also the plant (usually I used the type WERKS_D) for it, is being passed as string. Furthermore I use a string table to capture the results of the select.

The select itself looks pretty normal:

  method zif_pp62_db_access~read_plant_materials.
    select matnr
      from marc
      where matnr in @material_range and
            werks = @plant and
            lvorm = @space
      into table @result.
  endmethod.

The test passes with no problems.

What do you think about this approach? Am I causing maybe much cost for converting everything from and to strings? Let me know!

Assigned Tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Raphael Pacheco
      Raphael Pacheco

      Hi Jörg Krause !

      Pretty interesting blog, but I missed some more details about performance between these two range forms.

      BR,

      Pacheco.

      Author's profile photo Jörg Krause
      Jörg Krause
      Blog Post Author

      I did not make any performance measures yet. First, I wanted to know if there is already any experience around...

      Author's profile photo Raphael Pacheco
      Raphael Pacheco

      I never tried it before read this blog hehe. Surelly I'll try it soon.

      BR,

      Pacheco.

      Author's profile photo Matthew Billingham
      Matthew Billingham

      I did exactly this for generic (text based) ranges. Works like a charm. Thanks for writing it up.

      Author's profile photo Klaus Kronawetter
      Klaus Kronawetter

      Hi Jörg,

      I don't really see the advantages of this approach. You have to be much more careful with conversions / data formats (for example you cannot use an ALPHA conversion on the string). Also, sooner or later you will need to convert the result into the right data type (for example to display the right column header in an ALV). Thirdly, you want to use the right type for a selection screen to enable value help etc. for the user - imagine a complex selection screen where every field is declared as string.

       

      BR, Klaus

      Author's profile photo Jörg Krause
      Jörg Krause
      Blog Post Author

      Thanks for your thoughts!

      Regarding the alpha conversion, I do not agree:

      data matnr type matnr.
      data matnr_string type string.
      
      matnr = |{ matnr_string alpha = out }|.

      This should do the job.

      Regarding selection screens: Since I pass the ranges as generic STANDARD TABLE there is no need to convert them. A string-based range would occur when I have to mount the RANGE in the coding. However, I can pass a "normal" range as well as a string-based range to my selector.

      In general, everything you would use in the User UI should of course refer to DDIC objects. I never would use a generic string in an ALV table or on a selection screen. The scope of this blog was intended for classes that do the backend work.

       

      Author's profile photo Klaus Kronawetter
      Klaus Kronawetter

      Of course you can use another, typed variable, but as I said, you cannot alpha convert the string itself. Now you have two variables that store the same information, which I think is unnecessary.

      Author's profile photo Jörg Krause
      Jörg Krause
      Blog Post Author

      Well, not really the same information. One has the internal material number, the other the external one. In internal logic, you normally should not be in need of an external representation of data. It comes to conversion when you show something on the screen or you make an output of any other type. In this case, I always use separate data objects following MVP design. So this approach does not multiply the number of my variables.

      Author's profile photo Uwe Fetzer
      Uwe Fetzer

      For quick'n'dirty devolpments it's totally fine, but for "real business" I'd prefer the typed ranges. You'd miss the semantics and you have therefore to document (more) your coding.

      Author's profile photo Jörg Krause
      Jörg Krause
      Blog Post Author

      So you think the variable MATERIAL_RANGE is missing semantics? If so, I should have chosen a better name, for instance MATERIAL_NUMBER_RANGE. Setting a dictionary type adds semantics on a meta level, but I think a program should be readable immediately, not only after checking the type of a variable.

      Author's profile photo Michael Keller
      Michael Keller

      Hi Jörg,

      perhaps you can add some range type definition to your interface ZIF... (if you use one)? So you will always get it with you when you “manually” transport your development objects from one type of system to another? That could help to reduce the dependencies to DDIC development objects. Just a “fast” thought and not well proofed ?

      If you are interested, here is an approach I tried in the past to deal with selection criteria and getting them into my main class.

      Author's profile photo Jörg Krause
      Jörg Krause
      Blog Post Author

      That’s true. Local types are suitable for coding that have to run in different system environments. The use of Intrinsic types such as string was also a claim for less noisy code.

      methods read_material
        returning value(materials) type string_table.
      
      " compare to
      
      methods read_material
        returning value(materials) type zif_pp62_db_access=>ty_materials.

       

       

      Still I’m not convinced about the approach. I am applying it to my actual project and collect my experiences. 

      Author's profile photo Amarpreet Singh
      Amarpreet Singh

      If I was reviewing/ reading  somebody else’s code I would prefer the second ( noisy code ) over the first option.

      It would just give me more confidence about what’s going on.

       

      Again, you could argue that it’s because of our lack of experience in using the string approach. 🙂

      Author's profile photo Scott Lawton
      Scott Lawton

      I would have to think about it more and/or do some experimenting to be sure, but it seems like this approach could potentially pose a greater risk for SQL injection. Something worth testing, at least!

      Author's profile photo Jörg Krause
      Jörg Krause
      Blog Post Author

      I was now going on for some day refactoring an old program using the string approach.

      As I also notice reading the comments above, the approach is not only about using strings in SQL, but moreover about using simple types in program code. More precise, the approach is this:

      • Prefer using intrinsic types instead of DDIC types for variable values
        • String for all kind of alphanumeric fields
        • Integers
        • Floats
      • Use generic types for selection ranges in method interfaces
        • so you can pass a select-option from a report as well as a generic range of string
      • To keep the code clear and understandable, use good variable names (see also https://github.com/SAP/styleguides/blob/master/clean-abap/CleanABAP.md#use-descriptive-names)
      • When calling foreign methods and modules, conversion may be necessary
      • For things displayed in the front end, DD types are always necessary.

      I am still in phase of evaluation. When my actual refactoring is finished, I will come back with some code examples. Meanwhile let me know your thoughts.

       

      Author's profile photo Glen Simpson
      Glen Simpson

      Hi Jörg

      Coincidence! I came across this technique for the first time, just last week. It was in a standard update routine in S/4HANA:

      function finplan_upd_delete.
      *"----------------------------------------------------------------------
      *"*"Update Function Module:
      *"
      *"*"Local Interface:
      *"  IMPORTING
      *"     VALUE(IT_AWTYP_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_RYEAR_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_POPER_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_CATEGORY_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_RACCT_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_RBUKRS_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_RCNTR_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_PRCTR_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_RFAREA_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_RBUSA_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"     VALUE(IT_KOKRS_RANGE) TYPE  FINS_PLAN_API_RANGE_TT OPTIONAL
      *"----------------------------------------------------------------------
      
        delete from acdocp where
            awtyp in it_awtyp_range and
            ryear in it_ryear_range and
            poper in it_poper_range and
            category in it_category_range and
            racct in it_racct_range and
            rbukrs in it_rbukrs_range and
            rcntr in it_rcntr_range and
            prctr in it_prctr_range and
            rfarea in it_rfarea_range and
            rbusa in it_rbusa_range and
            kokrs in it_kokrs_range.

      Note that the line type of FINS_PLAN_API_RANGE_TT uses strings:

      @EndUserText.label : 'Range for plan data'
      @AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE
      define type fins_plan_api_range {
        sign   : bapisign;
        option : bapioption;
        low    : abap.string(0);
        high   : abap.string(0);
      
      }

      I quite like the use of a generic range in this exact situation (an update module) as it avoids the creation of separate range table types in the data dictionary (why create them if they're not needed?!).

      But like Michael Keller, I like to create local range table types in my interfaces/classes if I know they are not needed globally.

      Regards

      Glen