Create an alias/synonym for STRING_AGG aggregate function in HANA

[bdeboer]

I am facing an issue with the aggregate function "STRING_AGG" in CDS, which is not supported in both HANA and SQLite. As a solution I'm currently maintaining two sets of CDS files to handle STRING_AGG / GROUP_CONCAT depending on the database. But this is starting to get diffucult to maintain. I'm hoping there is a better way.

I've tried to creating a custom GROUP_CONCAT.hdbfunction which simply calls STRING_AGG. But it seems these functions can't work as aggregate functions in HANA.

I also tried to create a synonym for STRING_AGG. I'm not sure if it's possible. I cannot seem to find STRING_AGG in the system views.

My next plan would be to create a script that automatically generates the set for HANA. Performing a simple replace GROUP_CONCAT -> STRING_AGG. But this doesn't feel optimal either.

Is there a better solution for handling the "STRING_AGG" aggregate function in CDS for HANA and SQLite without maintaining separate sets of CDS files?

[junwu]

why you think creating alias can help?

cds is data modeling, it is using kind of sql syntax, don't get the wrong impression, it is not the place to do a lot sql coding.

[bdeboer]

If CAP/HANA would somehow support custom aggregate functions we could simply create the GROUP_CONCAT aggregate in HANA. We wouldn't need to maintain database specific sets of cds files for this function.

[junwu]

if I am not wrong, those function are db object, not supported in cds.

like I said, cds is not the place to do sql coding, it is a wrong place. don't get fooled by the sql syntax thing in cds.

[junwu]

cds is just modelling the table relationship, ER diagram thing.

[bdeboer]

The ability to create custom queries is a feature supported by CDS. It's a nice feature that is enabling us to implement many of the requirements we have for our product. We don't want to do SQL coding outside of CDS since we want to leverage the security and ODATA features provided by CAP.