SQL to CDS parsing error in CAP

[#1]

Hi Experts,

I am trying to parse SQL statement with case in aggregate function SUM, it throwing error. But without case statement, its working.

Online CDS Parser: https://cap.cloud.sap/docs/04-CDS/pegjs/

Not Working Code:

select matnr,
dedc,
sum (case when Qty = '' then 0 else CAST(Qty as float) end )
from Table1
where ID = "ID1213"

Working Code:

select matnr,
dedc,
sum(Qty)
from Table1
where ID = "ID1213"

After removing CASE statement its working.

Can anyone please help.

Thanks.

Satya

[Daniel7 · Accepted solution]

I just tried your example as well (replacing <namespace> with foo.bar) and it worked, with sqlite (except for not having that table of course).

1. What database do you use?

2. You don't have to go the SQL → CQN → SQL way, but can also pass plain SQL strings to db/tx.run (which is to allow using native database features, which can hardly be expressed in CQN)

That is, just try to do this:

// const stmtCDS = cds.parse.cql(joinSQLStmt); // don't go via CQN
const Data = await tx.run(joinSQLStmt);

Yet: for this to work, you have to replace CDS names with database names. E.g. foo.bar.Headers would become foo_bar_Headers

[former_member125907 · Accepted solution]

Thank you very much for quick reply.

1). I am using HANA DB

2). I am passing SQL to tx.run().

Working perfectly!!.

[Daniel7]

This looks like you are using an outdated version of @sap/cds-dk. I did the above with the current version and that worked.

[former_member125907]

Thanks Danial,

it's working after update.

[former_member125907]

Hi Daniel,

After updating the @sap/cds-dk, query is parsing in REPL. , but throwing error inside the service.js file.

Version after update:

Error inside .js file:-

Code:

cds.parse.cql(`SELECT matnr,
                      desc,
                      sum (case when Qty = '' then 0 else CAST(Qty as float) end )
               from table1
                      WHERE id = 'ID1213'
               GROUP BY
                     matnr,
                     desc`);

Thanks.

[Daniel7]

Thanks for reporting that Satya.

Actually that "try it live" online parser uses an outdated parser implementation we once made only for this tool, while the actual implementation in CDS compiler and runtimes is much more enhanced. Unfortunately it slipped our attention, that we still had this embedded in capire → we'll remove it in the next update of capire.

Closest option to try out CQL to CQN parsing is to use cds repl in your command line like so:

> cds repl
Welcome to cds repl v4.5.0
> cds.parse.cql(`
... select matnr,
... dedc,
... sum (case when Qty = '' then 0 else CAST(Qty as float) end )
... from Table1
... where ID = "ID1213"
... `)
{
  SELECT: {
    from: { ref: [ 'Table1' ] },
    columns: [
      { ref: [ 'matnr' ] },
      { ref: [ 'dedc' ] },
      {
        func: 'sum',
        args: [
          {
            xpr: [
              'case',
              'when',
              { ref: [ 'Qty' ] },
              '=',
              { val: '' },
              'then',
              { val: 0 },
              'else',
              { ref: [ 'Qty' ], cast: { type: 'float' } },
              'end'
            ]
          }
        ]
      }
    ],
    where: [ { ref: [ 'ID' ] }, '=', { ref: [ 'ID1213' ] } ]
  }
}
>

[former_member125907]

Hi Daniel,

Thanks for quick response, but still getting same error, while using CDS repl.

Error:

cds:4:37-41: Error: CAST is not supported

[Daniel7]

Looks like you still have an outdated version used during runtime... otherwise this error wouldn't show up.

[former_member125907]

Hi Deniel,

Thanks for help. I have updated  @sap/cds to 4.4.10, but getting below error.

const  joinSQLStmt =  `select h.Col1,  
                               h.Col2,
                               h.Col3,
                               h.Col4,
                               p.Col5 as Name,
                               p.Col5 as Desc,
                              sum (case when p.Quantity = '' then 0 else CAST(p.Quantity as float) end) as SQty
                         from  <namespace>.Header  as h
                         left outer join <namespace>.Items  as p on h.ID = p.ItemID 
                         group by
                                h.Col1,  
                                h.Col2,
                                h.Col3,
                                h.Col4,
                                p.Col5,
                                p.Col5`;

                                      
              const stmtCDS =   cds.parse.cql(joinSQLStmt); // working perfectly 
              const Data = await tx.run(stmtCDS); // throwing error 

SqlError {message: 'sql syntax error: incorrect syntax near ")": line 1 col 150 (at pos 150)', code: 257, sqlState: 'HY000', level: 1, position: 150, …}
code: 257
level: 1
message: 'sql syntax error: incorrect syntax near ")": line 1 col 150 (at pos 150)'
position: 150