Technical Articles
get customizing from different levels
Dear community, from time to time I have the need to read customizing from custom defined database tables. You probably too? ๐ Often these database tables have one or two organizational elements as primary key. For example company code and purchasing organization. Or company code and sales organization. The following logic should be used while reading:
“If an entry is found with both organizational elements, this must be used. Otherwise an entry is only searched for with the first organizational element, the second one is initial.“
example table design
“ORGANIZATION” could be the company code and “SUB_ORGANIZATION” could be the sales organization. Let’s call them different “levels”. I’ve made some example entries to play with.
example entries
I’ve programmed three different ways of solving this problem/requirement (see methods “GET_BY_SOLUTION_<NR>”). I’ve seen them in a slightly modified form in real projects. Personally, I like solution 2. But I assume that there are more, perhaps much simpler solutions? Whereby simpler is perhaps not necessarily more understandable or more elegant. Who knows? ๐
CLASS lcx_error DEFINITION INHERITING FROM cx_static_check.
ENDCLASS.
CLASS lcx_error IMPLEMENTATION.
ENDCLASS.
CLASS lcl_switches DEFINITION.
PUBLIC SECTION.
METHODS constructor.
METHODS get_by_solution_1
IMPORTING
iv_organization TYPE char4
iv_sub_organization TYPE char4
RETURNING
VALUE(rs_result) TYPE zswitches.
METHODS get_by_solution_2
IMPORTING
iv_organization TYPE char4
iv_sub_organization TYPE char4
RETURNING
VALUE(rs_result) TYPE zswitches.
METHODS get_by_solution_3
IMPORTING
iv_organization TYPE char4
iv_sub_organization TYPE char4
RETURNING
VALUE(rs_result) TYPE zswitches.
PRIVATE SECTION.
METHODS static_select
IMPORTING
iv_organization TYPE char4
iv_sub_organization TYPE char4
RETURNING
VALUE(rs_result) TYPE zswitches.
METHODS dynamic_select
IMPORTING
iv_condition TYPE string
RETURNING
VALUE(rs_result) TYPE zswitches
RAISING
lcx_error.
ENDCLASS.
CLASS lcl_switches IMPLEMENTATION.
METHOD constructor.
ENDMETHOD.
METHOD get_by_solution_1.
SELECT SINGLE *
FROM zswitches
INTO rs_result
WHERE organization = iv_organization
AND sub_organization = iv_sub_organization .
IF sy-subrc <> 0.
SELECT SINGLE *
FROM zswitches
INTO rs_result
WHERE organization = iv_organization
AND sub_organization = space.
IF sy-subrc <> 0.
RETURN.
ENDIF.
ENDIF.
ENDMETHOD.
METHOD get_by_solution_2.
rs_result = static_select( iv_organization = iv_organization
iv_sub_organization = iv_sub_organization ).
IF rs_result IS INITIAL.
rs_result = static_select( iv_organization = iv_organization
iv_sub_organization = space ).
ENDIF.
ENDMETHOD.
METHOD get_by_solution_3.
IF iv_organization IS INITIAL OR iv_sub_organization IS INITIAL.
RETURN.
ENDIF.
DATA(lv_condition) = |organization = { iv_organization } AND | &&
|sub_organization = { iv_sub_organization }|.
TRY.
rs_result = dynamic_select( lv_condition ).
CATCH lcx_error.
IF lv_condition CS 'space'.
RETURN.
ELSE.
lv_condition = |organization = { iv_organization } AND | &&
|sub_organization = space |.
RETRY.
ENDIF.
ENDTRY.
ENDMETHOD.
METHOD static_select.
SELECT SINGLE *
FROM zswitches
INTO rs_result
WHERE organization = iv_organization
AND sub_organization = iv_sub_organization.
IF sy-subrc <> 0.
RETURN.
ENDIF.
ENDMETHOD.
METHOD dynamic_select.
TRY.
SELECT SINGLE *
FROM zswitches
INTO rs_result
WHERE (iv_condition).
CATCH cx_sy_dynamic_osql_error INTO DATA(osql_error).
MESSAGE osql_error->get_text( ) TYPE 'I'.
RAISE EXCEPTION NEW lcx_error( ).
ENDTRY.
IF sy-subrc <> 0.
RAISE EXCEPTION NEW lcx_error( ).
ENDIF.
ENDMETHOD.
ENDCLASS.
My example serves as a basis for discussion here. Any proposals to solve the requirement on a different way? ๐
Best regards, thanks for reading and please stay healthy
Michael
P.S.: Please support the virtual wishing well.
P.S.S.: Not tired of reading blogs? Visit the Devtoberfest 2020.
Hi Michael,
Quite a common scenario, thanks for initiating this discussion. My observation - Probably 4th option could be to select data from table using first key field only and then do the READ in code where IF~ELSE for second key field can be used. This way, DB is hit only once if second key field is space. Otherwise, we are hitting it twice.
I am looking forward to more comments here...
Gaurav
How could I have forgotten this solution? Thanks for the note!
In the past, I've been using something like this:
This is working even with many optional key fields, returning the "best match" in first line (the sequence of columns in the "order by" clause defines the priorization!).
In my company we are using a small framework with a set of class methods for that purpose, which is reading all lines in a similar way. It reads all relevant lines first, then loops through the result identifying the best match.
Karl
Nice solution. Have to check that. Thanks! ๐
Hey Michael,
the solution to a requirement that every company has sooner or later... ๐
three suggestions:
For my company I build a complete dynamic tool for switching exits on and off.
Cheers
~Enno
Hi Enno, I like the idea of having a longer org field with a delimiter to split the value in meaningful parts. That's an interesting design idea. It could have impact to some select possibilities but that's an other discussion. I didn't unterstand the point with the "exits". Could you provide an example? Thanks in advance.
Exit = Switch ?
Yes Michael, I do have an elegant solution ?
Note: I have not compiled the code; you will get syntax errors but I will try and give you the idea.
ย
Define your Exception object as Resumable.
Then:
Definition:
ย
Implementation:
Caller:
ย
Lunch is getting cold so I must go right now ?
Another interesting solution. However, I have to admit that I have to debug them on the system to understand everything ๐
Yeah โฆ. I find it quite powerful and elegant because when it throws the exception I have all the information in the exception object of what exactly happened inside to be able to control the subsequent flow/processing without having to depend on additional helper variables and/or IF/ELSE statements etcโฆ I could just continue to call the method like normal as long as it either Resumes it or throws a hard/non-resumable exception.
The point is, from the OO perspective, the outside/caller should have all the information of what exactly happened inside (the method in case of an error happened) thus controlling the subsequent processing steps of the calling program.
Sougata.