Does CAP accept only desc and not DESC?

[GregorSchuetz]

Hello folks,

I'm currently working on an application in SAP CAP that consumes the Microsoft Graph API. In some cases I have to sort my selection from the database by using either ASC for ascending or DESC for descending.

Now the problem or bug or whatever you name it that I've encountered was the following:

I made following selection on my Database using ASC and it works just fine without any issues:

const logins = await cds.run(cds.parse.cql(
     `SELECT * FROM Login WHERE createdDateTime >= '2022-04-10T11:00:00Z' ORDER BY createdDateTime ASC`
));

But when I wanted to use DESC it would not sort it descendingly but just straight up ignore it, unless it was written in lower case:

working code:

const logins = await cds.run(cds.parse.cql(
     `SELECT * FROM Login WHERE createdDateTime >= '2022-04-10T11:00:00Z' ORDER BY createdDateTime desc`
));

// same code but without cds.parse.cql
const checkDate = '2022-04-10T11:00:00Z';
const logins = await cds.run(
      SELECT.from(Logins).where("createdDateTime >=", checkDate).orderBy("createdDateTime desc")
);

not working code:

const logins = await cds.run(cds.parse.cql(
     `SELECT * FROM Login WHERE createdDateTime >= '2022-04-10T11:00:00Z' ORDER BY createdDateTime DESC`
));

//same code but without cds.parse.cql
const checkDate = '2022-04-10T11:00:00Z';
const logins = cds.run(
      SELECT.from(Logins).where("createdDateTime >=", checkDate).orderBy("createdDateTime DESC") 
)

Note that for some reason you can use either ASC or asc but not DESC or desc. Only desc in lowercase worked for me. I know that it is recommended not to use cds.parse.cql but I tested it without and it still didn't work if DESC was written in uppercase.

I am confused why this would work for ASC but not for DESC or am I missing something?

Thank you in advance.

Regards,

Gregor 🙂

[Jörg_Brandeis · Accepted solution]

Hi Gregor,

I am not a CAP expert so I can only add my two cents from the SQL perspective:

• databases are normaly case insensitive regarding to keywords. So the corresponding framework above must somehow do some magic.
• The SQL default behaviour is sorting ascending. the specification of asc (or ASC) is optional and not required. But for descending you have to specify desc (or DESC).

So my educated guess is: The framework magic is scanning for "desc" and everything else will be ignored and results in the default sorting.

Regards,
Jörg