AGGREGATION ON CDS VIEW and Filtering data with where clause issue

[Imtiyaz]

Hi,

We have following case where in we have internal table data say ITAB (fields: matnr, werks) from previous CDS and lgort (Storage loc.) on selection screen

Now, Need to get SUM of stock (MARD-LABST) at matnr level.

meaning fetch from MARD based on ITAB matnr, werks, selection screen lgort and then SUM LAST as well.

tried by creating 2 CDS view as below it doesn't filter based on 'For all entries' where condition ( even hardcoded values doesn't work). it sums LABST for all werks and lgort. expectation is it should SUM only where clause matnr, werks and selection screen lgort.

___________________________________

matnr, werks,lgort,labst->CDS1

matnr, SUM(labst) group by matnr-->CDS2 ( select from CDS1..meaning calling CDS1 in CDS2)

Program:

Select matnr, stock
from CDS2
Inner join CDS1 on CDS2.matnr = CDS1.matnr
For all entries of ITAB where CDS1.matnr = itab-matnr
and CDS1.werks = itab-werks
and CDS1.lgort in select options of selection screen

_______________________________________

what I understood is while calling CDS2 aggregation already happened before where clause consideration which is in program and using werks and lgort in where clause doesn't work or filter...but it filers by matnr as matnr is in both CDS views.

Any suggestion here?

[Sandra_Rossi]

Remove the "for all entry" and where clauses in your ABAP query, add UP TO 1 ROWS, and try again to make sure that your CDS views are fine. If you get only one line, then the culprit is the "where" clause (possibly the classic issue with missing leading zeroes i.e. the ALPHA conversion routine).

[Imtiyaz]

Thanks for reply.

Tried already.

If CDS views run independently the result is as follows.

CDS1 has 4 entries having different werks and lgort. It is as expected.

CDS2 make it one entry by summing  LABST for CDS1 4 entries. It looks fine.

 program also gives one entry which is fine but it is same as CDS2 ( which has SUM of all weeks, lgort).. is not expected because in where clause I passed hard-coded value to CDS1 fields same as it is in database. I don't think conversion issue here. It should have first filtered based on where clause of CDS1 and then SUM through CDS2.

[Sandra_Rossi]

The problem with the "DDIC conversion routines" is that the database contents may appear different from the real database contents. I'm not sure you know what the DDIC conversion routines, because you didn't talk about MATNR which is the most "dangerous" candidate.

[Imtiyaz]

Yeah. I understand conversion. 

Matnr is on both CDS and already with leading zero.

Coming to WERKS it is alphanumeric say E001 and LGORT say 1200. Both are as it is in DB. 

[OlegBash]

Could try to select from CDS1 and inner join CDS2 ? in you sample: you have CDS2 from CDS1.
if it is possible could please provide snippet as in your system?

[Imtiyaz]

tried both ways, select query fetch from CDS1 and join CDS2 and vice versa.

as below:

Define view ZCDS1
as select distinct from mard
{
key matnr,
key werks,
key lgort,
labst
}

Define view ZCDS2

as select from ZCDS1

{
key matnr,
sum(labst) as stock
} group by matnr

Program:
From previous fetch, we have combination of matnr,werks stored in ITAB. 

s_lgort selection screen.

select ZCDS1~matnr
          ZCDS2~stock
from ZCDS1
inner join ZCDS2 on ZCDS2~matnr = ZCDS1~matnr
for all entries in @ITAB where ZCDS1~matnr = @ITAB-matnr
                                         and ZCDS1~werks = @ITAB-WERKS
                                         and ZCDS1~lgort in @s_lgort
into table @DATA(lt_stock).

[OlegBash]

in the CDS with aggregation you should put LGORT. for now the system calculates sum by matnr and nothing more. despite the CDS1 is used in JOIN and in CDS2 it DOES NOT mean that the instances are the same. they are calculating independently.

So, the CDS2 should be like below. For clearness and openess - I have created ZMARD like MARD and fill it. you could check here. Maybe i am missing something - please inform.

define view ZMARD_CDS3_W as select from ZMARD_CDS1_M
{
    key matnr,
    key lgort,
    sum(labst) as stock
} group by matnr, lgort

[OlegBash]

maybe will be good if you put all fields and calculation on database side.

define view ZMARD_CDS4_W as select from ZMARD_CDS1_M
{
    key matnr,
    key werks,
    key lgort,
    sum(labst) as stock
} group by matnr, werks, lgort

and in report:

    SELECT matnr, stock
    FROM ZMARD_CDS4_W
    FOR ALL ENTRIES IN @MT_tab_matnr
         WHERE matnr = @MT_tab_matnr-matnr
           AND werks = @MT_tab_matnr-werks
           AND lgort IN @s_lgort
    INTO TABLE @DATA(lt_stock).

[Imtiyaz]

