Select statement in AMDP returning no values

[former_member202733]

Hi guys how are you doing?

I'm just starting in this sap hana world, so maybe it's should be a silly question.

Well, I'm faccing a problem with an AMDP.

I wrote an stored procedure that returns 2 data sets, the first select statemant works fine but the second one, that is a join between MSEG table and the result of the first select statment returns no data.

    e_result_mseg = (
        select ms.mandt, ms.mblnr, ms.bwart, ms.grund, ms.matnr, ms.erfmg,
               ms.erfme, ms.aufnr
        from mseg as ms left outer join :e_result as r on ms.mandt = r.mandt and
                                                          ms.mblnr = r.wablnr and
                                                          ms.mjahr = r.myear
    );

I am also tried this approach and just like the previous statment, it's fails.

    e_result_mseg = (
        select ms.mandt, ms.mblnr, ms.bwart, ms.grund, ms.matnr, ms.erfmg,
               ms.erfme, ms.aufnr
         where ms.mandt = :p_client
               and ms.mblnr  in ( select wablnr from :e_result as r where wablnr is not null )
               and ms.mjahr  in ( select myear  from :e_result as r where myear is not null )
    );

But when I execute this statement in my ABAP program it's works fine.

    zcl_ejemplo_amdp_oee=>get_data( EXPORTING p_client = sy-mandt
                                               p_budat = budat
                                               p_lang  = sy-langu
                                    IMPORTING e_result = lti_result
                                              e_result_mseg = lti_mseg ).

     SELECT mseg~mblnr, mseg~bwart, mseg~grund,
            mseg~matnr, mseg~erfmg, mseg~erfme,
            mseg~aufnr
     FROM mseg
     FOR ALL ENTRIES IN @lti_result
        WHERE mseg~mblnr = @lti_result-wablnr AND
              mseg~mjahr = @lti_result-myear  
        INTO TABLE @DATA(lti_mseg2).

Can someone please help me to find where I am doing something wrong?

Best regards,

Ronaldo S. Vieira

[former_member202733]

Hi!
Maybe you are right. I have not enough knowledge to say the opposite. But I tried this and still not working.

    e_result_mseg = (
        select ms.mandt, ms.mblnr, ms.bwart, ms.grund, ms.matnr, ms.erfmg,
               ms.erfme, ms.aufnr
        from mseg as ms
        where ms.mandt = :p_client and
              ms.mblnr = '4900002390'
    );

Even if I perform this query filtering only the MANDT this still with no results.

So I have no idea what is going on here.

Best regards,

Ronaldo S. Vieira

[former_member202733]

Now I can say with a 100% of sure, it is possible perform joins between db tables and result set tables.

best regards

Ronaldo S. Vieira

[former_member202733 · Accepted solution]

Well, I found the problem.

And the problem is the MSEG table.

I figure it out that this table does not work in let's say, CDS world, so I just replace MSEG by DOCMAT table and everythig is working fine.

I only would like to know if there are some documentantion about this kind of table that does not works in the CDS context.

Best regards

Ronaldo S. Vieira

[DoanManhQuynh · Accepted solution]

you should look at Florian answer.

[former_member202733 · Accepted solution]

Hi, I look at the Florian answer, but I was solve the issue before Florian give me an answer, actualy I found this post here but the document that Florian give me in the answer is very helpfull.

best regards,

Ronaldo S. Vieira

[former_member825735 · Accepted solution]

Thanks for this answer

[rene_dimarco · Accepted solution]

Util tu post, gracias.

[pfefferf]

The reason why you get no result by a query on table MSEG within an AMDP is that table MSEG was "simplified" with an S/4 release. that means that in this table no data is stored anymore. When you do a query from the ABAP layer the query is redirected by the DB interface layer to the proxy CDS View NSDM_DDL_MSEG, which gets the data out of the new data model. When you do the query within an AMDP the redirection is not done, because you directly operate on DB level (w/o using the DB interface layer like it is used by Open/ABAP SQL).

Details about simplications, like that one discussed, can be found in the Simplification List.

[former_member202733]

Thank you very much!

best regards,

Ronaldo S. Vieira