AMDP – Error with Customer Namespaces and joined CDS View with Parameters

[markus_kraetzschmar]

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.

1.  Is the customer namespace with the /xxx/. The SQL Script does not like the “/”.
   1.  I put the table names into “” but I’m not sure if it works, since …
2. The joined CDS View need parameters to be passed, and I could not figure out how to pass them.
3. The Question: As found on the net, I can’t pass Range tables. So I build an SQL String out of them.
   1. So far, I found only that I can use it afterwards as a filter.
   2. Question: Can I pass the SQL Sting in the Where Condition?

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.

[Sandra_Rossi]

1) Use double quotes around the names (see https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/209f5020751910148fd8fe88a...). The question you are asking has been asked in the forum, as you can see if you search

hana AMDP select-options parameter site:sap.com

[Sandra_Rossi]

I'm surprised that your SQLScript could work in HANA with slash names without double quotes around. In your last comment, you mentioned FULL OUTER JOIN "CUST/FI_FGL_RST" without the first slash in the HANA function name. I'm not expert, so let's wait for the experts. After USING, you may indicate only objects which are used in the AMDP method and which are ABAP objects. I don't know which ones are ABAP, which ones are not ABAP.

[markus_kraetzschmar]

Dear Sandra, well I never said (Hopefully) that the /xxx/ works on hana db. Well, yes I worked in the past a short time directly on a HANA DB, but this seems no longer the common way to do things. I know that on Hana DB there are no "/" in the names. However, the table names given by the Application server including CDS views and all the new stuff are created in the customer namespaces with the "/". Using an AMDB I only know the names with the "/" and not the may be exisiting corresponding names at the DB, since I can't take a direkt look.

[markus_kraetzschmar · Accepted solution]

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.

[markus_kraetzschmar]

@Sandra_Rossi :

Dear Sandra. 
It seems that the quotes worked at least for the table in combination with the Using Parameter.

Activation is still not possible, since I can't use the CDS View as I would like to use it. Neither works the SQL name nor the real name.
Providing the needed parameters for the view seems to be troublesome as well.

Here the latest version on the method body:

  METHOD get_acc_provision_data BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
         OPTIONS READ-ONLY USING /cust/fi_acc_md1 /cust/fi_fgl_rst.

    -- /CUST/FI_FAGLFLEXT_RSTSP SQL_View_Name = /CUST/FI_FGL_RST

        tmp_itab = select *
                     from "/CUST/MASTERDATA1" as MD1
                     full outer join "CUST/FI_FGL_RST"( 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 rbukrs = '1000' ;
                      --:id_rn_where_cond;

         et_selected_data = APPLY_FILTER (:tmp_itab, :id_rn_where_cond);

  ENDMETHOD.

[Sandra_Rossi]

Please do not post a solution. Instead, click on "show replies" then "comment".

[markus_kraetzschmar]

Hello Sanda. If possible you might ask for a redesign of this Community page. Directly below your comment is the "Answer"-Button, so naturally I used that one. It seems formating options like code snipplets within a comment is not possible, so it's less comfortable to post an progress-update. On the other hand the reading flow ist better.

[markus_kraetzschmar]

Hello Sanda. If possible you might ask for a redesign of this Community page. Directly below your comment is the "Answer"-Button, so naturally I used that one. It seems formating options like code snipplets within a comment is not possible, so it's less comfortable to post an progress-update. On the other hand the reading flow ist better. 🙂