Hello,
I have a problem with the execution of SELECT DISTINCT queries on a HANA Cloud DB.
I am running the following CQN query.
let departmentTeams = await service.run(SELECT.distinct.from('RequirementDetails').columns('Department').where(`RequirementModelPermanentId = ${requirementModelPermanentId}`));
This works fine in BAS where we are using SQLite. After deploying on to our subaccount (where we have a HANA Cloud DB), this statement started causing 500: Internal Server Error.
The logs suggest that the query which is being run is this one instead.
SqlError: invalid column name: not included in SELECT DISTINCT column list: ALIAS_1.REQUIREMENTID ... 'SELECT DISTINCT Department AS "Department" FROM CockpitService_RequirementDetails ALIAS_1 WHERE RequirementModelPermanentId = 22543 ORDER BY RequirementId ASC LIMIT 1000 OFFSET 0'
I believe the ORDER BY RequirementId is what is causing the problem. Is this behaviour normal?
If so, how do we query the distinct rows?
Thank you for your help
Atakan
PS: If I leave out the distinct part, the query runs fine btw.