on 06-26-2020 3:50 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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 😞
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
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
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.