Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Keller
Active Contributor
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."


Need an example? Here is a little design I've made to demonstrate what I mean.



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.


10 Comments