[cds] - [SqlError: sql syntax error: incorrect syntax near "FROM": line 1 col 127 (at pos 127)] {
code: 257,
sqlState: 'HY000',
level: 1,
position: 0,
query: `…`
}
[error] - 500 > {
message: 'sql syntax error: incorrect syntax near "FROM": line 1 col 127 (at pos 127)',
code: '257'
}
Here is the full query:
```sql
WITH Books as (
SELECT *,
('$[' || lpad("$$RN$$", 6, '0')) as _path_
FROM (
SELECT *,
ROW_NUMBER() OVER () as "$$RN$$"
FROM (
SELECT
FROM (
SELECT Books.createdAt,
Books.createdBy,
Books.modifiedAt,
Books.modifiedBy,
Books.ID,
Books.title,
Books.descr,
Books.author_ID,
Books.genre_ID,
Books.stock,
Books.price,
Books.currency_code
FROM localized_AdminService_Books as Books
WHERE (
CASE
WHEN Books.title != ? THEN true
WHEN NOT Books.title != ? THEN false
ELSE (
CASE
WHEN COALESCE(Books.title, ?) IS NULL THEN false
ELSE true
END
)
END
) = TRUE
ORDER BY Books.ID ASC
) as __select__
left JOIN AdminService_Authors as author ON author.ID = __select__.author_ID
GROUP BY author.name
LIMIT 1000
)
)
), Books_author2 as (
SELECT *,
(
(_parent_path_ || '].author[') || lpad("$$RN$$", 6, '0')
) as _path_
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY _parent_path_) as "$$RN$$"
FROM (
SELECT author2.name,
Books._path_ as _parent_path_
FROM AdminService_Authors as author2
inner JOIN Books as Books ON __select__.author_ID = author2.ID
) as author2
) as author2
WHERE "$$RN$$" = 1
)
SELECT *
FROM (
SELECT "_path_",
"_blobs_",
"_expands_",
"_json_"
FROM (
SELECT _path_ as "_path_",
'{}' as "_blobs_",
'{"author":null}' as "_expands_",
'{}' as "_json_"
FROM Books
)
)
UNION ALL
(
SELECT "_path_",
"_blobs_",
"_expands_",
"_json_"
FROM (
SELECT _path_ as "_path_",
'{}' as "_blobs_",
'{}' as "_expands_",
(
SELECT name as "name"
FROM JSON_TABLE('[{}]', '$' COLUMNS(I FOR ORDINALITY)) FOR JSON ('format' = 'no', 'omitnull' = 'no', 'arraywrap' = 'no') RETURNS NVARCHAR(2147483647)
) as "_json_"
FROM Books_author2
)
)
ORDER BY "_path_" ASC
```
As far as I can tell, this is the broken part:
Books_author2 as (
SELECT *, -- looks weird, wheres the from?
(
(_parent_path_ || '].author[') || lpad("$$RN$$", 6, '0')
) as _path_
FROM ( -- looks weird, is that the from I was looking for → it comes without corresponding SELECT
Detailed steps to reproduce
cds watch --profile hybridHere is the full query:
```sql WITH Books as ( SELECT *, ('$[' || lpad("$$RN$$", 6, '0')) as _path_ FROM ( SELECT *, ROW_NUMBER() OVER () as "$$RN$$" FROM ( SELECT FROM ( SELECT Books.createdAt, Books.createdBy, Books.modifiedAt, Books.modifiedBy, Books.ID, Books.title, Books.descr, Books.author_ID, Books.genre_ID, Books.stock, Books.price, Books.currency_code FROM localized_AdminService_Books as Books WHERE ( CASE WHEN Books.title != ? THEN true WHEN NOT Books.title != ? THEN false ELSE ( CASE WHEN COALESCE(Books.title, ?) IS NULL THEN false ELSE true END ) END ) = TRUE ORDER BY Books.ID ASC ) as __select__ left JOIN AdminService_Authors as author ON author.ID = __select__.author_ID GROUP BY author.name LIMIT 1000 ) ) ), Books_author2 as ( SELECT *, ( (_parent_path_ || '].author[') || lpad("$$RN$$", 6, '0') ) as _path_ FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY _parent_path_) as "$$RN$$" FROM ( SELECT author2.name, Books._path_ as _parent_path_ FROM AdminService_Authors as author2 inner JOIN Books as Books ON __select__.author_ID = author2.ID ) as author2 ) as author2 WHERE "$$RN$$" = 1 ) SELECT * FROM ( SELECT "_path_", "_blobs_", "_expands_", "_json_" FROM ( SELECT _path_ as "_path_", '{}' as "_blobs_", '{"author":null}' as "_expands_", '{}' as "_json_" FROM Books ) ) UNION ALL ( SELECT "_path_", "_blobs_", "_expands_", "_json_" FROM ( SELECT _path_ as "_path_", '{}' as "_blobs_", '{}' as "_expands_", ( SELECT name as "name" FROM JSON_TABLE('[{}]', '$' COLUMNS(I FOR ORDINALITY)) FOR JSON ('format' = 'no', 'omitnull' = 'no', 'arraywrap' = 'no') RETURNS NVARCHAR(2147483647) ) as "_json_" FROM Books_author2 ) ) ORDER BY "_path_" ASC ```As far as I can tell, this is the broken part:
Details about your project
related to #708