Using Window Expressions in BW ABAP Routines

[former_member832460]

Hello All,

I am looking to use window expressions in ABAP Routines in BW environment but getting following syntax error:

"OVER" is invalid here (due to grammar). contains an invalid character or it is a

As per this blog: https://blogs.sap.com/2021/11/24/window-expressions-in-abap-sql/ , we should be able to use window expressions in ABAP SQL (i.e. Open SQL in ABAP). However, I get the above stated error message when I attempt to use RANK( ) or others such window expressions such asSUM( ), COUNT( ), AVG( ), etc. SQL I am using within my test ABAP routine looks like:

     TYPES: BEGIN OF ls_windowfunctst,
		CITY TYPE c length 50, 
        RANK TYPE i,
        SALES TYPE p length 000009 decimals 000002,
      END OF ls_windowfunctst.
      DATA: lt_windowfunctst TYPE STANDARD TABLE OF ls_windowfunctst,
            wa_windowfunctst TYPE ls_windowfunctst.
      SELECT 
            CITY, 
            RANK( ) OVER( PARTITION BY CITY ORDER BY CITY ) as SALES_RANK,
            SALES
       FROM ZFMSALES
       INTO CORRESPONDING FIELDS OF TABLE @lt_windowfunctst.

We are running 7.5 SP8 for ABAP/BASIS ON HDB 2.0 on BW side.

If anyone has used window expressions with ABAP/Open SQL successfully, could you please advise what I might be doing wrong here? I suspect its not supported, but have not been to confirm this (hoping I am wrong since these expressions are pretty useful!).

TIA.

[Dominik_Graus · Accepted solution]

Hello Vince,

I'm afraid that windows expressions in ABAP SQL are not available in your release.

In SAP BW/4HANA 2021, your example above is running fine.

Note that there is a mismatch in the type declaration. You use RANK in the type declaration but SALES_RANK in the field list.

Regards,
Dominik

[retired_member]

Hello,

Window expressions were introduced to ABAP SQL with release 7.54.

https://help.sap.com/doc/abapdocu_latest_index_htm/latest/en-US/index.htm?file=abennews-754-abap_sql...

From that release on you do not need AMDP or Native SQL to use a window expression in ABAP any more.

Window expressions are not supported by ABAP CDS. In order to use them before 7.54, you can use AMDP.

Kind regards

Horst

[mahlzeit194837]

That's great news! Is it on the roadmap for CDS as well?

[retired_member]

Nope, Window expressions are not on the road map for ABAP CDS.

[MKreitlein]

Hello Vince,

the blog, which you linked looks like that the examples have been written in a HANA Calculation View.

I assume what you try will not work in pure ABAP (SE38 report).

You will have to use either an ABAP CDS View or a Calculation view, or in case of BW you can use an AMDP script.

Good luck 🙂

Martin

[Dominik_Graus]

Hi Martin,

It works in ABAP but not in SAP NetWeaver 7.50.

Regards,
Dominik

[MKreitlein]

Hello Dominik,

If BW 7.50 is on HANA why not, what is the difference?

Thanks,

Martin

[Dominik_Graus]

Hi Martin,

This feature is a new ABAP feature. It was shipped with release 7.54 as mentioned by Horst Keller in another reply. That's why the feature is not available in BW on HANA 7.50.

Regards,
Dominik

[former_member832460]

Thank you all for your responses. And thank you Safa for updating the blog.

We plan to migrate to BW 2021 sometime next year, so we look forward to using these tools in our ABAP tool kit.

[SafaBahoosh]

Hello Vince.

As it is already mentioned by Horst, window expressions are introduced with release 7.54 and so the example unfortunately doesn't work for you. I already update the blog though with this information, thank you for brings it up and thanks others for their comments 🙂

Regards

Safa