on 04-15-2024 9:14 AM
Dear Community.
My original issue is that I need a Full Outer Join.
Unfortunately, that’s not possible within a CDS View and basically only works directly on HANA DB.
Since we can only access the DB from the application server side, I was hoping a AMDP could fill the gap.
However, I can’t get it to activate.
I do have two issues at the moment and one additional question.
I kindly request some assistance from the experts. Somehow my problems are not covered in all the examples and guides I found in the web.
Below are our System details and the ABAP class with the code.
Regards
Markus
Our System is an ERP EHP8 on HANA:
HDB: 2.00.059.04. ….
SAP_BASIS, SAP_ABA: 750 SP22
SAP_APPL: 618 SP17
CLASS /cust/cl_fi_accc_amdp_db_serv DEFINITION
PUBLIC FINAL
CREATE PUBLIC.
PUBLIC SECTION.
INTERFACES if_amdp_marker_hdb.
INTERFACES /cust/if_definitions.
CLASS-METHODS get_acc_provision_data
IMPORTING VALUE(id_year) TYPE gjahr
VALUE(id_period) TYPE rpmax
VALUE(id_rn_where_cond) TYPE string
EXPORTING VALUE(et_selected_data) TYPE /cust/if_definitions~mtyt_test_struc_tab.
CLASS-METHODS get_where_from_ranges
IMPORTING irt_rn_bukrs TYPE REF TO trgr_bukrs
irt_rn_kostl TYPE REF TO fagl_range_t_kostl
irt_rn_aufnr TYPE REF TO range_t_aufnr
irt_rn_racct TYPE REF TO trgr_gl_account
RETURNING VALUE(rd_where_cond) TYPE string.
ENDCLASS.
CLASS /CUST/cl_fi_accc_amdp_db_serv IMPLEMENTATION.
METHOD get_acc_provision_data BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.
tmp_itab = select *
from /CUST/MASTERDATA1 as MD1
full outer join /CUST/FI_FAGLFLEXT_RSTSP( p_requested_period : id_period,
p_requested_year : id_year ) as FGL
on MD1.company_code = FGL.rbukrs
and MD1.AUFNR = FGL.ZZAufNr
and MD1.KOSTL = FGL.rcntr
and MD1.v_prctr = FGL.prctr
where :id_rn_where_cond;
et_selected_data = APPLY_FILTER (:tmp_itab, :id_rn_where_cond);
ENDMETHOD.
METHOD get_where_from_ranges.
DATA: lt_named_selltabs TYPE if_shdb_def=>tt_named_dref,
lrs_named_selltab TYPE REF TO if_shdb_def=>ts_named_dref.
IF irt_rn_bukrs->* IS NOT INITIAL.
APPEND INITIAL LINE TO lt_named_selltabs REFERENCE INTO lrs_named_selltab.
lrs_named_selltab->name = 'COMPANY_CODE'.
lrs_named_selltab->dref = irt_rn_bukrs.
ENDIF.
IF irt_rn_kostl->* IS NOT INITIAL.
APPEND INITIAL LINE TO lt_named_selltabs REFERENCE INTO lrs_named_selltab.
lrs_named_selltab->name = 'KOSTL'.
lrs_named_selltab->dref = irt_rn_kostl.
ENDIF.
IF irt_rn_aufnr->* IS NOT INITIAL.
APPEND INITIAL LINE TO lt_named_selltabs REFERENCE INTO lrs_named_selltab.
lrs_named_selltab->name = 'AUFNR'.
lrs_named_selltab->dref = irt_rn_aufnr.
ENDIF.
IF irt_rn_racct->* IS NOT INITIAL.
APPEND INITIAL LINE TO lt_named_selltabs REFERENCE INTO lrs_named_selltab.
lrs_named_selltab->name = 'RACCT'.
lrs_named_selltab->dref = irt_rn_racct.
ENDIF.
TRY.
rd_where_cond = cl_shdb_seltab=>combine_seltabs(
it_named_seltabs = lt_named_selltabs ).
CATCH cx_shdb_exception.
CLEAR rd_where_cond.
ENDTRY.
ENDMETHOD.
ENDCLASS.
Dear @Sandra_Rossi
I finally have a working version.
There are some remaining minor issues regarding the client Column, and the client depend handling, but there are existing solutions and samples.
Regarding the parameterized CDS View, and the Customer namespace.
I need to use the SQL name of the CDS View in the Using parameter of the AMDP Method otherwise he could not find the View in the Hana DB.
I only need to pass the parameters in the correct order as defined in the view. I don’t need to provide the names of the parameters.
So the final method body looks like:
METHOD get_acc_provision_data BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY USING /cust/fi_acc_md1 /cust/fi_fgl_rst.
-- CDS View: /CUST/FI_FAGLFLEXT_RSTSP and his SQL_View_Name = /CUST/FI_FGL_RST
tmp_itab = select *
from "/CUST/FI_ACC_MD1" as MD1
full outer join "/CUST/FI_FGL_RST"( :id_period, :id_year ) as FGL
on MD1.company_code = FGL.rbukrs
and MD1.AUFNR = FGL.ZZAufNr
and MD1.KOSTL = FGL.rcntr
and MD1.v_prctr = FGL.prctr ;
et_selected_data = APPLY_FILTER (:tmp_itab, :id_rn_where_cond);
ENDMETHOD.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
8 | |
6 | |
5 | |
5 | |
4 | |
3 | |
2 | |
2 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.