Thanks it will resolve to some extend by removing unwanted record in below example where lgort = L100 but didn't fix fully as it will do the SUM at lgort level. meaning if lgort is having 2 values on selection screen say L101 & L201 then matnr having 2 SUM entries one for L101 and other one for L201. expectation is it should SUM both lgort values and show one entry.

matnr = M1001, werks = W001, lgort = L100, lbst = 10,  

matnr = M1001, werks = W001, lgort = L101, lbst = 10,

matnr = M1001, werks = W001, lgort = L101, lbst = 20,

matnr = M1001, werks = W001, lgort = L201, lbst = 10,

Result is

matnr = M1001,  lbst = 30,

matnr = M1001,  lbst = 10,

expected

matnr = M1001,  lbst = 40,

I somehow tried to make ITAB also CDS and joined in CDS1 and added lgort in CDS2 to make it available for filter (as per your reply) 

Define view ZCDS1
as select distinct from mard

inner join ITAB_CDS on ITAB_CDS.matnr = mard.matnr and ITAB_CDS.werks = mard.werks
{
key matnr,
key werks,
key lgort,
labst
}

Define view ZCDS2

as select from ZCDS1

{
key matnr,

 lgort,
sum(labst) as stock
} group by matnr

In report:

select ZCDS2~matnr
          ZCDS2~stock
from ZCDS2
inner join ZCDS1 on ZCDS1~matnr = ZCDS2~matnr
 where ZCDS2~lgort in @s_lgort
into table @DATA(lt_stock).

[OlegBash]

I think you need to use "join with internal tab" (as you pointed INNER ITAB_CDS) if the system version allowed (directly with MARD, not via CDS). It should resolve the restriction with FAE (full code is here).

And it seems ( maybe I am wrong ) that we could not resolve only by cds. we should either: do extra calculation on application server  or do join itab (as new feature).

        TYPES: BEGIN OF ts_matnr_werks
            , matnr TYPE matnr18
            , werks TYPE werks_d
          , END OF ts_matnr_werks
          , tt_matnr_werks_srt TYPE sorted TABLE OF ts_matnr_werks WITH UNIQUE key matnr werks
          .

     data lt_tab_matnr_srt TYPE tt_matnr_werks_srt.
     FIELD-SYMBOLS <fs_tab_matnr> TYPE ts_matnr_werks.

    " just to prepare sorted table
    loop at mt_tab_matnr ASSIGNING <fs_tab_matnr>.
      INSERT <fs_tab_matnr> INTO TABLE lt_tab_matnr_srt.
    ENDLOOP.

    select z1~matnr, sum( labst ) as stock
        FROM zmard as z1
          join @LT_tab_matnr_srt as z2
        on z1~matnr eq z2~matnr
        and z1~werks eq z2~werks
      WHERE z1~lgort in @s_lgort
      GROUP BY z1~matnr
      INTO TABLE @DATA(lt_stock)
      .

[Imtiyaz]

with the current version "join with internal tab" is not allowed. syntax error for me.

yeah..at application layer level can be achieved. it is not just one query with stock,will have similar queries for other aggregations like open orders quantity, forecast etc with similar logic. so, trying to achieve by CDS at DB level itself as data would be huge.

[OlegBash]

"join with internal table" - is nothing more but GTT + insert. And you can reach it in custom way if the system version is no allowed.

Firstly, create table for temporary purposes (it could be GTT, but you can create transparent table). In my sample I prepared transparent table (in case system version is not allowing). This database transparent table will be inserted from matnr_werks_itab. (see below).

The structure as on the screentshot. the code is below and here (with all package).

    " via GTT_like technique (if no such object)
    DATA lt_db_table4join TYPE SORTED TABLE OF zmatnr_werks_gtt WITH UNIQUE KEY matnr werks.
    DATA ls_line_like_fae TYPE zmatnr_werks_gtt.
    FIELD-SYMBOLS <fs_tab_matnr> TYPE ts_matnr_werks.

    LOOP AT mt_tab_matnr ASSIGNING <fs_tab_matnr>.
      ls_line_like_fae-matnr = <fs_tab_matnr>-matnr.
      ls_line_like_fae-werks = <fs_tab_matnr>-werks.
      INSERT ls_line_like_fae INTO TABLE lt_db_table4join.
    ENDLOOP.

    " clear defore join - or add guid
    DELETE FROM zmatnr_werks_gtt.
    INSERT zmatnr_werks_gtt FROM TABLE lt_db_table4join.
    " no commit in one session


    SELECT z1~matnr, SUM( labst ) AS stock
    FROM zmard AS z1
      JOIN zmatnr_werks_gtt AS z2
    ON z1~matnr EQ z2~matnr
    AND z1~werks EQ z2~werks
  WHERE z1~lgort IN @s_lgort
  GROUP BY z1~matnr
  INTO TABLE @DATA(lt_stock)
  .

    " clear after using
    DELETE FROM zmatnr_werks_gtt.

[Imtiyaz]

