Window functions in CDS for Hana. How to use?

[pepl]

Hi!

Recently in CAP window functions have been introduced. However when I try to compile such a view I have a message like this:

[ERROR] db/models/payops_tooling/timeline.cds:177:9: Window functions are not supported by SAP HANA CDS (in entity:"timeline_views.payout_balance_1"/select/column:"withdraw_partition")

Debugging the code I have found that hdbcds is causing this message. So i have added following line into cdsrc.json

"hana": {
        "deploy-format": "hdbtable"
    }

After this view compiled but like this:

Could you please review what goes wrong? Thank you!

It would be great to have it fixed. Right now we are creating hdbviews manually and declaring those views in cds with @cds.persistence.exists which is additional work.

[patricebender]

Hi,

could you please share your view definition.

If I can reproduce your issue, I am more than happy to fix it ASAP.

Best Regards,

Patrice

[steffen_weinstock]

Hi,

can you also provide the respective CDS source (at least the view definition for "payout_balance_1"; better would be a self contained minimal model that allows us to reproduce the problem)?

[pepl]

Hi! Here is the way how I define a window function:

view payout_balance_1 as
    select from payout_balance_union {
        * ,
        // every withdraw will have its own counter, we do it descending to make first lines belong to first withdraw
        sum(
            case source
                when
                    'WITHDRAW'
                then
                    1
            end
        ) over(
            partition by asset_type, asset_id order by
                created desc
        ) as withdraw_partition : Integer @title : 'withdraw_partition'
    };

However I tried simpler examples without case and the result is same unfortunately

Thank you!

[patricebender]

Thanks for providing this snippet.

I was able to reproduce the issue with the `cds build --production` command.

When using the CLI to compile your snippet like so `❯ cds compile -2 sql` everything is fine.

I will investigate what is going wrong and let you know once this issue has been resolved.

Best Regards,

Patrice

[patricebender · Accepted solution]

Hi,

Thank you for reporting this.

I have identified and fixed the bug in the cds-compiler. The fix will be shipped in the beginning of February with the new @sap/cds version.Would that be sufficient for you?

Best Regards,

Patrice

[pepl · Accepted solution]

That's fantastic, thank you so much for such a fast troubleshooting patricetao . As I have mentioned is not critical because we already use window functions using native hdbview artifacts. In any way we will need to perform refactoring and careful testing. So it's great that we are one step closer towards a coherent CDS solution.

[patricebender · Accepted solution]

Hi pepl.booking,

The fix has been shipped with sap/cds-compiler@2.12.0.

Could you please check if this resolves the issues as expected?

Thanks and Best Regards,

Patrice

[pepl · Accepted solution]

Yes, it's working! This change - is a game changer. Now the code will have much fewer artifacts. No more blind hdbview creations without any intellisense and other nice features. No need in synonyms manipulation as well.

If you manage somehow to solve currency conversion issue within CAP ( SAP-relevant issue with handling currencies like IDR,JPY with 0 decimals or like JOD, OMR, BHR with 3 decimals ) - then we can also get rid of calculation views in our model.

Thank you so much for delivering this!

[gregorw · Accepted solution]

pepl.booking have you asked about the currency conversion issue in another question here or filed an incident via SAP Support?

[pepl · Accepted solution]

no not yet. Should I?