cancel
Showing results for 
Search instead for 
Did you mean: 

Error in custom logic of CAP: " invalid column name: COUNT"

kammaje_cis
Active Contributor
0 Kudos
1,366

I am creating an OData service using CAP.

Target is to get the below URL working.

<CF Trail>/analytics-output/ApplicationUsage?select=application,count&$orderby=count

Here is the service model.

    @readonly
    entity ApplicationUsage {
        key application : String(80);
            count       : Decimal;
            timestamp   : DateTime null;
    }

Here is the implementation in custom logic (js file)

    srv.on('READ', 'ApplicationUsage', (req) => {
        selectQuery = 'select ' +
            'key app.application as application, ' +
            'COUNT(app.application) as count : Decimal ' +
            'from cis.fiorianalytics.Actions as action ' +
            'inner join cis.fiorianalytics.Apps as app on action.applicationID = app.id '+
            'group by app.application';

        let query = cds.parse.cql(selectQuery);
        //Append the timestamp WHERE and ORDERBY condition
        query.SELECT.orderBy = req.query.SELECT.orderBy;
        query.SELECT.where = req.query.SELECT.where;
        return cds.run(query);
    });

This works great in local deployment and I get the data. But it fails to work in HANA (after deployment to trial CF).

I end up getting not so useful error as below in the browser.

<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
<code>500</code>
<message>Internal Server Error</message>
</error>

When I look into the 'Log' on SCP, I see below line which seems to be related.

Any help is greatly appreciated.

vansyckel
Advisor
Advisor
0 Kudos

Hi Krishna,

What does the parsed query look like?

Best,
Sebastian

kammaje_cis
Active Contributor
0 Kudos

Both at the local and on the CF it looks the same and looks good as well.

{
  "SELECT": {
    "from": {
      "join": "inner",
      "args": [
        {
          "ref": [
            "cis.fiorianalytics.Actions"
          ],
          "as": "action"
        },
        {
          "ref": [
            "cis.fiorianalytics.Apps"
          ],
          "as": "app"
        }
      ],
      "on": [
        {
          "ref": [
            "action",
            "applicationID"
          ]
        },
        "=",
        {
          "ref": [
            "app",
            "id"
          ]
        }
      ]
    },
    "columns": [
      {
        "key": true,
        "ref": [
          "app",
          "application"
        ]
      },
      {
        "key": true,
        "ref": [
          "action",
          "userID"
        ]
      },
      {
        "func": "COUNT",
        "args": [
          {
            "ref": [
              "action",
              "userID"
            ]
          }
        ],
        "as": "count",
        "cast": {
          "type": "Decimal"
        }
      },
      {
        "val": 1,
        "as": "userCount",
        "cast": {
          "type": "Integer"
        }
      }
    ],
    "groupBy": [
      {
        "ref": [
          "app",
          "application"
        ]
      },
      {
        "ref": [
          "action",
          "userID"
        ]
      }
    ]
  }
}

Accepted Solutions (0)

Answers (1)

Answers (1)

vansyckel
Advisor
Advisor
0 Kudos

Hi Krishna,

The order by clause in "req.query.SELECT.orderBy" doesn't fit to your SQL because the model is unaware of the join you are using (the model is the basis of the OData query processing). You either need to adjust the model or build the clauses manually instead of taking from "req.query.SELECT".

Best,
Sebastian

kammaje_cis
Active Contributor
0 Kudos

Thanks, Sebastian. I did not completely understand. Can you pleae point where exactly in the parsed query you find a problem?

vansyckel
Advisor
Advisor
0 Kudos

Hi Krishna,

You have defined entity ApplicationUsage with property count. If you now do "$orderby=count" in your request, this will translate to "order by ApplicationUsage.count asc" in "req.query.SELECT.orderBy", i.e., an order by on a column. However, in your SQL, you join two tables, while creating aliases CAP doesn't know, and count is actually an aggregation, which CAP also can't know. Hence, the content of "req.query.SELECT.orderBy" will not fit to your SQL.

By the way, SQLite simply ignores errors like this, which is why you don't experience them locally.

Best,
Sebastian