CTE error in the SQL query

Get Help
1

I need to use a CTE for my SQL query but it doesn't work. See the the first img below. I create another query with the exactly same script and filters inside the with statement and the query works normally (see the second img). I really can't tell what is wrong with the CTE..

image
image1015×839 33.5 KB

image
image914×616 29.2 KB

2

My code is below:

with inv as (
select 
    p.productId as 编码,
    p.productFullName as 品名,
    b.brand as 品牌,
    i.warehouse as 仓库,
    i.date as 日期,
    i.qty as 数量,
    i.amount as 成本额
from product as p
left join 
    (
        select productRowId, warehouse, qty, amount, date
        from inventoryHist
        WHERE {{date}}
    ) as i on p.rowId = i.productRowId
left join 
    (
        select brand, code, class, rowId as brandrowId
        from brand
    ) as b on p.brandRowId = b.brandrowId
where p.productId like CONCAT('%', {{product}}, '%') or p.productFullName like CONCAT('%', {{product}}, '%')
)
select * from inv
3

Hi @xuheng925
The error is coming from your database. Check your database query log for more details.
Try running the query directly on your database. Try enclosing column names.

4

Thanks @flamber. I just realize the reason of the error is MySQL 5.7 doesn't support With statement (which is supported by 8.0). We will have to use sub-query as alternative for that, sub-optimal from performance point of view.