How to use SQL function such as Count/Min/Max in Cloud Application Programming

[sapdeveloper_]

Hello Everyone,

Kindly let me how I can use SQL functions in CQL. How i can frame the CQL for the below -mentioned select query i

Sample Select Query with groupBy Clause and SQL Function

Select status,Count(status) as Count from foobar where createdon>= valid_from and createdon <= valid_to grouby(status)

I am referring the below-mentioned URL

https://cap.cloud.sap/docs/cds/cql

Thanks

Sandeep

[OlenaT]

Hi Sandeep,

The CQL for your select query should look as follows:

'SELECT from foobar {status, count(status) as Count} where createdon>= valid_from and createdon <= valid_to group by status'

Best regards,

Olena

[sapdeveloper_]

Hello Olena,

Thanks for your reply. I tried got below mentioned error

[ERROR] SQLITE_ERROR: near "from": syntax error

Thanks

Sandeep

[sapdeveloper_]

Hello Olena,

If I follow below-mentioned notation how I can use count functionality of SQL

let query = SELECT.from(foobar, ['status']). where({ status: 'Active' }).groupBy('status');

let results = cds.run(query)

Thanks in advance.

Regards,

Sandeep

P.S I tried using count function too but I got the message that count is not a function.

[acmebcn]

Hi Sandeep and Olena, I also tried to use Olena's approach right in an entity and it didn't work either. I wrote something like the following:

service KpiService {
    entity kpibyStatus as select from foobar {status, count(status) as count} group by status;            
}<br>

But it got the error:

[ERROR] srv\kpi-service.cds:6:71-93: Element "KpiService.kpiByStatus.count" does not have a type: Elements of ODATA entities must have a type
[ERROR] srv\kpi-service.cds:6:5-111: Entity "KpiService.kpiByStatus" does not have a key: ODATA entities must have a key<br>

Any suggestion?

Thanks in advance,

David R.

PS: Correction,Olena, not Elena 😞

[OlenaT]

Hi Sandeep,

It's strange that you are getting an SQLite error for the mentioned above CQL. It's translated to the following SQL

SELECT status, count ( status ) AS "Count" FROM foobar WHERE createdon >= valid_from AND createdon <= valid_to GROUP BY status 

which looks correct.

Regarding your second question, there is no API for count yet, we are planning to support it, but I cannot comment on timeline.

Could you please share your example in details (your .cds model, what are you trying to achieve), so I can reproduce it?

Best regards,

Olena

[OlenaT]

Hi David,

Thank you for the example, I replied to your question here.

Best regards,

Olena

[sapdeveloper_]

Hello Olena,

Thanks for providing the example. I am able to do with following lines of code

query = SELECT.from(FooBar, ['Count(ID) as count'])..groupBy('status');

results = await cds.run(query)

With this I am able to get count of various status in the results

Really thanks for your time and assistance.

Regards,

Sandeep