ABAP CDS Aggregation with Case Statement and Having Condition

[kuanlung_wong3]

Hi,

I'm exploring the ABAP CDS view, and trying out the following to calculate the amount of stock issued, but not yet goods receipt for every PO item and report by material, plant and batch.

Since I'm checking at EKBE, 90% of the aggregated result would have a sum of zero value, and I'm not interested in those records. Hence, I would like to filter them away using the HAVING statement as shown below. However, it's giving a syntax error.

Appreciate if someone could provide some guidance. Thanks.

define view YCDSV_SIT as
select 
  from wb2_v_ekko_ekpo2 as a
  join ekbe             as b
    on a.ebeln_i = b.ebeln
   and a.ebelp_i = b.ebelp
{
  a.matnr_i as matnr
, a.werks_i as werks
, a.ebeln_i
, a.ebelp_i
, b.xblnr
, b.charg
, sum( 
       case
         when shkzg = 'S' then b.menge * -1
         else b.menge
       end
     ) as TransitStock
}
 where   a.bsart   = 'ZSTO'
   and   a.loekz_i = ' '
   and   a.elikz_i = ' '
   and   b.zekkn   = '00'
   and ( b.vgabe   = '1'
    or   b.vgabe   = '6' )
 group by matnr_i, werks_i, ebeln_i, ebelp_i, xblnr, charg
having TransitStock > 0
;

[DoanManhQuynh]

i dont think you cant use the aggregation inside it own sql like that. Might be you could try the annotation Consumption.filter:

https://help.sap.com/doc/saphelp_nw75/7.5.5/en-US/d6/0c0bf6798a481fb7412bc89934cb8a/content.htm?no_c...

[maheshpalavalli]

You gave all the information except the syntax error screenshot which is the actual information that is required.

[Sandra_Rossi]

In standard SQL and in CDS views, HAVING may not use the alias but must repeat the corresponding expression, cf example in HAVING.

In your case:

...
HAVING sum( 
       case
         when shkzg = 'S' then b.menge * -1
         else b.menge
       end ) > 0

[kuanlung_wong3]

Hi,

In the original CDS definition, the syntax error is as follow:

If I try with Sandra's suggestion, it gives another syntax error that goes like this:

The main problem with this issue is the usage of alias with "having" condition. I found a sample provided by SAP which looks like this:

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
    inner join 
      snwd_bpa on buyer_guid = snwd_bpa.node_key 
  {  key bp_role as role, //e.g. customer or supplier 
     count(distinct buyer_guid) as partners_count, 
     sum(snwd_so.gross_amount) as sum_gross_amount } 
  where snwd_so.currency_code = 'EUR' 
  group by bp_role 
  having sum(snwd_so.gross_amount) > 100000.00;

If I make a small change to this example by replacing the "sum(snwd_so.gross_amount)" with alias "sum_gross_amount", it would report the same syntax error "Column sum_gross_amount is not known".

@AbapCatalog.sqlViewName: 'SALES_ORDER_VW' 
define view sales_order as 
  select from snwd_so 
    inner join 
      snwd_bpa on buyer_guid = snwd_bpa.node_key 
  {  key bp_role as role, //e.g. customer or supplier 
     count(distinct buyer_guid) as partners_count, 
     sum(snwd_so.gross_amount) as sum_gross_amount } 
  where snwd_so.currency_code = 'EUR' 
  group by bp_role 
  having sum_gross_amount > 100000.00;

Appreciate if anyone could advise. Thanks.

[former_member280812]

Hi,

I suggest to put your main query first into a temporary table before using the having clause in your condition.

[kuanlung_wong3]

Hi,

You're right. It would work if we work with a temporary table. But I find it weird that the CDS syntax is not supporting the use of alias when it comes to "having" condition in one single selection, while this is a valid syntax in MySQL. I doubt that SAP would miss out of this usage, more likely that I haven't figured it out.

You could try out the MySQL statement here:

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_case

with the statement:

SELECT SupplierID, 
       SUM( CASE WHEN CategoryID= "1" 
       		  THEN price 
              ELSE price * -1 
            END) AS Price2
  FROM Products
 GROUP BY SupplierID
HAVING Price2 > 1;

[former_member280812]

Hi,

Ya It would be better if they consider using having clause with aliases, maybe there is a reason why it does not support that syntax. But for now I am using temporary table as a workaround for that.