Yeah. Looks like need to handle this through SQL statement in ABAP or will try association. Thanks for the try.

[Sandra_Rossi]

Okay, I had the same kind of weird issues with just a simple SELECT LABST FROM CDS1 which gave a wrong result (without even joining to CDS2). ABAP 7.58.

Probably that using replaced objects like MARD (it has a replacement object named NSDM_E_MARD) may lead to many possible errors. Use the replacement object and I guess it will solve.

I don't know any way to check inconsistencies.

Replacement Objects - ABAP Keyword Documentation

• When you define a replacement object for a database table for a classic database view, the system checks whether the specified CDS view fulfills the prerequisites. If a CDS view used as a replacement object is changed later on in such a way that the prerequisites are no longer fulfilled, a runtime error DBSQL_REDIRECT_INCONSISTENCY occurs when an Open SQL read is performed on the database table or on the database view.

• You should be extremely careful when specifying a proxy object for a database table or a database view. Incorrect usage can cause inconsistencies.

LABST is no more stored in MARD, it can be accessed correctly via NSDM_E_MARD: 3450094 - CDC is not working if the calculated field is changed - SAP for Me.

When reading MARD, the replacement object should be automatically used, but it seems it's not... (maybe some bugs here)

[Imtiyaz]

Good information will look into detail for replacement object.

However, see below case where open orders SUM aggregation also doesn't give proper result.

Define view ZCDS1
as select distinct from ekko
inner join ekpo on ekpo.ebeln = ekpo.ebeln
inner join eket on eket.ebeln = ekpo.ebeln and eket.ebelp = ekpo.ebelp
{
key ekko.bsart,
key ekpo.ebeln,
key ekpo.ebelp,
       ekpo.matnr,
       ekpow.werks,
       (eket.menge - eket.wege) as open_qntity
}
WHERE ekpo.loekz = ' '
     and ekpo.elikz = ' '

Define view ZCDS2
as select from ZCDS1
{
key matnr,
sum(open_qntity) as open_qntity_sum
} group by matnr

Program:
From previous fetch, we have combination of matnr,werks stored in ITAB.
document type s_bsart select option on selection screen

select ZCDS1~matnr
           ZCDS2~open_qntity_sum
from ZCDS1
inner join ZCDS2 on ZCDS2~matnr = ZCDS1~matnr
for all entries in @ITAB where ZCDS1~bsart in @s_bsart
                                         and ZCDS1~matnr = @ITAB-matnr
                                         and ZCDS1~werks = @ITAB-WERKS
into table @DATA(lt_open_orders_qty).

Note: changing ZCDS1 and ZCDS2 position in select query doesn't fix issue.

Analysis:
Independently run CDS2 it gives one record against particular matnr and sum value is 144
Independently run CDS1 and manually filter for same matnr and saw it has 6 records having different doc. type (BSART) and different werks..SUM value is 144

both results look ok because filter is not performed against werks and bsart. which will be in program.

now, query in program executed as below and result against same matnr is 144. which is not expected as doc. type and werks filter is passed in where clause of CDS1.
same issue here also (same as stock one). this issue is at multiple places, if one solved all aggregated SUM queries issues will be solved.
this approach is ok or some other approached need to follow here?

[Sandra_Rossi]

I guess that it's a bug in this situation: one CDS view containing an aggregate function, and ABAP querying this CDS view with FOR ALL ENTRIES. In classic ABAP, you can't use aggregate functions with FOR ALL ENTRIES. I'm surprised to not find anything in the SAP notes yet. I would expect an ABAP syntax error.

[Imtiyaz]

SAP_BASIS Release 750

SAP_ABA Release 750

so, it is ABAP 7.5 release..right? does that make some difference.

hhmm. If we use, SELECT statement directly do SUM and FOR ALL ENTRIES (FAE) and GROUP BY then it gives error. For time being FAE is removed and some hardcoded values passed in WHERE clause even then it won't filter.

If we call alone CDS1 one in program it gives result with filtered value then how to do aggregation? 

Just thinking what is the other way to achieve this aggregation against matnr as single entry in result. 

[Sandra_Rossi]

It is ABAP 7.50, not 7.5, and it's the same "bug" in ABAP 7.58. I guess it's worth creating a blog post to attract CDS/Open SQL experts and get their opinion.

If there's a bug even without FOR ALL ENTRIES, then maybe the cause is the join between two CDS views 1 and 2, where the CDS view 2 is based on the CDS view 1. What happens if you make the CDS view 2 select directly from NSDM_N_MARD instead of ZCDS1?

[Imtiyaz]

NSDM_N_MARD doesn't exist in my system. our system is still ECC but database is HANA.

[Imtiyaz]

one CDS with all fields and second CDS that has only fields required for aggregation ( say matnr). calling those CDS views in program as join and filtering not working. what would be the other way to achieve this aggregation?

Note: AMDP and Table function usage needs to be avoided due to some reasons.