complex boolean column expressions must be wrapped in case … when … then …

[patricebender]

Detailed steps to reproduce

> q = cds.ql`SELECT from ${Books} { (stock>0 or stock = 0) as stockNotEmpty }`
cds.ql {
  SELECT: {
    from: { ref: [ 'sap.capire.bookshop.Books' ] },
    columns: [
      {
        xpr: [
          { ref: [ 'stock' ] },
          '>',
          { val: 0 },
          'or',
          { ref: [ 'stock' ] },
          '=',
          { val: 0 }
        ],
        as: 'stockNotEmpty'
      }
    ]
  }
}
> await q
Uncaught:
[SqlError: sql syntax error: incorrect syntax near "(": line 1 col 107 (at pos 107)] {
  code: 257,
  sqlState: 'HY000',
  level: 1,
  position: 0,
  query: `WITH Books as (SELECT  (CASE WHEN Books.stock > ? THEN true WHEN NOT Books.stock > ? THEN false END)  or  (CASE WHEN Books.stock = ? THEN true WHEN NOT Books.stock = ? THEN false END)  as "stockNotEmpty" FROM sap_capire_bookshop_Books as Books) SELECT '$[' as "_path_",'{}' as "_blobs_",'{}' as "_expands_",(SELECT "stockNotEmpty" FROM JSON_TABLE('{}', '$' COLUMNS("'$$FaKeDuMmYCoLuMn$$'" FOR ORDINALITY)) FOR JSON ('format'='no', 'omitnull'='no', 'arraywrap'='no') RETURNS NVARCHAR(2147483647)) as "_json_" FROM Books`
}

WITH Books AS (
    SELECT 
        (CASE 
            WHEN Books.stock > ? THEN true 
            WHEN NOT Books.stock > ? THEN false 
         END) OR 
        (CASE 
            WHEN Books.stock = ? THEN true 
            WHEN NOT Books.stock = ? THEN false 
         END) AS "stockNotEmpty" # parentheses missing?
    FROM sap_capire_bookshop_Books AS Books
) 
SELECT 
    '$[' AS "_path_",
    '{}' AS "_blobs_",
    '{}' AS "_expands_",
    (
        SELECT "stockNotEmpty" 
        FROM JSON_TABLE('{}', '$' COLUMNS("'$$FaKeDuMmYCoLuMn$$'" FOR ORDINALITY)) 
        FOR JSON ('format'='no', 'omitnull'='no', 'arraywrap'='no') 
        RETURNS NVARCHAR(2147483647)
    ) AS "_json_" 
FROM Books

Details about your project

@capire/bookshop	…
@cap-js/asyncapi	1.0.2
@cap-js/cds-types	0.9.0
@cap-js/db-service	1.17.0
@cap-js/hana	1.6.0
@cap-js/openapi	1.1.2
@cap-js/sqlite	1.8.0
@sap/cds	8.8.0
@sap/cds-compiler	5.7.1
@sap/cds-dk	8.7.0
@sap/cds-fiori	1.3.0
@sap/cds-foss	5.0.1
@sap/cds-mtxs	2.5.0
@sap/eslint-plugin-cds	3.1.2
Node.js	v20.11.0

Run cds v -i in your project root to generate this

[patricebender]

This problem is trickier than it looks, but if you want to use complex boolean expressions in columns, a workaround is to take care that the expression itself is wrapped in a case … when … then … statement: ((stock>=0 or stock is null) ? true : false)

q = cds.ql`SELECT from ${Books} { ((stock>=0 or stock is null) ? true : false) as stockNotEmpty }`
> await q
[
  { stockNotEmpty: true },
  { stockNotEmpty: true },
  { stockNotEmpty: true },
  { stockNotEmpty: true },
  { stockNotEmpty: true }
]